1

I am fairly new to SQL and am a bit out of my depth with the query I have been working on. I am attempting to modify a query created by someone else to fix an error. the code I have modified is below:

SELECT COUNTY                               COUNTY,
DECODE(RATE,'115A','R','C')             R_C,
SUM(MTH_CHG)                            MO_CHG,
SUM(YTD_CHG)                            YR_CHG,
COUNT(CUST)                             TOTAL_CUST
    FROM RPT_BLOC_VW,
        (SELECT SRAT_CODE               RATE,
        NVL(billed_chg,0) + NVL(adj_chg,0) MTH_CHG,
        0                             YTD_CHG,
        BLOC_CODE,
        FROM RPT_REV_CON_ETL,
        (SELECT DISTINCT cust_code, prem_code AS CUST 
            FROM   rpt_rev_con_etl 
            WHERE  PROCESS_DATE BETWEEN vStartMonth AND vEndMonth 
                   AND UTVSRAT_desc = '115')
        WHERE PROCESS_DATE BETWEEN vStartMonth AND vEndMonth
        AND UTVSRAT_DESC = '115'
    UNION ALL
     SELECT         SRAT_CODE          RATE,
         0                             MTH_CHG,
         NVL(billed_chg,0) + NVL(adj_chg,0) YTD_CHG,
         BLOC_CODE
        FROM RPT_REV_CON_ETL
          WHERE PROCESS_DATE BETWEEN vStartYear AND vEndMonth
        AND DESC = '115'
     ) DATA
     WHERE DATA.BLOC_CODE = RPT_BLOC_VW.BLOC_CODE(+)
 GROUP BY COUNTY, DECODE(RATE,'115A','R','C')
 ORDER BY COUNTY, DECODE(RATE,'115A','R','C') DESC ;

What I am looking for is a way to combine the second and third select statements into one. They have the same From and Where clause so I am fairly certain it should be possible, but I cannot think of a way to do it that doesn't mess up the distinct clause and screw up the count I am trying to get.

I am pretty sure there is something obvious I am overlooking.

-edited to include full query-

6
  • I changed the formatting, that may not have been as helpful as I wanted ... – jcolebrand Jan 4 '17 at 0:30
  • Don't use strings as date and don't use 2 digit years! – Wernfried Domscheit Jan 4 '17 at 8:44
  • @ Wernfried Domscheit they are actually variables, however the code I pasted was from my most recent test run which I had set the dates manually. didn't notice that when I was pasting. – Cronisej Jan 4 '17 at 15:52
  • I think there's a typo in this code. Above the UNION ALL you select four columns but below it you select five columns. – Joe Obbish Jan 4 '17 at 17:36
  • @Joe Obbish Thanks for catching that, I had been focusing on the section above the join and didnt think how the below section would affect it. In the original version there was a 5th column being selected in the above section, however it was inaccurate and that's what I was working to replace. which is where the third select statement comes in. Removing the "NULL Cust" line should fix that as its not needed given that ive removed the corresponding line in the above section. ive edited the code to remove that line. – Cronisej Jan 4 '17 at 17:51
1

With your original code as such:

    SELECT COUNTY                                  COUNTY,
       DECODE(RATE,'115A','R','C')             R_C,
       SUM(MTH_CHG)                            MO_CHG,
       SUM(YTD_CHG)                            YR_CHG,
       COUNT(CUST)                             TOTAL_CUST
    FROM RPT_BLOC_VW,
       (SELECT SRAT_CODE               RATE,
        NVL(billed_chg,0) + NVL(adj_chg,0) MTH_CHG,
         0                             YTD_CHG,
         BLOC_CODE
        FROM RPT_REV_CON_ETL,
          (SELECT DISTINCT
           CUST_CODE,
           PREM_CODE AS CUST
    FROM   RPT_REV_CON_ETL
    WHERE PROCESS_DATE BETWEEN '28-OCT-16' AND '29-NOV-16' AND DESC = '115'
    )
          WHERE PROCESS_DATE BETWEEN '28-OCT-16' AND '29-NOV-16'
        AND DESC = '115'
     ) DATA
     WHERE DATA.BLOC_CODE = RPT_BLOC_VW.BLOC_CODE(+)
 GROUP BY COUNTY, DECODE(RATE,'115A','R','C')
 ORDER BY COUNTY, DECODE(RATE,'115A','R','C') DESC ;

I start by rewriting it to (this helps me think about it a little differently, which is weird, I'm sure, but this is how I approach it)

SELECT county            COUNTY, 
       Decode(rate, '115A', 'R', 'C') R_C, 
       SUM(mth_chg)      MO_CHG, 
       SUM(ytd_chg)      YR_CHG, 
       Count(cust)       TOTAL_CUST 
FROM   rpt_bloc_vw, 
       (SELECT srat_code                            RATE, 
               Nvl(billed_chg, 0) + Nvl(adj_chg, 0) MTH_CHG, 
               0                                    YTD_CHG, 
               bloc_code 
        FROM   rpt_rev_con_etl, 
               (SELECT DISTINCT cust_code, 
                                prem_code AS CUST 
                FROM   rpt_rev_con_etl 
                WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' 
                       AND desc = '115') 
        WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' 
               AND desc = '115') DATA 
WHERE  DATA.bloc_code = rpt_bloc_vw.bloc_code(+) 
GROUP  BY county, Decode(rate, '115A', 'R', 'C') 
ORDER  BY county, Decode(rate, '115A', 'R', 'C') DESC; 

If I rewrite your query to use CTEs like so:

WITH DistinctCustomers AS (
    SELECT DISTINCT cust_code, 
                    prem_code AS CUST 
    FROM   rpt_rev_con_etl 
    WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
),
DATA as (
    SELECT srat_code                            RATE, 
           Nvl(billed_chg, 0) + Nvl(adj_chg, 0) MTH_CHG, 
           0                                    YTD_CHG, 
           bloc_code ,
           cust.CUST
    FROM   rpt_rev_con_etl etl 
    JOIN   DistinctCustomers cust ON cust.cust_code = etl.cust_code
    WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
)
SELECT county COUNTY, 
       Decode(rate, '115A', 'R', 'C') R_C, 
       SUM(mth_chg) MO_CHG, 
       SUM(ytd_chg) YR_CHG, 
       Count(cust) TOTAL_CUST 
FROM   rpt_bloc_vw rpt
JOIN   DATA d on d.bloc_code = rpt.bloc_code(+) 
GROUP  BY county, Decode(rate, '115A', 'R', 'C') 
ORDER  BY county, Decode(rate, '115A', 'R', 'C') DESC; 

It looks like, if I read this correctly, that you are first getting the distinct customers that had any processes between the date ranges.

Then you go back for the rate codes and charges for those customers. You could probably just do a join there and not need the distinct (which was probably added because it wasn't written correctly, by my eye, but I don't write a lot of Oracle SQL, just MS SQL).

That gives us this:

WITH DATA as (
    SELECT srat_code                            RATE, 
           Nvl(billed_chg, 0) + Nvl(adj_chg, 0) MTH_CHG, 
           0                                    YTD_CHG, 
           bloc_code ,
           prem_code as Cust
    FROM   rpt_rev_con_etl etl 
    WHERE  process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
)
SELECT county COUNTY, 
       Decode(rate, '115A', 'R', 'C') R_C, 
       SUM(mth_chg) MO_CHG, 
       SUM(ytd_chg) YR_CHG, 
       Count(cust) TOTAL_CUST 
FROM   rpt_bloc_vw rpt
JOIN   DATA d on d.bloc_code = rpt.bloc_code(+) 
GROUP  BY county, Decode(rate, '115A', 'R', 'C') 
ORDER  BY county, Decode(rate, '115A', 'R', 'C') DESC; 

Since I don't have a table schema, I'm just assuming that what I've done so far is correct. Pretty sure I got this under control, but see the first query to tell me what I did wrong if I got it wrong.

So then this can be reduced even further if we just do some joins across things like so:

SELECT county COUNTY, 
       Decode(etl.srat_code, '115A', 'R', 'C') R_C, 
       SUM(Nvl(etl.billed_chg, 0) + Nvl(etl.adj_chg, 0)) MO_CHG, 
       SUM(0) YR_CHG, 
       Count(etl.prem_code) TOTAL_CUST 
FROM   rpt_bloc_vw rpt
JOIN   rpt_rev_con_etl etl on etl.bloc_code = rpt.bloc_code(+) AND etl.process_date BETWEEN '28-OCT-16' AND '29-NOV-16' AND desc = '115'
GROUP  BY county, Decode(etl.srat_code, '115A', 'R', 'C') 
ORDER  BY county, Decode(etl.srat_code, '115A', 'R', 'C') DESC; 

But I'm pretty sure I got something wrong on that last one, I don't always trust my own reductions. I've tried to show all my steps here starting with the first edit I did to your post on your original SQL code.

Can you tell me how the output of the last SQL I wrote compares to your original query?

5
  • I don't think you can mix Oracle join syntax with ANSI join syntax, ie. JOIN rpt_rev_con_etl etl on etl.bloc_code = rpt.bloc_code(+) should not work. – Wernfried Domscheit Jan 4 '17 at 8:48
  • @WernfriedDomscheit probably not, also there was an issue with another bit of the code I think. Oracle is not my forte, so I was trying to help work him down the path of how I would refactor this to get where he is trying to go – jcolebrand Jan 4 '17 at 16:00
  • @ jcolebrand seeing your process was very helpful, unfortunately The code you provided will not work for my purposes. When I posted the question I was simply looking for a way to combine the two select statements, so I left out a section of the query that was not relevant, but that section would affect how you refactored the code. I have edited the original post to include the full query. also, a distinct customer is the combination of a cust_code and prem_code, so leaving one of those out like your final code snippet did will not work. just seeing your process has helped a lot though. – Cronisej Jan 4 '17 at 16:16
  • As you can see I'm a little out of my depth when it comes to Oracle so I'm gonna leave it for a better expert than me – jcolebrand Jan 4 '17 at 16:46
  • 1
    @jcolebrand the (+) syntax can be changed to LEFT JOIN .. ON ;) – ypercubeᵀᴹ Jan 4 '17 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.