Help with SQL Statement..

Hello,I have an date attribute in my workflow and wish to compare that attribute to the current date and determine whether the workflow is overdue or not..I am using the following CASE statement to do this and it doesnt work...it is displaying OVERDUE for all the records within my report..I know the Attribute ID is 19, can someone have a look at my statement and see what i'm doing wrong?CASE WHEN EXISTS (SELECT WF_ValDate, WF_AttrID, WF_ID, WORK_WORKID FROM WWork,WFAttrData WHERE WF_ValDate < GETDATE() AND WF_AttrID = '19' AND WORK_WORKID = WF_ID) THEN 'Overdue' ELSE 'No' END Thanks,Asha

Find more posts tagged with

Comments

Victoria_Freihofer_(rgsinc01admin_-_(deleted))

Message from Olson, Leonard <<A HREF="mailto:len.olson@rgsinc.com">len.olson@rgsinc.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

I'm not sure this is your answer, but I saw a post from someone else where they used a DateDiff function to perform a calculation with a date in MSSQL. Try it like this and see if works, if not you'll need to look up how to calculate with dates. Here's a link on date & math functions for SQL.

http://www.informit.com/isapi/product_id~%7B3D76583B-E984-472F-AB06-A3EEF379C549%7D/content/index.asp

CASE WHEN EXISTS
(SELECT WF_ValDate, WF_AttrID, WF_ID, WORK_WORKID
FROM WWork,WFAttrData
WHERE DATEDIFF(WF_ValDate < GETDATE()) AND WF_AttrID = '19' AND WORK_WORKID = WF_ID)
THEN 'Overdue'
ELSE 'No'
END

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, October 27, 2003 5:38
To: eLink Recipient

Help with SQL Statement..

Posted by DeloitteAdmin on 10/27/2003 05:37 PM

Hello,

I have an date attribute in my workflow and wish to compare that attribute to the current date and determine whether the workflow is overdue or not..

I am using the following CASE statement to do this and it doesnt work...it is displaying OVERDUE for all the records within my report..

I know the Attribute ID is 19, can someone have a look at my statement and see what i'm doing wrong?

CASE WHEN EXISTS
(SELECT WF_ValDate, WF_AttrID, WF_ID, WORK_WORKID
FROM WWork,WFAttrData
WHERE WF_ValDate < GETDATE() AND WF_AttrID = '19' AND WORK_WORKID = WF_ID)
THEN 'Overdue'
ELSE 'No'
END

Thanks,
Asha

John W. Simon, Jr.

In Oracle you usually have to truncate your date fields...trunc(wf_valdate)...to get them to behave as you would expect...because the date field actually includes a time. You might look in the SQL manuals to see if there is a corresponding function...