Re: Help with NULLs in Where Clause in E/SQL
}From: niv@ix.netcom.com (Nivaldo Diaz )
}Date: 9 Oct 1995 16:08:18 GMT
}X-Informix-List-Id: <news.17760>
}
}I am using SQL Descriptors in E/SQL C. I am trying to tell an already
}prepared SQL statment that a field in the where clase is NULL. But it
}does not seem to work. The statment looks like this:
}
}select * from t1 where f1 = ?;
}
}I get screen input and set up my descriptor like so:
}
}SET DESCRIPTOR "mydesc" VALUE 1 DATA= :f1_data, TYPE=SQLCAHR,
}SIZE=:f1_size;
}
}This works fine. But when I do the following for a null value in field
}2, no rows are returned (there is data there).
}
}indval=-1;
}SET DESCRIPTOR "mydesc" VALUE 1 INDICATOR = :indval;
}
}Before I enter the screen I prepare and describe my SQL so all I have
}to do is set the descriptor and open the cursor. I don't want to have
}to reparse my SQL and make a special case for NULL data on my where
}clause.
}
}Any suggestions or comments would be appreciated. Post here or send
}email to kurt@hteinc.com. TIA.
}
}Kurt Kessel
}HTE, Inc.
}kurt@hteinc.com
Kurt,
You may not want to have to reparse your SQL, but you are going to have to
make some alteration to handle nulls, because nulls are not treated the
same as other values...
One possibility worth investigating is:
SELECT * FROM T1
WHERE ((f1 = ? AND ? IS NOT NULL) OR (f1 IS NULL AND ? IS NULL))
[ AND ... ]
The three question marks should all point to the same data element and
should have the same indicator values. This will work only if the SELECT
statement will correctly compare a value with NULL in the IS [NOT] NULL
clause. I haven't tested that, though I'm 90-95% confident that it would
work. Failing that, you are going to have to have two versions of your
SELECT statement.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>