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

On Fri, 24 Sep 2004 05:08:14 -0400, Andrew Hardy wrote: PLEASE! Just tell us what you want to accomplish! I have no idea what the problem with the code below is, what problem you are trying to solve, or what you mean by "should the query return 'no matching record', if the thePosNullField is null". Do you want a query that will return all of the records for a certain value of f2 that still have a NULL in thePosNullField? What? I've said it before, I'll say it again: Please post your problem and ask us how to solve it. DO NOT post the solution you could not implement and ask us how to make it work. If you could not make it work chances are it was the wrong solution! Sorry to flame you, but posts like this are FRUSTRATING beyond belief. We want to help, but have no idea what is needed. Art S. Kagel > 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

Not sure about the syntax as I'm not an ESQL program but SELECT f1, f2, f3, possNull FROM tab1 where f2 = "abc" should return all the rows for f2 n= "abc" no matter the value of possNull I believe your query will return all the values where the f2 criteria are true, no matter the value of vThePossNullField. you will get p both the NUlls and the Null nulls why do you think you would get anything other than that? "Andrew Hardy" <Andrew.Hardy@marconi.com> wrote in message news:<cj0tlq$5ll$1@news.xmission.com>... > 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