[CASE-OPENED] Can WebFOCUS Subquery File (.sql) Contain Parameters?
I've created a WebFOCUS subquery file - (.sql) and the corresponding synonym (.mas/.acx) - AOK, as I can run it successfully from a TABLE FILE command.
Currently in the .sql file is command like 'WHERE T1."CAR" = 'AUDI''.
Can I make the hardcoded 'AUDI' a variable that I'd pass from the focexec?
I tried using an amper variable, but the system, understandably, thought I meant 'WHERE T1."CAR" = &CAR'.This message has been edited. Last edited by: <Kathryn Henning>,
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
September 09, 2015, 02:35 PM
David Briars
Went to the health club during lunch, and the answer came to me while on the rowing machine. (And here I thought nothing good ever came out of excercising.)
Rather than creating the .sql/.acx/.mas in an application folder, I instead create them via the 'ON TABLE HOLD AS n FORMAT SQL_SCRIPT' TABLE FILE subcommand.
The TABLE creating the SQL Script has the dynamic filtering I need to do.
By definition, the .sql./.acx/.mas are stored temporarily/for a particular run like all HOLD files.
Once the TABLE creating the script and synonym is done, I then bring the script into the next table via the DB_INFILE function, to create my required nested query.
So it looks like I'm set.
Anyone else using WF generated SQL scripts?
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
September 10, 2015, 10:39 AM
gregv
David, I haven't used SQL scripts but I'm interested in what you've done. Do you have a code example that you can share? Thanks.
Greg
current client: WF 8.1.05 & 8.2 - Windows 7 64bit - Tomcat 7 - MRE / BID - IE11
Here is an example of creating a subquery to be used in a nested subquery SELECT:
The example is running against the AdventureWorks2014 MS SQL Server sample database.
-*
-* Create a subquery (.sql/.mas/.acx) and store in temporary space.
-*
TABLE FILE PRODUCTSUBCATEGORY
PRINT PRODUCTSUBCATEGORYID
WHERE NAME EQ 'Road Bikes'
ON TABLE HOLD AS SUBCATID FORMAT SQL_SCRIPT
END
-RUN
-*
-* Create a report from the PRODUCT table, using a nested subquery.
-*
TABLE FILE PRODUCT
"Report Product Data for 'Road Bikes'"
PRINT PRODUCTID
NAME
PRODUCTSUBCATEGORYID
WHERE DB_INFILE(SUBCATID, PRODUCTSUBCATEGORY, PRODUCTSUBCATEGORY)
WHERE READLIMIT EQ 2
END
Report results:
Report Product Data for 'Road Bikes'
PRODUCTID NAME PRODUCTSUBCATEGORYID
749 Road-150 Red, 62 2
750 Road-150 Red, 44 2
With SQL tracing ON, here is what the created subquery looks like, as well as, the SELECT that actually pulls data from the database:
SELECT
(T1."ProductSubcategoryID") AS E01
FROM
AdventureWorks2014.Production.ProductSubcategory T1
WHERE
(T1."Name" = 'Road Bikes');
(FOC2546) SQL SCRIPT SUBCATID.SQL CREATED SUCCESSFULLY
SELECT
TOP 2 T1."ProductID",
T1."Name",
T1."ProductSubcategoryID"
FROM
AdventureWorks2014.Production.Product T1
WHERE
(T1."ProductSubcategoryID" IN ( SELECT
(T1."ProductSubcategoryID") AS E01
FROM
AdventureWorks2014.Production.ProductSubcategory T1
WHERE
(T1."Name" = 'Road Bikes')));
I want to find out who at IB implemented FORMAT SQL_SCRIPT, so I can take them to a White Sox game, and then to Billy Goat's the next time they are in Chicago. :-)
September 11, 2015, 02:23 AM
StuBouyer
Hi David,
Assuming that PRODUCT is in the same DB as PRODUCTSUBCATEGORY then you can create a JOIN between SUBCATID and PRODUCT rather than using DB_INFILE.
One advantage of the JOIN over DB_INFILE that I have found is that you can utilize other fields from the SQL_SCRIPT in your final report.
I think of this as using the SQL_SCRIPT as a CTE in SQL Server. Depending on your data structure it is much more efficient than holding to disk.
WebFOCUS 8.2.03 (8.2.06 in testing)
September 11, 2015, 09:56 AM
David Briars
Stu,
Thanks.
Yeah, for sure we use the WF SQL script feature, to create inline view and scalar subquires, as well as the nested subquery I show in my example.
Also, for sure, which type of 'query within a query' you use depends upon your requirements and relational database/structure.