Hi All -
Is there a way to seperate 'NULL' & 0 for an INT column of a 'SQL server' data source.

I am using SQLpassthru & trying to code in a Filter for 'NULL' like:

-SET NODATA = '' ;
-SET &WHERE = IF &VAR1 EQ '' THEN 'WHERE VAR IS NULL' ELSE 'WHERE VAR =' | &VAR1

SQL SQLMSS PREPARE SQLOUT FOR
SELECT * FROM TABLE WHERE &WHERE

The problem is this logic is not taking 'WHERE VAR = 0' For a zero value coming from data source.

-Any suggestions, much appreciated!

Thanks,
-Kal.

Why not just write

SELECT * FROM TABLE
WHERE VAR IS NULL OR 0;

I tried this in access

SELECT *
FROM Table1
WHERE (((Table1.var) Is Null)) OR (((Table1.var)=0));

Kal,

Can't tell for sure, but you're problem may relate to the way Dialogue Manager treats zero in DM variables. I copies this from an IBI advanced search entry. Maybe it's relevant. Maybe not.

Symptom:

How to distinguish between a dot, a blank and a zero, in Dialogue Manager


Problem:

Dialogue Manager treats incoming values as either character strings or
numeric strings. Problem is that character string can contain numbers.
The ASIS function was created for this purpose


Solution:

-*ET &INPT='0';
-*ET &INPT=' ';
-SET &INPT='.' | X;
-SET &OUTPUT = IF ASIS(&INPT) EQ ' ' THEN 'BLANK' ELSE
-IF ASIS(&INPT) EQ '0' THEN 'ZERO' ELSE
-IF ASIS(&INPT) EQ '.X' THEN 'DOT' ELSE 'OTHER';
-TYPE &INPT &OUTPUT

Hi Frank -
That was my issue- that the DM is giving a null for even a '0' value in the database & I wanted to differentiate! Thanks for your timely response!

Hi Dwf -
ASIS() worked!!
The explanation was very helpful, Thanks very much!!

Regards,
Kal.