Parameter to SQL Passthru

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,

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Platinum Member

posted

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?

Posts: 87 | Registered: August 03, 2006

Ignored post by gregson06 posted

Francis Mariani

Expert

posted

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.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Platinum Member

posted

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,

Posts: 87 | Registered: August 03, 2006

Ignored post by gregson06 posted

Platinum Member

posted

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,

Posts: 87 | Registered: August 03, 2006

Ignored post by gregson06 posted

Francis Mariani

Expert

posted

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

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Platinum Member

posted

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,

Posts: 87 | Registered: August 03, 2006

Ignored post by gregson06 posted

BlueZone

Platinum Member

posted

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.

-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !!

Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006

Ignored post by BlueZone posted

jgelona

Master

posted

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

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006

Ignored post by jgelona posted