Problem with IFNULL

[Mighty]

I am trying to run the following query:

Select ORDNUM_10, LINNUM_10, DELNUM_10, PRTNUM_10, CURDUE_10, ORDER_10, STATUS_10, CURQTY_10 FROM "Order Master" WHERE Left(ORDNUM_10, 1)='7' AND ORDNUM_10>'705279' ORDER BY ORDNUM_10, LINNUM_10, DELNUM_10

This returns a load of records. However, I run into problems when I try to step through the records as the CURDUE_10 date field contains invalid dates - NULL dates to be precise.

So I tried to run the following query:

Select ORDNUM_10, LINNUM_10, DELNUM_10, PRTNUM_10, IFNULL(CURDUE_10, ORGDUE_10) AS DUEDATE, ORDER_10, STATUS_10, CURQTY_10 FROM "Order Master" WHERE Left(ORDNUM_10, 1)='7' AND ORDNUM_10>'705279' ORDER BY ORDNUM_10, LINNUM_10, DELNUM_10

However, this query returns no records. Can anyone explain this??? Mighty

 

[Linda84]

Are your Null Dates true-null? I.e., do you have an extra null byte defined for the CURDUE_10 field and is is set to 1? Or, are you dealing with legacy data where the field is set to all 0s or blanks?

You may try using the standard IF function instead.

Select ORDNUM_10, LINNUM_10, DELNUM_10, PRTNUM_10, IF(CURDUE_10 <> '', CURDUE_10, ORGDUE_10) AS DUEDATE, ORDER_10, STATUS_10, CURQTY_10 FROM &quot;Order Master&quot; WHERE Left(ORDNUM_10, 1)='7' AND ORDNUM_10>'705279' ORDER BY ORDNUM_10, LINNUM_10, DELNUM_10
Linda
Pervasive Software Support