Focal Point
[CASE-OPENED] Can WebFOCUS Subquery File (.sql) Contain Parameters?

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3287013876

September 09, 2015, 12:27 PM
David Briars
[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

local: WF 8.2 - Windows 7 64bit - Tomcat 6 - MRE / BID - FOCUS - IE11

PMF 8
September 10, 2015, 10:01 PM
David Briars
No problem, Greg.

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.

Dave