Can someone please help me to convert this SQL to Webfocus code? GETDATE() - COALESCE(tl.FUTURE_SHIP_DATE,GETDATE()) >= 0

[Merin Joseph]

[David Beagan]

Looks like you are wanting to get FUTURE_SHIP_DATE values that are earlier than the current date-time — as well as the FUTURE_SHIP_DATE values that are NULL. Assuming that FUTURE_SHIP_DATE is of data type HYYMDS

(FUTURE_SHIP_DATE LE '&DATEHYYMDS' OR FUTURE_SHIP_DATE IS MISSING)

[Patrick Huebgen]

Slightly different version - sample based on wf_retail

TABLE FILE retail_samples/wf_retailBY HIGHEST wf_retail.WF_RETAIL_TIME_SALES.TIME_DATEWHERE WF_RETAIL.WF_RETAIL_PRODUCT.PRODUCT_CATEGORY EQ 'Accessories'AND (wf_retail.WF_RETAIL_TIME_SALES.TIME_DATE LE DT_CURRENT_DATE() OR wf_retail.WF_RETAIL_TIME_SALES.TIME_DATE IS MISSING)END

If your field is data time - use DT_CURRENT_DATETIME()

[Mat Barnard]

Actually the original code won't return any null FUTURE_SHIP_DATEs.

The COALESCE will resolve to GETDATE() when the FUTURE_SHIP_DATE is null so the result will be zero.

I think the predicate should just return FUTURE_SHIP_DATEs in the past.

[David Beagan]

Seems like it would return FUTURE_SHIP_DATE values of NULL. Notice the >=0 at the end of the line:

GETDATE() - COALESCE(tl.FUTURE_SHIP_DATE,GETDATE()) >= 0

[Merin Joseph]

Why its LE ? its GE right ???

DEFINE--- TODAY/YYMD = &YYMD;

WHERE FUTURE_SHIP_DATE GE TODAY OR FUTURE_SHIP_DATE IS MISSING;

[David Beagan]

The FUTURE_SHIP_DATE is subtracted from GETDATE() and selecting the ones that are greater or equal to 0. This happens for FUTURE_SHIP_DATE earlier than or equal to today.

[Mat Barnard]

You're right of course.

This is why I shouldn't answer questions before my first coffee of the day. :-)