unload and null values

I need to create an unload file with a null value in the 2nd column. I have tried SELECT col1, "" FROM table But this extracts the following; 123|\\ | We have solutions that work, neither of which is pretty; Solution 1 ---------- SELECT col1, "NULL-VALUE" FROM table and then pipe the unload file through sed and substitute "NULL-VALUE" Solution 2 ---------- CREATE dummy_table (dummy_column CHAR(1)); SELECT col1, dummy_column FROM table, OUTER dummy_table WHERE col1 = dummy_column ... DROP TABLE dummy_table Is there a neater way of doing this? -- Steve Wright

> > Is there a neater way of doing this? What about this : SELECT a , NULL FROM table WHERE ... -- With best regards, Yuri Dovgart, SAP R/3, Informix consultant, "Telecominvest" company. E-mail y_dovgart@tci.ukrtel.net Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

In the second example you created a table with a 1 character string. Does to matter if the column has a space in it or does it have to be totally null? If not then: select col1, " " # space between the quotes from table should be able to do the job. Steve Wright wrote: > I need to create an unload file with a null value in the 2nd column. > > I have tried > > SELECT col1, "" > FROM table> > But this extracts the following; > > 123|\\ | > > We have solutions that work, neither of which is pretty; > > Solution 1 > ---------- > > SELECT col1, "NULL-VALUE" > FROM table> > and then pipe the unload file through sed and substitute "NULL-VALUE" > > Solution 2 > ---------- > > CREATE dummy_table (dummy_column CHAR(1)); > SELECT col1, dummy_column > FROM table, OUTER dummy_table > WHERE col1 = dummy_column > ... > DROP TABLE dummy_table> > Is there a neater way of doing this? > > -- > Steve Wright

In article <37949017.2C7EF52F@btl.net>, Alvan Rowland Jr. <alvan@btl.net> writes > In the second example you created a table with a 1 character > string. Does to matter if the column has a space in it or does it > have to be totally null? Unfortunately it has to be null. :-) > > If not then: > select col1, " " # space between the quotes > from table> > should be able to do the job. This is effectively the same as our first attempt which produces a space fill string instead of a null string. > > > Steve Wright wrote: > >> I need to create an unload file with a null value in the 2nd >> column. > >> I have tried > >>     SELECT col1, "" >>     FROM table> >> But this extracts the following; > >>     123|\\ | > >> We have solutions that work, neither of which is pretty; > >> Solution 1 >> ---------- > >>     SELECT col1, "NULL-VALUE" >>     FROM table> >> and then pipe the unload file through sed and substitute >> "NULL-VALUE" > >> Solution 2 >> ---------- > >>     CREATE dummy_table (dummy_column CHAR(1)); >>     SELECT col1, dummy_column >>     FROM table, OUTER dummy_table >>     WHERE col1 = dummy_column >>     ... >>     DROP TABLE dummy_table> >> Is there a neater way of doing this? -- Steve Wright

In article <7n16eq$jma$1@nnrp1.deja.com>, Yuri Dovgart <y_dovgart@tci.ukrtel.net> writes > >> >> Is there a neater way of doing this? > >What about this : > >SELECT a , NULL FROM table >WHERE ...> Tried this. Informix complains that "null" is not a column in any table in the query. When I first attempted to select a null value from with 4gl I tried something like the above. I eventually assigned null to a variable and used that within the query. However this approach only works in 4gl. We are attempting to do this select from with dbaccess. -- Steve Wright

I see exactly what you mean. I just tried what you tried and different combinations of it. I don't think there is any way around it in SQL. I think your temporary table is the best bet. I suppose you could have a null table ( just one column containing null ) in your database with one row. Its not that untidy. But its starting to bug me the more I think about it ..... : ) Steve Wright <steve@wrightnet.demon.co.uk> wrote in message news:Pzo75GAUv6k3EwHq@wrightnet.demon.co.uk... > I need to create an unload file with a null value in the 2nd column. > > I have tried > > SELECT col1, "" > FROM table> > But this extracts the following; > > 123|\\ | > > We have solutions that work, neither of which is pretty; > > Solution 1 > ---------- > > SELECT col1, "NULL-VALUE" > FROM table> > and then pipe the unload file through sed and substitute "NULL-VALUE" > > Solution 2 > ---------- > > CREATE dummy_table (dummy_column CHAR(1)); > SELECT col1, dummy_column > FROM table, OUTER dummy_table > WHERE col1 = dummy_column > ... > DROP TABLE dummy_table> > Is there a neater way of doing this? > > > -- > Steve Wright

Do you really need the null? If you want to load into a table with more columns than there were in the unload you just name the columns which correspond to the unload in the INSERT and the un-named column gets a null inserted. Regards Ian Steve Wright wrote: > I need to create an unload file with a null value in the 2nd column. > > I have tried > > SELECT col1, "" > FROM table> > But this extracts the following; > > 123|\\ | > > We have solutions that work, neither of which is pretty; > > Solution 1 > ---------- > > SELECT col1, "NULL-VALUE" > FROM table> > and then pipe the unload file through sed and substitute "NULL-VALUE" > > Solution 2 > ---------- > > CREATE dummy_table (dummy_column CHAR(1)); > SELECT col1, dummy_column > FROM table, OUTER dummy_table > WHERE col1 = dummy_column > ... > DROP TABLE dummy_table> > Is there a neater way of doing this? > > -- > Steve Wright