Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Using INSERT Statement in WebFocus
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Using INSERT Statement in WebFocus
 Login/Join
 
Platinum Member
posted
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
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5604 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Just you know, the report involves 11 UNION ALLs


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Any help on this, please?


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Using INSERT Statement in WebFocus

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.