Re: Type casting qustion

In article <3587D091.46CB@bayer.co.uk>, Peter Lancashire <Peter.Lancashi re.PL1@bayer.co.uk> writes >Damir Kropf wrote: >> >> Hello, >> >> Suppose you created table 't1' with only one column - 'c1' of type >> smallint. Insert some data into table (e.g. 1,2,3,4 ...). >> >> Following SQL statement returnted all values from the table in Informix >> version 5, while Informix 7.x returns no rows: >> >> sql select * from t1 where c1 <> '' >> This is meaningless since '' is not a valid smallint. It appears to get convert to null hence it becomes select * from t1 where c1 <> NULL since NULL is undefined we cannot tell wether the two values are the same or not. Hence this becomes select * from t1 where NULL. which will not return any rows. Try using select * from t1 where c1 IS NOT NULL >> As we have a number of similar SQL statements in a number of >> applications is there a way to revert SQL cast behaviour in Informix 7 >> to that of Informix 5 ? >> >> All the best, >> Damir Kropf >It's the typed null problem. Write a stored procedure that returns a >null smallint and then write > > sql select * from t1 where c1 <> null_int > >You will need to do the same for the date type. > >Why Informix cannot allow a constant NULL in this context I do not know. -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care