Is there any way to pass a parameter to a script of SQL passthru? I'm trying to cut to the chase on a code that will return values based on date that won't exaust time on the answer-set and recalc on the Report server. Basically I'd like to do this:

SQL SQLORA
SELECT * FROM TABLEA WHERE TABLEA_DATE > &PARAM;
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS MYHOLD
END

Anyone done something like this before?

Thanks!

Sure, this is done all the time.

With Dialog Manager, the variable value replaces the variable name before the line of code is processed. The only case this does not happen is when a variable contains another variable, you have to append .EVAL to the variable name - but that's another story.

Be aware that Date-Time columns in relational dbs are treated as alpha in SQL, you have to use quotes. Also, depending on the type of relational db, the date should probably use "-" as separator, e.g. '2006-11-27'.

As far as I know, this is the syntax to use:

SET SQLENGINE=SQLORA
SQL
SELECT * FROM TABLEA WHERE TABLEA_DATE > '&PARAM';
TABLE ON TABLE HOLD AS MYHOLD
END

You may also need to provide the server name, if more than one WF data adapter is defined:

SQL SET SERVER server-name

This goes between the SQLENGINE command and the SELECT command.

This message has been edited. Last edited by: Francis Mariani,

I've verified that that works, but will it detect the fact that there is a parameter if I put it the same way I had in my previous post? Like an amper-variable? Or would SQL not know what to do with it?

Dialog Manager takes care of the variable, SQL doesn't even get to see it.

If you add this command at the top of your program:

-SET &ECHO=ALL;

you will be able to see the program code and you will see that the variable has been translated before the SQL is run.

I keep trying it but for some reason my SQL results and my results from Developer are not the same. I'm trying to use the SQL function "to_date" so that the user can enter dates in the format 'MM/DD/YYYY' instead of 'YYYY/MM/DD'. Does this change anything on this end?

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

I am convinced that this is the issue, infact when I copy the script exactly between the sql tool and Developer I get totally different answers, as if FOCUS is looking for something else. Nothing seen differently with ECHO=ON, I'm not sure where else to look or how else to rephrase it.

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

Your SQL date selection can be Month Day Year if you wish - SQL figures out the date.

Don't bother using the SQL to_date function.

If the user enters a date with slashes, use Dialog Manager to change the slashes to dashes and use the new variable in the SQL command, e.g.:

-SET &DATE1 = '21/12/2006';
-SET &DATE2 = EDIT(&DATE1,'99$-99$-9999');
-TYPE &DATE1 &DATE2

(The $ means ignore the /, the - means add a -.)

Francis,

I'll try using this format. I managed to get the dates to work using TABLE but not through the SQL Passthru. I'll keep the forum posted incase it's not.

Thanks again!

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

G :
To add to FM's note, you should be able to use DM to set dates and use the variables in your SQL. But the important thing is to make sure that you are using the correct date formats.

At our site for instance, when we use SQL-passthru to DB2, we have use the CCYY-MM-DD format for DATE format fields. Whereas the same field would be used as just CCYYMMDD if I was coding the same WHERE in FOCUS code.
Use (SET XRETRIEVAL=OFF) in combination with (-SET &ECHO=ALL; ) to see how the varaible is being plugged in the SQL code. This will check the SQL syntax and return you the code without actually executing it.
-*
SET XRETRIEVAL=OFF
-SET &ECHO=ALL;
-*
-SET &TODY1=EDIT(&YYMD,'9999-99-99');
-*
SQL DB2
SELECT
FLD1
,FLD2
FROM DBNME.TBL1 A
WHERE PROCESS_DATE = '&TODY1.EVAL'
;
TABLE
ON TABLE HOLD AS HOLD000
END
-RUN
-*

Hope that helps.
Sandeep M.

I do this all the time. Assuming the date is valid and in mm/dd/yyyy format with the slashes, here's what we do:

-SET &MYDATE=EDIT(&PARAM,'99$99$9999');
SET SQLENGINE=SQLORA
SQL
SELECT *
FROM TABLEA
WHERE TABLEA_DATE > TO_DATE('&MYDATE','MMDDYYYY');
TABLE ON TABLE HOLD AS MYHOLD
END