SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-20-2008, 09:17 AM
| ||||
| ||||
 null literal in 9.3 Hi, I tried to do a select NULL as... for an not existing column in an Union select statement. I have a problem with my solution in 9.3 when I try to turn the select into a view and work with the view. Select and create view work (below is a simplified version) select cast(null as integer) nilcol from informix.systables -> works create view niltab(nilcol) as select cast(null as integer) nilcol from informix.systables -> works But the select fails: select * from niltab 201: A syntax error has occurred. It works in 9.4 but I would need it in 9.3. Can anybody give me an hint or workaround how I can make this work in 9.3? Regards, Michael      |
|
04-20-2008, 09:17 AM
| |||
| |||
| Re: null literal in 9.3 I can't reproduce this on 9.20.F, 9.30.UC7 or 9.40.FC1, so I'm not convinced the problem isn't elsewhere. However, try '':INTEGER as an alternative both to NULL and CAST. -- Regards, Doug Lawry www.douglawry.webhop.org "Michael Wimmer" <newsgroup1@ecom.at> wrote in message news:433bb0fb$0$4084$91cee783@newsreader01.highway .telekom.at... > Hi, > > I tried to do a select NULL as... for an not existing column in > an Union select statement. > > I have a problem with my solution in 9.3 when I try to turn the > select into a view and work with the view. > > Select and create view work (below is a simplified version) > > select cast(null as integer) nilcol from informix.systables > -> works > > create view niltab(nilcol) as > select cast(null as integer) nilcol from informix.systables > -> works > > But the select fails: > > select * from niltab > 201: A syntax error has occurred. > > It works in 9.4 but I would need it in 9.3. Can anybody give me > an hint or workaround how I can make this work in 9.3? > > Regards, > > Michael |
|
04-20-2008, 09:17 AM
| |||
| |||
| Re: null literal in 9.3 Doug Lawry wrote: > I can't reproduce this on 9.20.F, 9.30.UC7 or 9.40.FC1, so I'm not convinced the > problem isn't elsewhere. However, try '':INTEGER as an alternative both to NULL > and CAST. Hi Doug, thanks a lot, '' did the trick. create view niltab (a) as select cast('' as INTEGER), from informix.systables Funny that you can not reproduce the problem. Did you try to create a view and select from the view? Only the select on the view fails, selecting from a table and creating the view works... If I change the '' to NULL inside the cast function, the select on the view fails.... Regards, Michael |
|
04-20-2008, 09:18 AM
| |||
| |||
| Re: null literal in 9.3 Is there a reason for a view with a null column? I am curious how this could be useful? You could do this in your select : select case when 't'::boolean then null::integer end a, id from test ; I also wrote a procedure called null() that always returned null. I don't know what kind of performance hit that takes versus the above or the view. Michael Wimmer wrote: > Doug Lawry wrote: > > I can't reproduce this on 9.20.F, 9.30.UC7 or 9.40.FC1, so I'm not convinced the > > problem isn't elsewhere. However, try '':INTEGER as an alternative both to NULL > > and CAST. > > Hi Doug, > > thanks a lot, '' did the trick. > > create view niltab (a) > as select cast('' as INTEGER), > from informix.systables > > Funny that you can not reproduce the problem. Did you try to create a > view and select from the view? Only the select on the view fails, > selecting from a table and creating the view works... > > If I change the '' to NULL inside the cast function, the select on the > view fails.... > > Regards, > > Michael |
|
04-20-2008, 09:19 AM
| ||||
| ||||
| Re: null literal in 9.3 Hi, I have to select objects which are logically the same entities but come from two different tables (not my model...). Some fields are only available in either one of the sources. The logic is already programmed but the database was now extended and I want to create a view (select... tab1 union select... tab2) to keep as much of the logic as possible. I can use any literal value to fill up the non-existing columns, but to me it seemed to be the cleanest to use NULLs. Anyway, the Cast('' as Integer) worked... But I am still curious why my Select and Create View both work but the select on the view fails... Regards Michael bozon wrote: > Is there a reason for a view with a null column? I am curious how this > could be useful? > > You could do this in your select : > > select > case when 't'::boolean then null::integer end a, > id > from > test > ; > > I also wrote a procedure called null() that always returned null. I > don't know what kind of performance hit that takes versus the above or > the view. > > > Michael Wimmer wrote: >> Doug Lawry wrote: >>> I can't reproduce this on 9.20.F, 9.30.UC7 or 9.40.FC1, so I'm not convinced the >>> problem isn't elsewhere. However, try '':INTEGER as an alternative both to NULL >>> and CAST. >> Hi Doug, >> >> thanks a lot, '' did the trick. >> >> create view niltab (a) >> as select cast('' as INTEGER), >> from informix.systables >> >> Funny that you can not reproduce the problem. Did you try to create a >> view and select from the view? Only the select on the view fails, >> selecting from a table and creating the view works... >> >> If I change the '' to NULL inside the cast function, the select on the >> view fails.... >> >> Regards, >> >> Michael > |
Linear Mode
