Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    14

    Unanswered: Join Multiple Statements for view

    Hello, I have the following view for the 3rd quarter I need to include all 4 quarters in this view. The only thing that will change is the qtr name (ie qt1, qtr2 etc) the sums and the date ranges, the % of target achieved will need to be at the end and calcuate all months. How can I join each of these queries for all quarters into one big one that will output each quarter in the view, ive tried but keep getting errors? - thanks for your help

    SQL> CREATE VIEW campuscont_qtr3 AS
    2 SELECT campus.campus,
    3 ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3",
    4 (ROUND(campus.QTR3/tot_contr,2)*q3)/
    5 campus.QTR3*100 "% OF TARGET ACHIEVED"
    6 FROM campus,(SELECT SUM(AMOUNT) Q3
    7 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
    8 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q3contr,
    9 (SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr;

    CAMPUS QTR3 % OF TARGET ACHIEVED
    ------ ---------- --------------------
    Main 396.9 79.38
    East 194.4 77.76
    West 97.2 77.76
    North 121.5 81

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140

    Re: Join Multiple Statements for view

    You can make use of the UNION / UNION ALL clause

    CREATE VIEW CAMPUS_CONTR_ALL_QTR
    AS

    SELECT
    1 AS QTR,
    ...the rest of your select concerning the first quarter

    UNION ALL

    SELECT
    2 AS QTR,
    ...the rest of your select concerning the second quarter

    and so on.

  3. #3
    Join Date
    Sep 2003
    Posts
    14

    Re: Join Multiple Statements for view

    OK. looks like this will work but IM getting the wrong output. I would only like one instance of campus and all the quarters to follow. Heres what the output I receieved.

    SQL> SELECT campus.campus,
    2 ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3"
    3 FROM campus,(SELECT SUM(AMOUNT) Q3
    4 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
    5 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q3contr,
    6 (SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr
    7 UNION ALL
    8 SELECT campus.campus,
    9 ROUND(campus.QTR2/tot_contr,2)*q2 "QTR2"
    10 FROM campus,(SELECT SUM(AMOUNT) Q2
    11 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
    12 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q2contr,
    13 (SELECT SUM(QTR2) Tot_Contr FROM campus) totcontr;

    CAMPUS QTR3
    ------ ----------
    Main 396.9
    East 194.4
    West 97.2
    North 121.5
    Main 380.7
    East 234.9
    West 97.2
    North 97.2

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Let's get this straight. The UNION of selects should have returned the following result :

    CAMPUS QTR CONTRIBUTION
    ====================
    Main 1 396.9
    East 1 194.4
    West 1 97.2
    North 1 121.5
    Main 2 380.7
    East 2 234.9
    West 2 97.2
    North 2 97.2

    Now what you actually would like is something like
    CAMPUS QTR1 QTR2 QTR3
    =====================
    Main 396.9 380.7
    East 194.4 234.9
    West 97.2 97.2
    North 121.5 97.2

    Is that what you want ?

    BTW, I assume that the date range in your latest select for Q2 is a typo...

  5. #5
    Join Date
    Sep 2003
    Posts
    14
    Yes, just what I wanted. How would I do it?
    - thanks

  6. #6
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    OK. To achieve this, your first query is looking good, and should be modified only slightly.

    I can only suggest to use the INNER JOIN, LEFT OUTER JOIN, etc. syntax. It makes the query much easier to read.

    Your final select would look like this

    SELECT campus.campus,
    ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3",
    (ROUND(campus.QTR3/tot_contr,2)*q3)/
    campus.QTR3*100 "% OF TARGET ACHIEVED"

    /* CAMPUS IS YOUR DRIVING TABLE */
    FROM campus

    /* inner join on 1st qtr contribution, select SUM always returns 1 row */
    INNER JOIN
    (SELECT SUM(AMOUNT) Q1
    FROM contribution wHERE CDATE >= TO_DATE('01/01/03', 'MM/DD/YY')
    AND CDATE <= TO_DATE('31/03/03', 'MM/DD/YY')) Q1contr
    ON 1 = 1

    /* inner join on 2ND qtr contribution, select SUM always returns 1 row */
    INNER JOIN
    (SELECT SUM(AMOUNT) Q2
    FROM contribution wHERE CDATE >= TO_DATE('01/04/03', 'MM/DD/YY')
    AND CDATE <= TO_DATE('30/06/03', 'MM/DD/YY')) Q2contr
    ON 1 = 1

    etc..

    /* inner join on total contribution */
    INNER JOIN
    (SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr
    ON 1 = 1;



    Just out of curiosity. Isn't there any relationship between "Contributions" and "Campus" (like a CAMPUS column in table "Contributions") ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •