Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to get wf to read more than 1 sql statement

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How to get wf to read more than 1 sql statement
 Login/Join
 
<d3nis370>
posted
I have three sql statements and each select different items. once all three statements have been executed I need to add all three numbers to get a final number. Now this is where I have trouble. I do not know how to do this. Below is my code for the three sql statements. As of now when I run them separatly everything works but they are on 3 separate pages.So my question is how do I get the sql statements to print out there individual results on one page and how do I write a sql statement that will allow me to add all the numbers and print them out as TOTAL?


ENGINE DB2 SET DEFAULT_CONNECTION Production_DR
SQL DB2 PREPARE SQLOUT FOR
SELECT SUM(PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT)AMT1
, SUM(PC_PREMIUM_AMOUNT)PREM, SUM(PC_FEE_AMOUNT)FEE
, SUM(PC_TAX_AMOUNT)TAX
FROM MTGLIB01/FMR003, MTGLIB01/FMR002
WHERE PC_VSI_POLICY_NO = PT_VSI_POLICY_NO
AND PC_POLICY_SEQ = PT_POLICY_SEQ
AND PC_TRAN_SEQUENCE = PT_TRAN_SEQUENCE
AND PC_SUB_SEQUENCE = PT_SUB_SEQUENCE
AND PC_TRAN_DATE = 1100226
AND SUBSTR(PC_TRAN_TYPE,1,1)NOT IN ('V','P')
AND PC_FUNDED_DATE > 0
AND SUBSTR(PC_TRAN_TYPE,2,1) != 'R'
;
END



ENGINE DB2 SET DEFAULT_CONNECTION Production_DR
SQL DB2 PREPARE SQLOUT FOR
SELECT SUM(PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT)AMT2
, SUM(PC_PREMIUM_AMOUNT)PREM, SUM(PC_FEE_AMOUNT)FEE
, SUM(PC_TAX_AMOUNT)TAX
FROM MTGLIB01/FMR003, MTGLIB01/FMR002
WHERE PC_VSI_POLICY_NO = PT_VSI_POLICY_NO
AND PC_POLICY_SEQ = PT_POLICY_SEQ
AND PC_TRAN_SEQUENCE = PT_TRAN_SEQUENCE
AND PC_SUB_SEQUENCE = PT_SUB_SEQUENCE
AND PC_TRAN_DATE = 1100226
AND SUBSTR(PC_TRAN_TYPE,1,1)NOT IN ('V','P')
AND PC_FUNDED_DATE > 0
AND SUBSTR(PC_TRAN_TYPE,2,1) = 'R'
;
END


ENGINE DB2 SET DEFAULT_CONNECTION Production_DR
SQL DB2 PREPARE SQLOUT FOR
SELECT SUM(PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT)AMT3
, SUM(PC_PREMIUM_AMOUNT)PREM, SUM(PC_FEE_AMOUNT)FEE
, SUM(PC_TAX_AMOUNT)TAX
FROM MTGLIB01/FMR003, MTGLIB01/FMR002
WHERE PC_VSI_POLICY_NO = PT_VSI_POLICY_NO
AND PC_POLICY_SEQ = PT_POLICY_SEQ
AND PC_TRAN_SEQUENCE = PT_TRAN_SEQUENCE
AND PC_SUB_SEQUENCE = PT_SUB_SEQUENCE
AND PC_TRAN_DATE = 1100226
AND SUBSTR(PC_TRAN_TYPE,1,1) = 'R'
AND PC_OPERATOR_ID IN ('S38','A38')
;
END


any ideas would be great

This message has been edited. Last edited by: <d3nis370>,
 
Report This Post
Francis Mariani
Expert
posted Hide Post
The quickest way I can think of is to use the SQL UNION statement. Something like this:

ENGINE DB2 SET DEFAULT_CONNECTION BSL
SQL DB2
SELECT TIME_DIM_KEY FROM TIME_D
WHERE TIME_DIM_KEY >= 37550 AND TIME_DIM_KEY < 37560

UNION

SELECT TIME_DIM_KEY FROM TIME_D
WHERE TIME_DIM_KEY >= 37560 AND TIME_DIM_KEY < 37570
;

END


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, 2005Report This Post
<d3nis370>
posted
I did the union but it puts everything under 1 name like so


AMT1 PREE FEE TAX
300 100 100 100
900 300 300 300
1800 600 600 600



I need it to be


AMT1 PREE FEE TAX
300 100 100 100

AMT2 PREE FEE TAX
900 300 300 300

AMT3 PREE FEE TAX
1800 600 600 600



any ideas?
 
Report This Post
Francis Mariani
Expert
posted Hide Post
SET PAGE=NOLEAD

SQL DB2
SELECT
TIME_DIM_KEY AS TDK1
FROM BASEL.TIME_D
WHERE TIME_DIM_KEY >= 37550 AND TIME_DIM_KEY < 37560
;
TABLE ON TABLE HOLD AS H001 FORMAT HTMTABLE
END

SQL DB2
SELECT
TIME_DIM_KEY AS TDK2
FROM BASEL.TIME_D
WHERE TIME_DIM_KEY >= 37560 AND TIME_DIM_KEY < 37570
;
TABLE ON TABLE HOLD AS H002 FORMAT HTMTABLE
END

-HTMLFORM BEGIN
!IBI.FIL.H001;
<HR>
!IBI.FIL.H002;
-HTMLFORM END


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, 2005Report This Post
<d3nis370>
posted
Nothing printed out when I did what you suggested. Not sure what I did wrong.
 
Report This Post
Francis Mariani
Expert
posted Hide Post
Let's look at your code then.


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, 2005Report This Post
Ram Prasad E
Master
posted Hide Post
All the 3 query uses same tables and join conditions. So why don't you club together, to get better performance.


WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
njsden
Virtuoso
posted Hide Post
quote:
Nothing printed out when I did what you suggested. Not sure what I did wrong.


Run your code again. If you get no "visible" output in your browser it's most likely due to an error during your request; just use the "View Source" option in your internet browser to take a peek at what WebFOCUS managed to do and pay attention to any (FOCxxxx) messages in there. It should lead you in the right direction to find where the error could be.

This message has been edited. Last edited by: njsden,


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
njsden
Virtuoso
posted Hide Post
quote:
I did the union but it puts everything under 1 name


If you already retrieved everything you need, then why not just HOLD those results and use this new HOLD file to produce the independent little reports you need. That way you'll be hitting the database only once.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Do as njsden suggests (and as all WebFOCUS developers do) and view the source if nothing is displayed, or add some error validation in the code (as all WebFOCUS developers should do):

SET PAGE=NOLEAD

SQL DB2
SELECT
TIME_DIM_KEY AS TDK1
FROM BASEL.TIME_D
WHERE TIME_DIM_KEY >= 37550 AND TIME_DIM_KEY < 37560
;
TABLE ON TABLE HOLD AS H001 FORMAT HTMTABLE
END
-RUN
-IF &FOCERRNUM NE 0 GOTO FOC_ERR;

SQL DB2
SELECT
TIME_DIM_KEY AS TDK2
FROM BASEL.TIME_D
WHERE TIME_DIM_KEY >= 37560 AND TIME_DIM_KEY < 37570
;
TABLE ON TABLE HOLD AS H002 FORMAT HTMTABLE
END
-RUN

-IF &FOCERRNUM NE 0 GOTO FOC_ERR;

-HTMLFORM BEGIN
!IBI.FIL.H001;
<HR>
!IBI.FIL.H002;
-HTMLFORM END
-EXIT

-FOC_ERR


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, 2005Report This Post
Tony A
Expert
posted Hide Post
I would suggest having a read of a basic SQL manual to understand how UNION works (if you do not already know).

To get the three values in separate columns you could -
ENGINE DB2 SET DEFAULT_CONNECTION Production_DR
SQL DB2
SELECT SUM(PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT) AS AMT1
     , NULL AS AMT2
     , NULL AS AMT3
     , SUM(PC_PREMIUM_AMOUNT)PREM, SUM(PC_FEE_AMOUNT)FEE
     , SUM(PC_TAX_AMOUNT)TAX
  FROM MTGLIB01/FMR003, MTGLIB01/FMR002
 WHERE PC_VSI_POLICY_NO = PT_VSI_POLICY_NO
   AND PC_POLICY_SEQ = PT_POLICY_SEQ
   AND PC_TRAN_SEQUENCE = PT_TRAN_SEQUENCE
   AND PC_SUB_SEQUENCE = PT_SUB_SEQUENCE
   AND PC_TRAN_DATE = 1100226
   AND SUBSTR(PC_TRAN_TYPE,1,1)NOT IN ('V','P')
   AND PC_FUNDED_DATE > 0
   AND SUBSTR(PC_TRAN_TYPE,2,1) != 'R'
UNION
SELECT NULL AS AMT1
     , SUM(PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT) AS AMT2
     , NULL AS AMT3
     , SUM(PC_PREMIUM_AMOUNT)PREM, SUM(PC_FEE_AMOUNT)FEE
     , SUM(PC_TAX_AMOUNT)TAX
  FROM MTGLIB01/FMR003, MTGLIB01/FMR002
 WHERE PC_VSI_POLICY_NO = PT_VSI_POLICY_NO
   AND PC_POLICY_SEQ = PT_POLICY_SEQ
   AND PC_TRAN_SEQUENCE = PT_TRAN_SEQUENCE
   AND PC_SUB_SEQUENCE = PT_SUB_SEQUENCE
   AND PC_TRAN_DATE = 1100226
   AND SUBSTR(PC_TRAN_TYPE,1,1)NOT IN ('V','P')
   AND PC_FUNDED_DATE > 0
   AND SUBSTR(PC_TRAN_TYPE,2,1) = 'R'
UNION
SELECT NULL AS AMT1
     , NULL AS AMT2
     , SUM(PC_PREMIUM_AMOUNT + PC_FEE_AMOUNT + PC_TAX_AMOUNT) AS AMT3
     , SUM(PC_PREMIUM_AMOUNT)PREM, SUM(PC_FEE_AMOUNT)FEE
     , SUM(PC_TAX_AMOUNT)TAX
  FROM MTGLIB01/FMR003, MTGLIB01/FMR002
 WHERE PC_VSI_POLICY_NO = PT_VSI_POLICY_NO
   AND PC_POLICY_SEQ = PT_POLICY_SEQ
   AND PC_TRAN_SEQUENCE = PT_TRAN_SEQUENCE
   AND PC_SUB_SEQUENCE = PT_SUB_SEQUENCE
   AND PC_TRAN_DATE = 1100226
   AND SUBSTR(PC_TRAN_TYPE,1,1) = 'R'
   AND PC_OPERATOR_ID IN ('S38','A38')
;
END

That should give you (according to your post above) -
AMT1 AMT2 AMT3 PREE FEE TAX
 300    .    .  100 100 100
   .  900    .  300 300 300
   .    . 1800  600 600 600

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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
<d3nis370>
posted
Thanks everyone for your advice I went ahead and made a hold file and everything works perfect. Thanks again!
 
Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to get wf to read more than 1 sql statement

Copyright © 1996-2020 Information Builders