Oninit cdi / Archive
Talk to an expert →

SELECTing NULL in view w/ union

Posted in 1999

Topics: General Discussion

allenj — — source: Informix-list mailing list archive (1991-1998)

I am taking advantage of the new 7.31 ability to create a view containing a union. I have three tables A,B and C that I want to UNION together into a view. ala: SELECT <col1>,<col2>,<col3> from A UNION SELECT <col1>,<col2>,<col3> from B UNION SELECT <col1>,<col2>,<col3> from C ; The tables are the same EXCEPT that table A has an extra date column that tables B and C don't have. I do want that Table A date column in the view. The column should be NULL for rows from tables B and C in the view. When I UNION together the SELECTs, the number of columns must be the same all the way across. What do I specify for this extraneous date column (that I want to be NULL) in the SELECTs for tables B and C??? I tried a SELECT <col1>,<col2>,NULL,<col3> from B.......... but the engine yells about there not being a column named NULL. If I can help it, I don't want to resort to "" in the SELECT stmt. Any ideas? thanks allen

↪ replying to allenj
Jay Buckler — — source: Informix-list mailing list archive (1991-1998)

As I recall, using a function on the null value will properly type the null to a null date: SELECT <col1>,<col2>,col_date,<col3> from A UNION SELECT <col1>,<col2>,date(null),<col3> from B UNION SELECT <col1>,<col2>,date(null),<col3> from C ; Jay Buckler allenj <allenj@ndr.com> wrote in message news:37C58AB1.59AC1A37@ndr.com... > I am taking advantage of the new 7.31 ability to create a view > containing a union. > > I have three tables A,B and C that I want to UNION together into a view. > > ala: > > SELECT <col1>,<col2>,<col3> from A > UNION > SELECT <col1>,<col2>,<col3> from B > UNION > SELECT <col1>,<col2>,<col3> from C ; > > The tables are the same EXCEPT that table A has an extra date column > that tables B and C don't have. I do want that Table A date column in > the view. The column should be NULL for rows from tables B and C in > the view. > > When I UNION together the SELECTs, the number of columns must be the > same all the way across. What do I specify for this extraneous date > column (that I want to be NULL) in the SELECTs for tables B and C??? > > I tried a > SELECT <col1>,<col2>,NULL,<col3> from B.......... > > but the engine yells about there not being a column named NULL. If I > can help it, I don't want to resort to "" in the SELECT stmt. > > Any ideas? > > thanks > allen

↪ replying to Jay Buckler
allenj — — source: Informix-list mailing list archive (1991-1998)

This solved my problem. Thanks very much. :) aj Jay Buckler wrote: > > As I recall, using a function on the null value will properly type the null > to a null date: > > SELECT <col1>,<col2>,col_date,<col3> from A > UNION > SELECT <col1>,<col2>,date(null),<col3> from B > UNION > SELECT <col1>,<col2>,date(null),<col3> from C ; > > Jay Buckler > > allenj <allenj@ndr.com> wrote in message news:37C58AB1.59AC1A37@ndr.com... > > I am taking advantage of the new 7.31 ability to create a view > > containing a union. > > > > I have three tables A,B and C that I want to UNION together into a view. > > > > ala: > > > > SELECT <col1>,<col2>,<col3> from A > > UNION > > SELECT <col1>,<col2>,<col3> from B > > UNION > > SELECT <col1>,<col2>,<col3> from C ; > > > > The tables are the same EXCEPT that table A has an extra date column > > that tables B and C don't have. I do want that Table A date column in > > the view. The column should be NULL for rows from tables B and C in > > the view. > > > > When I UNION together the SELECTs, the number of columns must be the > > same all the way across. What do I specify for this extraneous date > > column (that I want to be NULL) in the SELECTs for tables B and C??? > > > > I tried a > > SELECT <col1>,<col2>,NULL,<col3> from B.......... > > > > but the engine yells about there not being a column named NULL. If I > > can help it, I don't want to resort to "" in the SELECT stmt. > > > > Any ideas? > > > > thanks > > allen