CODEWITHSUNDEEP
oracle-databaseora-00979
SKINNY
SKINNY

Reputation: 3

ORA-00979 ORA-22818

Query works without the case statement. After I add the CASE statement, Getting ORA-00979 (Not a GROUP BY expression) for the query below (because I did not add case statement to GROUP BY clause). After I tried that (adding to Group By), I got ORA-22818 (Subquery expressions not allowed here). Any suggestions?

Note: All the left outer joins are to reference tables Table: d_r_o og is the main DIM table.

The case statement is to return a timestamp condition for certain data criteria.

select nvl(country_name,'unknown_cntry') CNTRY_NAME, 
       nvl(rs.reg_tp_nm,'rgs_stus') RG_STUS,
       nvl(REBTN,'u_B_type') B_type_nm,
       decode(to_char(og.TIMESTAMP, 'YYYY'),
                '2015', 'CCCASE',
                        'CURRENT'),
       count(dkey),
       (CASE
          WHEN PG.rite_ts <= (SELECT pssd_dt
                                FROM lpyr
                                WHERE LPYR.PGY_KEY = OG.r_Pgy_KEY)
            THEN 'RECTIFIED'
          ELSE 'no'
        END) RCT_STUS,
  FROM d_r_o og
  left outer join LORSR rs
    on og.key = rs.key
  left outer join LRBR br
    on og.key = br.key
  left outer join LUST st
    on og.key = st.key
  group by nvl(country_name,'unknown_cntry') CNTRY_NAME, 
           nvl(rs.reg_tp_nm,'rgs_stus') RG_STUS,
           nvl(REBTN,'u_B_type') B_type_nm,
           decode(to_char(og.TIMESTAMP, 'YYYY'),
                    '2015', 'CCCASE',
                            'CURRENT'),
           count(dkey);

Upvotes: -1

Views: 656

Answers (2)

SKINNY
SKINNY

Reputation: 3

Thanks for the input guys. I was able to figure it out using a little bit of each of the feedback from you. I pretty much removed lpyr from the case and put it as another left outer join in the from statement.

Then i replaced the CASE statement with : (CASE WHEN PG.rite_ts <= lpyr.pssd_dt
THEN 'RECTIFIED' ELSE 'no' END)

I also added this CASE statement to the Group by Clause and it worked. Thanks everyone. Whew!!! Results are accurate.

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5432

For your ORA-22818 error, you should change this

 when PG.rite_ts <= (SELECT pssd_dt FROM lpyr WHERE LPYR.PGY_KEY = OG.r_Pgy_KEY)

Because subquery expressions SELECT pssd_dt may return many rows, not one value to compare with PG.rite_ts

One solution for this should be using min (or max?) function

when PG.rite_ts <= (SELECT min(pssd_dt) FROM lpyr WHERE LPYR.PGY_KEY = OG.r_Pgy_KEY)

Upvotes: 0

Related Questions