The sql passthru below is giving me an error message
if I replace &var1 with hard-coded 'FOC_NONE', it works.
If I take out the OR filter, the query works fine.
same query on Db2 works fine.

can't figure out why!

-DEFAULT &var1 = 'FOC_NONE' ;
ENGINE DB2 SET DEFAULT_CONNECTION XXX
SQL DB2 PREPARE SQLOUT FOR
SELECT column
FROM TABLE1
WHERE (COL1 = '&var1') OR ('&var1' = 'FOC_NONE') )
AND DATE BETWEEN '&from_date' AND '&to_date'
FOR FETCH ONLY
;
END

TABLE FILE SQLOUT
PRINT *
END


is giving me this -
(FOC1400) SQLCODE IS -199 (HEX: FFFFFF39)
: [42601] {DB2 FOR OS/390}{ODBC DRIVER}{DSN08015} DSNT408I SQLCODE = -19
: 9, ERROR: ILLEGAL USE OF KEYWORD AND. TOKEN FOR WITH FETC
: H ORDER UNION EXCEPT QUERYNO OPTIMIZE WAS EXPECTED DSNT
: 418I SQLSTATE = 42601 SQLSTATE RETURN CODE
: DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
: DSNT416I SQLERRD = 506 0 0 -1 48 0 SQL DIAGNOSTIC
: INFORMATION DSNT416I SQLERRD = X'000001FA' X'00000000' X
: '00000000' X'FF
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

Thanks!

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

Figured it out.

FOC_NONE simply ignores the whole statement in which it is found.
so instead of
WHERE (COL1 = '&var1') OR ('&var1' = 'FOC_NONE') )
this would suffice
WHERE (COL1 = '&var1') - if &var1 is 'FOC_NONE', the whole statement is ignored.

FOC_NONE is really cool.

Thanks.

Actually, it DOESN'T ignore the whole statement - it just seems that way. What it ignores is the entire PHYSICAL line of code on which it appears.

If the entire WHERE statement appears on the same line, the whole line is ignored. In the case of a compound statement using AND, this could be a bad thing.

If the statement spans more than a single line (which is my rule of thumb if I use FOC_NONE) then only the line where the FOC_NONE is substituted is ignored. This means that the WHERE and the ; must be on their own lines as well in order to avoid syntax errors.

FOC_NONE definitely has its place and make a few things easier, but personally it drives me nuts and I avoid it if there's another good way around it.

Hi Darin,
Also, I noticed that the effect of FOC_NONE is only when used in report statements. When used in DM it does not have it's deadly effect.

That's correct. It is recognized as the lines in a table request are parsed and placed on the focstack. Since all Dialogue Manager commands have already been evaluated and/or executed before this happens, the FOC_NONE has no effect on DM statements.

the SQL PASSTHRU process will delete any line that contains FOC_NONE,
The solution to your problem is to put the line that contains FOC_NONE in a separate line Like :
-DEFAULT &var1 = 'FOC_NONE' ;
ENGINE DB2 SET DEFAULT_CONNECTION XXX
SQL DB2 PREPARE SQLOUT FOR
SELECT column
FROM TABLE1
WHERE ((COL1 = '&var1')
OR ('&var1' = 'FOC_NONE')
)
AND DATE BETWEEN '&from_date' AND '&to_date'
FOR FETCH ONLY
;
END

TABLE FILE SQLOUT
PRINT *
END

try this !

Hafid.