I intend to select some data from Teradata and return the result set to Webfocus using SQL Passthru but due to the significant number of rows of data involved, I plan to use volatile table such that the first section of code creates this volatile table while the next statements INSERT data into the volatile table. The expectation is to get the aggregated result set after all he inserts have been done within Teradata.
The query is working fine when run directly in Teradata but when I incorporate this query into webfocus, it seems to be retuning only the data from the first section of the query. That is, all the data from the insert statements are not coming out. I don’t know if the insert section is not being executed at all.
-*session 1 SQL SQLDBC CREATE VOLATILE TABLE VOL_TABLE AS (SELECT VAL1, VAL2, SUM(AMT) AS AMT FROM TABLE1 WHERE TEST = 'Y' GROUP BY VAL1, VAL2 ) WITH DATA ON COMMIT PRESERVE ROWS;
-*session 2 INSERT INTO TD1840 SELECT VAL3, VAL4, SUM(AMT) AS AMT FROM TABLE1 WHERE TEST = 'Y' GROUP BY VAL3, VAL4
UNION ALL
SELECT VAL5, VAL6, SUM(AMT) AS AMT FROM TABLE1 WHERE TEST = 'Y' GROUP BY VAL5, VAL6; END -RUN
SQL SQLDBC PREPARE SQLOUT FOR SELECT VAL1, VAL2, AMT FROM VOL_TABLE END
-*session 3 TABLE FILE SQLOUT PRINT VAL1 VAL2 AMT ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE SET BYDISPLAY ON ON TABLE PCHOLD FORMAT HTML END
Any help will be appreciated. Thanks.This message has been edited. Last edited by: <Kathryn Henning>,
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
August 12, 2014, 04:07 AM
umun
Please, is there anyone in the house who can suggest how I can this? Thanks.
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
August 12, 2014, 08:54 AM
Francis Mariani
Probably because you can have only one SQL request in one WebFOCUS SQL passthru call. The Adapter Administration for UNIX, Windows,OpenVMS, IBM i, and z/OS manual references a "SQL Reference manual" but I cannot find this. If that volatile table is deleted after the end of the SQL statement, I suggest creating a stored procedure or a view...
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
August 12, 2014, 09:58 AM
umun
I already have a View which I am using but having multiple Views for the 11 sub-queries is not feasible as each different sub-query has different WHERE clauses and filters.
That is why I am using Union All to combine the data before pulling it into webfocus.
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
August 12, 2014, 11:03 AM
Tony A
Why are you not using just the selects with UNION ALL?
... or am I missing something here?
SQL SQLDBC PREPARE SQLOUT FOR
SELECT VAL1
, VAL2
, SUM(AMT) AS AMT
FROM TABLE1
WHERE TEST = 'Y'
UNION ALL
SELECT VAL3 AS VAL1
, VAL4 AS VAL2
, SUM(AMT) AS AMT
FROM TABLE1
WHERE TEST = 'Y'
GROUP BY VAL3, VAL4
UNION ALL
SELECT VAL5 AS VAL1
, VAL6 AS VAL2
, SUM(AMT) AS AMT
FROM TABLE1
WHERE TEST = 'Y'
GROUP BY VAL5, VAL6;
TABLE FILE SQLOUT
SUM AMT
BY VAL1
BY VAL2
END
-RUN
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 12, 2014, 12:02 PM
umun
Teradata is rejecting this because of the large number of rows of data. Due to the fact that we are using View and Union All, I guess before the aggregation, the View is exposing large number of rows and this is more than the threshold/cap in our Teradata system