[SOLVED] How to get wf to read more than 1 sql statement

May 18, 2010, 03:51 PM

<d3nis370>

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

May 18, 2010, 04:03 PM

Francis Mariani

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

May 18, 2010, 04:12 PM

<d3nis370>

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?

May 18, 2010, 04:19 PM

Francis Mariani

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

May 18, 2010, 04:33 PM

<d3nis370>

Nothing printed out when I did what you suggested. Not sure what I did wrong.

May 18, 2010, 06:23 PM

Francis Mariani

Let's look at your code then.

May 19, 2010, 01:37 AM

Ram Prasad E

All the 3 query uses same tables and join conditions. So why don't you club together, to get better performance.

May 19, 2010, 09:59 AM

njsden

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,

May 19, 2010, 10:03 AM

njsden

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.

May 19, 2010, 10:47 AM

Francis Mariani

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

May 19, 2010, 12:53 PM

Tony A

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

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