Hi,
I have a problem with date in WF769. Im trying to fetch the date using a SQL and formatting it to MM-DD-YYYY but it is giving me junk cahracters. Any help on this would be great.

SQL SQLORA
SELECT  TO_CHAR(T2.WK_BEG_D,'MM-DD-YYYY') WEEK_BEG_D, TO_CHAR(SYSDATE,'MM-DD-YYYY') CURR_DATE, T2.ACCT_MO_N
AS MONTH_NAME FROM ACCT T1, ACCT T2
WHERE  T2.ACCT_D = (T1.WK_BEG_D-1)
AND T1.ACCT_D = TO_CHAR(SYSDATE, 'DD-MON-YY' );
 
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD FORMAT ALPHA
END
 
-RUN
-READ HOLD &WEEK_BEG_D.A10. &CURR_DATE.A10. &MONTH_NAME.A10.

-TYPE &CURR_DATE

I get the following error

001001-29-
(FOC1400) SQLCODE IS 1843 (HEX: 00000733)
: ORA-01843: not a valid month
L (FOC1406) SQL OPEN CURSOR ERROR. : SQLOUT


Thanks in advance
LG

This message has been edited. Last edited by: Kerry,

My guess is that the field is a varable length char field.

The first 4 digits indicate the size.

If you print the field WEEK_BEG_D with the format /A10, it should fix the problem.

I think Waz is on the correct track but the error is being returned by Oracle.

The error in that case is in the SQL select not the TABLE FILE

Thanks Waz and JG, will give it a try

Just to clarify, the SQL returns perfect results when executed from Oracle clients like TOAD/SQLDeveloper

LG

If you want to know what the format is, add a ?FF after the TABLE FILE xxx or ? HOLD after the END

Use DATETRAN function in define and change it to Character format and create a hold table. And then read the hold table and assign to variable.

This is what i get when i ?FF
WEEK_BEG_D E01 A10V
CURR_DATE E06 A10V

Not sure what the V in A10V stands for....Any help?

I have been struggling with this for quite sometime, all only to fetch the current date from DB in the desired format and display on the report Any help??

Thanks in advance

LG

The V stands for Variable. That means that the data is stored in the hold file preceded by a 6 character number that indicates the length of the field. So in your case is will be 6 bytes length, 10 bytes for WEEK_BEG_D, 6 bytes length and 10 bytes for CURR_DATE. Your read statement should therefor be:

-READ HOLD &var.6. &WEEK_BEG_D.10. &var.6. &CURR_DATE.10.

this should produce the desired result.

The error suggests the problem is in the Oracle SQL pass-through step, not in the WebFOCUS step. Run the SQL pass-through step alone - you should still get the Oracle error.

SQL SQLORA
SELECT  TO_CHAR(T2.WK_BEG_D,'MM-DD-YYYY') WEEK_BEG_D, TO_CHAR(SYSDATE,'MM-DD-YYYY') CURR_DATE, T2.ACCT_MO_N
AS MONTH_NAME FROM ACCT T1, ACCT T2
WHERE  T2.ACCT_D = (T1.WK_BEG_D-1)
AND T1.ACCT_D = TO_CHAR(SYSDATE, 'DD-MON-YY' )
AND (ROWNUM < 10);
END

You are working with several dates, perhaps one of them does not contain the data or is not in the format you expect.

I would start by simplifying the SQL - remove the TO_CHAR functions, then start adding them one at a time until the error occurs.

Thanks for the response.
GamP your solution worked!! I wasnt aware that READ -HOLD would create extra bytes while reading


LG