Querying to return Null Values. - EXTENDED

Regarding the earlier query below: What I get when I execute the fetch query is this error: lesu273> finderr -9634 -9634 No cast from <type-name>. The specified cast does not exist or the cast function does not exist. Use the CREATE CAST statement to define the cast or to create the cast function. Unless I'm mistaken and another field has a problem of which I am not aware, I think this is occuring because it is trtying to place the null value from thePosNullField field into the variable vThePossNullField and not find a way/fucnctiuon to convert it. Does any one know if there an easy straight fwd way I can carry out these query without querying aheda and without creating a conversion function on the server ? Thanks, Andrew. ----- Forwarded by Andrew Hardy/MAIN/MC1 on 24/09/2004 15:31 ----- |---------+----------------------------> | | "Andrew Hardy" | | | <Andrew.Hardy@mar| | | coni.com> | | | Sent by: | | | owner-informix-li| | | st@iiug.org | | | | | | | | | 24/09/2004 10:08 | | | | |---------+----------------------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| | | | To: informix-list@iiug.org | | cc: | | Subject: Querying to return Null Values. | >--------------------------------------------------------------------------------------------------------------------------------------------------| I have a field in a table which from a design point of view should have a null value in when a row is first created, given what that field represents. It then may/or may not be filled in later. If at some stage later I create a cursor like this: SELECT{+INDEX(%s, my_index)} f1, f2, f3, thePosNullField FROM tableA WHERE f2 = 'something' FOR UPDATE; Then execute the following: EXEC SQL FETCH NEXT myCursor INTO :v1, :v2, :v3, vThePossNullField; It is possible that last field is still null. Although my criteria in the cursor is, for example, satisfied for f2, should the query return 'no matching record', if the thePosNullField is null, or should it fail/succeed in some other way? What is the best way to manage this situation, without querying ahead and with making minimal changes. Hope some-one can help, Thanks. Andrew H sending to informix-list sending to informix-list

On Fri, 24 Sep 2004 10:36:02 -0400, Andrew Hardy wrote: Ah! If you include an indicator variable the indicator will tell you if the retruned value is null. I do not know why you are getting this error, though, as IDS has a defined way to deal with NULLS for built-in types. You do not list your platform and version info so I'd be guessing, but is ThePossNullField a UDT of some kind? Anyway to handle the NULL: short possnullIndicator; DECLARE myCursor CURSOR FOR SELECT{+INDEX(%s, my_index)} f1, f2, f3, thePosNullField FROM tableA WHERE f2 = 'something' FOR UPDATE; EXEC SQL FETCH NEXT myCursor INTO :v1, :v2, :v3, :vThePossNullField indicator possnullIndicator; ^ BTW your MSG was missing this colon, if that was not a typo in the message this may be part of the problem. if (possnullIndicator == -1) { /* Returned row contains a NULL for the indicated column. */ ... } Art S. Kagel > Regarding the earlier query below: > > What I get when I execute the fetch query is this error: > > lesu273> finderr -9634 > -9634 No cast from <type-name>. > > The specified cast does not exist or the cast function does not exist. Use > the CREATE CAST statement to define the cast or to create the cast function. > > Unless I'm mistaken and another field has a problem of which I am not > aware, I think this is occuring because it is trtying to place the null > value from thePosNullField field into the variable vThePossNullField and not > find a way/fucnctiuon to convert it. > > Does any one know if there an easy straight fwd way I can carry out these > query without querying aheda and without creating a conversion function on > the server ? > > Thanks, > > Andrew. > > > > ----- Forwarded by Andrew Hardy/MAIN/MC1 on 24/09/2004 15:31 ----- > |---------+----------------------------> | | "Andrew > Hardy" | | | <Andrew.Hardy@mar| | | coni.com> > | | | Sent by: | | | > owner-informix-li| | | st@iiug.org | | | > | | | | | > | 24/09/2004 10:08 | | | | > |---------+----------------------------> > >--------------------------------------------------------------------------------------------------------------------------------------------------| > | > | > | To: informix-list@iiug.org > | > | cc: > | > | Subject: Querying to return Null Values. > | > >--------------------------------------------------------------------------------------------------------------------------------------------------| > > > > > > > I have a field in a table which from a design point of view should have a > null value in when a row is first created, given what that field represents. > It then may/or may not be filled in later. > > If at some stage later I create a cursor like this: > > SELECT{+INDEX(%s, my_index)} f1, f2, f3, thePosNullField FROM > tableA WHERE f2 = 'something' FOR UPDATE; > > Then execute the following: > > EXEC SQL FETCH NEXT myCursor INTO :v1, :v2, :v3, vThePossNullField; > > It is possible that last field is still null. > > Although my criteria in the cursor is, for example, satisfied for f2, should > the query return 'no matching record', if the thePosNullField is null, or > should it fail/succeed in some other way? > > What is the best way to manage this situation, without querying ahead and > with making minimal changes. > > Hope some-one can help, > > Thanks. > > Andrew H > > > sending to informix-list > > > > > sending to informix-list