Hi,
The below code works fine when i try it in the command console.

In the procedure viewer, when i try to open the HOLD file using report painter it works only if I set the 'to_date' to some value. Any other combination refuses to open and is producing this error message-
"ERROR PARSING MASTER FILE HOLD1(L (FOC1405) SQL PREPARE ERROR.
(FOC1400) SQLCODE IS -180 (HEX: FFFFFF4C)
(1405))

I am unable to figure out why!


-SET &from_date = '' ;
-SET &to_date = '' ;
-SET &to_date = '20091005' ; --> this case WORKS

-IF (&from_date EQ '') AND (&to_date EQ '') GOTO SET_DATE1 ELSE GOTO SET_DATE2 ;
-SET_DATE1
-SET &v_from_date='2000-01-01' ;
-SET &v_to_date='3000-12-31' ;
-GOTO QRY

-SET_DATE2
-IF (&to_date EQ '') AND (&from_date NE '') GOTO SET_DATE2A ELSE GOTO EXIT ;
-SET_DATE2A
-SET &v_to_date = EDIT(&to_date, '9999-99-99') ;
-SET &v_from_date = &v_to_date ;
-GOTO QRY

-QRY
ENGINE DB2 SET DEFAULT_CONNECTION XXXX
SQL DB2 PREPARE SQLOUT FOR
SELECT col1,col2,col3
FROM table
WHERE col3 LIKE 'abc%'
AND DATE(col1) >= '&v_from_date'
AND DATE(col2) <= '&v_to_date'
;
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOLD1
END

-EXIT

Thanks.

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

If I run your code with the date filled in, it immediately jumps to the exit label, which is indeed as coded (it may not be as intended).
If I do not provide a date, it will provide the standard dates and tries to run the sql code, which in my case is of course not present.

Does the db2 stuff run ok when dates are provided?
The message you get seems to indicate that there is some error with trying to run the query. And if that is the case then DevStudio cannot open the HOLD1 master file and produces the 'error parsing ...'.

If you are not doing any further coding with the hold file then try:

;
TABLE
ON TABLE HOLD AS HOLD1
END

Hi,

When I hardcode the values instead of using amper variables, I am able to open the hold file and the fex runs fine.
Query works fine on DB2 also.
On simply running the fex(with the amper variables used in the where clause), it gives the correct results. The only problem is that in this case, it doesn't allow me to open the SQLOUT or the HOLD file in report painter.

Thanks.

ON TABLE HOLD AS HOLD1 FORMAT ALPHA does not work either.

I just tested this code below.
fex runs fine.
Db2 query runs fine
When I set the 'from' and 'to' dates as below, fex runs fine and am able to open the SQLOUT/HOLD file in report painter.
-*SET &from_date = '20091005' ;
-*SET &to_date = '20091006' ;

If I set any(or both) of the dates to '' the report painter refuses to open the HOLD file and it seems like the IF criteria are failing for these scenarios.


-SET &from_date = '' ;
-SET &to_date = '' ;

-SET_DATE
-IF (&from_date EQ '') AND (&to_date EQ '') GOTO SET_DATE1 ELSE GOTO SET_DATE2 ;
-SET_DATE1
-SET &v_from_date='2000-01-01' ;
-SET &v_to_date='3000-12-31' ;
-GOTO QRY

-SET_DATE2
-IF (&from_date NE '') AND (&to_date EQ '') GOTO SET_DATE2A ELSE GOTO SET_DATE3 ;
-SET_DATE2A
-SET &v_from_date = EDIT(&from_date, '9999-99-99') ;
-SET &v_to_date = &v_from_date ;
-GOTO QRY

-SET_DATE3
-IF (&from_date EQ '') AND (&to_date NE '') GOTO SET_DATE2B ELSE GOTO SET_DATE4 ;
-SET_DATE2B
-SET &v_to_date = EDIT(&to_date, '9999-99-99') ;
-SET &v_from_date = &v_to_date ;
-GOTO QRY

-SET_DATE4
-IF (&from_date NE '') AND (&to_date NE '') GOTO SET_DATE2C ELSE GOTO QRY ;
-SET_DATE2C
-SET &v_from_date = EDIT(&from_date, '9999-99-99') ;
-SET &v_to_date = EDIT(&to_date, '9999-99-99') ;
-GOTO QRY


-QRY
-RUN
ENGINE DB2 SET DEFAULT_CONNECTION DDF_D39A
SQL DB2 PREPARE SQLOUT FOR
SELECT
col1, col2,col3
FROM Table
WHERE col1 LIKE 'ABC%'
AND DATE(col2) >= '&v_from_date'
AND DATE(col3) <= '&v_to_date'
;
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END

  
WHERE col1 LIKE 'ABC%'
-IF &v_from_date EQ ' ' AND &v_to_date EQ ' ' GOTO NO_DATES;
AND DATE(col2) >= '&v_from_date'
AND DATE(col3) <= '&v_to_date'
-NO_DATES
;

Actually, you may want to read up on -DEFAULT:

-DEFAULT &from_date = ' ', &to_date = ' '

-SET &v_from_date = IF &from_date EQ '' THEN '2000-01-01' ELSE EDIT(&from_date,'9999-99-99');
-SET &v_to_date   = IF &to_date   EQ '' THEN '3000-12-31' ELSE EDIT(&to_date,'9999-99-99');

-QRY
ENGINE DB2 SET DEFAULT_CONNECTION XXXX
SQL DB2 PREPARE SQLOUT FOR
SELECT col1,col2,col3
FROM table
WHERE col3 LIKE 'abc%'
AND DATE(col1) >= '&v_from_date'
AND DATE(col2) <= '&v_to_date'
;
END

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOLD1
END

-EXIT

Just a thought...

This message has been edited. Last edited by: Tom Flynn,

I just found out this-

If i SET the 'from' and 'to' dates to ' '
the first IF statement SETs the variables and then instead of jumping to -QRY step, control goes to the next IF statement at -SET_DATE2.

At step -SET_DATE2
I simply replaced this
-SET &v_from_date = EDIT(&from_date, '9999-99-99') ;
with
-SET &v_from_date = &v_from_date ;

and i could open the report painter.

So, when i execute the fex, control flows correctly.
But it seems like the report painter is not seeing the GOTOs

Here's an example using CAR master.
Can someone verify if you can open report painter on the hold file HOLD1 for this peice of code below.
Take off the 4 lines below STEP2 and report painter opens on the hold file!
Run the fex either way and it works fine.
Definitely, I am not following how this works.


-SET &country='W GERMANY' ;
-SET &var1 = '' ;
-SET &var2 = '' ;

-IF (&var1 EQ '') AND (&var2 EQ '') GOTO STEP1 ELSE GOTO STEP2 ;
-STEP1
-SET &limit= 0 ;
-GOTO QRY

-STEP2
-IF (&var1 NE '') AND (&var2 EQ '') GOTO STEP3 ELSE GOTO EXIT ;
-STEP3
-SET &limit= &var1 ;
-GOTO QRY

-QRY
-RUN
SQL
select country,car,model
from car
where country = '&country'
and sales >= &limit
;
TABLE ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END

-EXIT

Here's part of the problem. In the case where car1 ne '' and var2 eq '' then &limit variable get evaluated to blank which makes your SQL statement invalid (sales >=;) so there would be no hold file HOLD1. If that is a value (maybe put in a 1 for testing) then there is no problem. In a nutshell, you're creating invalid syntax by the parameter values you're using. I would expect the report painter to have a problem with this. This appears to be the case as well in your original code.

A couple of other notes:
You should NOT use -EXIT as a label. -EXIT is a reserved dialogue manager command. I know it works, but it should not be done. Also, using -EXIT wreaks havoc with ReportCaster.

Hi Darin,
I took out the two -SET commands at the top .
So, basically upon opening the HOLD file in report painter when it prompts values for the amper variables
-if no value is entered it pops an error.
-To proceed to the report painter,give any value to the variables and it opens up.(This worked)

I have to do more testing to make sure this really works.

Thanks much!