Hi,
We have a WebFocus procedure that is called by a Java app so the procedure resides on the data server and is not executed from Managed Reporting Environment.
I want to make a call to Oracle and get the date back. Then I want to convert it to a YYYYMMDD format so that I can compare another date with it e.g. IF database_date GT 20160501 THEN ...
What is it in my code that I need to fix to do that?
Thanks in advance.

SET SQLENGINE=SQLORA
SQL SET SERVER RUS_SIMSREAD
SQL
Select to_number(to_char(item_date_value, 'yyyymmdd')) AS TMPDATE from business_reference_item where reference_subtype = 'BILL_31_SIMS_PROCLAMATION_DATE';
;
TABLE ON TABLE HOLD AS PROCDATE FORMAT ALPHA
END
-RUN
-*
TABLE FILE PROCDATE
PRINT TMPDATE
TABLE ON TABLE HOLD AS SAVEDATE
END
-RUN
-*
-READ SAVEDATE NOCLOSE &TMPDATE.A13.
-SET &VAR_DATE_CHAR = &TMPDATE;
-TYPE &VAR_DATE_CHAR;
-RUN
-EXIT

This message has been edited. Last edited by: <Emily McAllister>,

Adrian,

For starters I would use the to_date function instead of to_number.

 
Select to_date(item_date_value, 'yyyymmdd')) AS TMPDATE from business_reference_item where reference_subtype = 'BILL_31_SIMS_PROCLAMATION_DATE'; 

This is assuming that it isn't already a date to begin with. I see that you are reading it to an amper variable. What value are you getting for the amper variable now? Maybe all you need is to do an EDIT('some value','9999$99$99') on your output?

Are you trying to do something like this?

 

ENGINE SQLORA SET DEFAULT_CONNECTION [Your Connection]
SQL SQLORA PREPARE SQLOUT FOR
select to_date('20160826','yyyymmdd') AS MY_DATE from DUAL
END

TABLE FILE SQLOUT
PRINT MY_DATE
ON TABLE SAVE AS DATETESTING
END
-RUN 

-READ DATETESTING &MYDT.A13
-SET &MYDT = EDIT('&MYDT.EVAL','99999999');
-TYPE &MYDT
-EXIT
 

At the very least, you have a typo here:

quote:

TABLE FILE PROCDATE
PRINT TMPDATE
TABLE ON TABLE HOLD AS SAVEDATE
^^^^^
END

Another issue is that the date gets returned from oracle as a float: 20160501.00

What works here is:

SQL SQLORA PREPARE PROCDATE FOR
Select to_number(to_char(CURRENT_DATE, 'yyyymmdd')) AS TMPDATE from dual
;
END
-RUN

SET HOLDLIST = PRINTONLY

TABLE FILE PROCDATE
PRINT COMPUTE TMPDATE1/I8 = TMPDATE;
ON TABLE HOLD AS SAVEDATE FORMAT ALPHA
END
-RUN

-SET &TMPDATE = '';
-READ SAVEDATE &TMPDATE.A8.
-SET &VAR_DATE_CHAR = &TMPDATE;
-TYPE &VAR_DATE_CHAR;
-RUN
-EXIT