Hi, I need to query a hold table that is created from an oracle database that has a date field (BEGIN_DATE) in this format mm/dd/yyyy hh24:mi:ss. The two string variables I get passed in from the form are in this format mm/dd/yyyy. I tried to format the BEGIN_DATE field in the SQL statement that creates the hold table but webfocus doesn't recognize the sql to_char() function. Does anyone know how I can convert the date field in my hold table to a character string that matches the format of the form variables that are being passed?
SQL SELECT
DISTINCT E.SNL_ID, E.NAME, A.SD_NUMBER,
A.ASSIGNMENT_TYPE, A.BEGIN_DATE
>FROM EXTCOPY.PERSON E, SD_ASSIGNMENT A
WHERE E.NAME LIKE '&OTHER.EVAL%'
AND A.ASSIGNMENT_TYPE = '&OTHERTYPE.EVAL'
AND E.SNL_ID = A.ASSIGNMENT_ID;
TABLE ON TABLE HOLD AS HOLDASSIGN
END JOIN SD_NUMBER IN HOLDASSIGN TO SD_NUMBER IN SD AS J8<br />END
TABLE FILE HOLDASSIGN
PRINT NAME NOPRINT
SD_NUMBER NOPRINT
DESCRIPTIVE_TITLE AS 'Title'
BEGIN_DATE/HMDYY AS 'Begin Date'
WHERE BEGIN_DATE GE '&begdate.eval'
WHERE BEGIN_DATE LE '&enddate.eval'
BY NAME AS 'Name'
BY ASSIGNMENT_TYPE AS 'Assignment Type'
BY SD_NUMBER AS 'SD #'
This message has been edited. Last edited by: <Mabel>,
July 19, 2005, 09:49 PM
reFOCUSing
Have you tried using the ORACLE function TO_DATE in your SQL?
Try something like this in your WHERE statement: fieldname = TO_DATE('&variable','MM/DD/YYYY')
Also I don't know if you just didn't post this part of your code but don't forget the 'SQL SQLORA' at the top of the SQL.
July 19, 2005, 10:07 PM
webfocusdev
Hi Curtis, Thanks for the help. It looks like webfocus doesn't recognize the to_date() function either. However, I found a solution with the help of this link. I used the webfocus HDATE() function in a define. http://techsupport.informationbuilders.com/tech/ibm/ibm_nf_nf751_p1.html Here it is...
SQL SELECT DISTINCT E.SNL_ID, E.NAME, A.SD_NUMBER, A.ASSIGNMENT_TYPE,
A.BEGIN_DATE
FROM EXTCOPY.PERSON E, SD_ASSIGNMENT A
WHERE E.NAME LIKE '&OTHER.EVAL%'
AND A.ASSIGNMENT_TYPE = '&OTHERTYPE.EVAL'
AND E.SNL_ID = A.ASSIGNMENT_ID;
TABLE
ON TABLE HOLD AS HOLDASSIGN
END
JOIN SD_NUMBER IN HOLDASSIGN TO
SD_NUMBER IN SD AS J8<br />END
DEFINE FILE HOLDASSIGN
BEGDATE/MDYY = HDATE(BEGIN_DATE, 'MDYY');<br />END TABLE FILE HOLDASSIGN
PRINT NAME NOPRINT
SD_NUMBER NOPRINT
DESCRIPTIVE_TITLE AS 'Title'
BEGIN_DATE NOPRINT
BEGDATE AS 'Begin Date'
WHERE BEGDATE GE '&begdate'
WHERE BEGDATE LE '&enddate'
BY NAME AS 'Name'
BY ASSIGNMENT_TYPE AS 'Assignment Type'
BY SD_NUMBER AS 'SD #'
This message has been edited. Last edited by: <Mabel>,
July 20, 2005, 02:00 PM
Francis Mariani
It's not that WebFOCUS did not recognize the to_date() Oracle function, it was Oracle.I think the syntax should be:
(TO_DATE('&INV_DATE', 'MM-DD-YYYY')
Note the - instead of /
July 20, 2005, 03:29 PM
reFOCUSing
webfocusdev, Quick question why are you not putting the date selection in your SQL call before you create the hold file?
July 20, 2005, 04:07 PM
webfocusdev
I would like to do exactly that. However, I am new to webfocus and I couldn't get any of the SQL funtions I normally use to work in webfocus (TO_CHAR(), TO_DATE(), TO_TIMESTAMP()) using the SQL passthru. I've used these functions numerous times before outside of webfocus on oracle databases. However in webfocus, I keep getting unrecognized scalar function errors. I looked in the documentation and they aren't listed as supported scalar functions.
July 20, 2005, 04:24 PM
Francis Mariani
This works without any problems:
-SET &START_DT = &DATEMDYY; SET SQLENGINE=SQLORA SQL SET SERVER EQP SQL SELECT * FROM EQP.TIME_D WHERE PERIOD_START_DT >= TO_DATE('&START_DT','MM-DD-YYYY'); END
July 20, 2005, 05:08 PM
webfocusdev
Francis,
Thanks. It looks like I had to add the...
SET SQLENGINE=SQLORA SQL SET SERVER xxxx
to the top of my SQL statement for it to recognize the functions.