How to avoid two similar CTE definitions

Question

I have a report which uses a dozen of CTEs and takes awfuly long to execute. Thing is that a lot of those CTEs use the same tables, joined in the same way, but on different conditions, depending on the column:

    WITH
Kps_CTE (sp_id,ksd_ma_s) As
    (
        select wir_sp_id,SUM(ksd_ma)
            from ksd
            inner join ks on ksd_ks_id=ks_id 
            inner join do ON ksd_do_id=do_id AND do_dot_id in (101)
            inner join wi ON do_wi_id=wi_id 
            inner join wir ON wir_wi_id=wi_id
            INNER JOIN sp ON wir_sp_id=sp_id
            where ksd_ksk_id=13
                AND sp_date >= '2013-01-01'
            group by wir_sp_id
    ),      
    Kis_CTE (sp_id,ksd_wn_s) As
    (
        select wir_sp_id,SUM(ksd_wn)
            from ksd
            inner join ks on ksd_ks_id=ks_id 
            inner join do ON ksd_do_id=do_id AND do_dot_id in (4)
            inner join wi ON do_wi_id=wi_id 
            inner join wir ON wir_wi_id=wi_id
            INNER JOIN sp ON wir_sp_id=sp_id
            where ksd_ksk_id=14
                AND sp_date>= '2013-01-01'
            group by wir_sp_id
    )


 select * from ...

So right now I get only two columns per each table. I would like to avoid joining the same tables all over again and create a few columns in one CTE using different conditions. Is this possible? If I just join these two I won't save much resources.

score 1 · Accepted Answer · answered Mar 01 '16 at 07:58

You have to describe what you are doing with these CTEs. Perhaps this can figure out your case:

;WITH Kps_CTE (sp_id,ksd_ma_s) As
(
    select wir_sp_id,
      SUM(case when ksd_ksk_id=13 then ksd_ma end) as KPS, 
      Sum(case when ksd_ksk_id=14 then ksd_wn end) as KIS, 
    from ksd
    inner join ks on ksd_ks_id=ks_id 
    inner join do ON ksd_do_id=do_id 
    inner join wi ON do_wi_id=wi_id 
    inner join wir ON wir_wi_id=wi_id
    INNER JOIN sp ON wir_sp_id=sp_id
    where (ksd_ksk_id=13 AND do_dot_id = 101
        OR ksd_ksk_id=14 and do_dot_id = 4)
      AND sp_date >= '2013-01-01'
    group by wir_sp_id
),

If OR list is big - it may be converted into another CTE with list of tuples (ksd_ksk_id; do_dot_id), then joined.

Not sure why you are having all those magic numbers in the query, won't be surprised if finally we'll find out that you actually need PIVOTor something.

How to avoid two similar CTE definitions

1 Answers1