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>,

Please, is there anyone in the house who can suggest how I can this? Thanks.

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...

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.

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

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

Thanks.

Just you know, the report involves 11 UNION ALLs

Any help on this, please?