Issue while creating case expression

Question

I am facing issues when I try to create a case expression with a group function condition along with normal column conditions.

Table: STG

+ -------+--------+------+----------+
| Ref_ID | Actual | Paid | Reason   |
+ -------+--------+------+----------+
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 0      | 480  | TRAINING |
| H1     | 0      | 0    |          |
| H1     | 360    | 0    |          |
| H1     | 360    | 0    |          |
+ -------+--------+------+----------+

Already the code is like below:

Explanation: If Actual hrs per day exceeds 480 then that needs to be taken as 480. ie. 8hrs/day * 60 =480. then the sum of actual is extended 1440 then that needs to be deducted from the sum of actual hrs.

In this same query, I have to sum the all actuals along with paid (paid hrs if reason is Training)

 SELECT
    CASE WHEN (SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END))>1440 THEN
    ((SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END))-1440) ELSE 0
    END SINGLE_RATE
    FROM STG WHERE REF_ID='H1'
    GROUP BY REF_ID;

I tried to modify like below:

SELECT
    CASE WHEN (SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END)+(case when reason = 'training' then paid else 0 end ))>1440 THEN
    ((SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END)+(case when reason = 'training' then paid else 0 end ))-1440) ELSE 0
    END SINGLE_RATE
    FROM STG WHERE REF_ID='H1'
    GROUP BY REF_ID;

but getting below error:

    ORA-00979: not a GROUP BY expression
    00979. 00000 -  "not a GROUP BY expression"
    *Cause:
    *Action:

Also tried like:

SELECT
    (sum ( (CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END) + case when reason = 'training' then paid else 0 end )-1440) SINGLE_RATE
    FROM STG WHERE REF_ID='H1'
    GROUP BY REF_ID
    having sum ( (CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END) + case when reason = 'training' then paid else 0 end )>1440;

but the paid amount is not getting calculated. Only actual is added.

Please give your suggestions.

You could refer this: https://stackoverflow.com/questions/11325268/how-do-i-use-group-by-based-on-a-case-statement-in-oracle — Namandeep_Kaur, Nov 22 '18 at 18:11
What issue are you having? What have you tried to do and what was wrong with it? You can include your desired result in your question; and if you have a query that gets the wrong results, you can include that too, and explain what is wrong with it. — Alex Poole, Nov 22 '18 at 18:31
Please let me know, if anybody have the solution for mentioned question. I need it in urgent basis. — user10692352, Nov 22 '18 at 18:45
You have not specified the desired result for the sample data, and the query you gave does not seem to be relevant. — Hilarion, Nov 22 '18 at 18:56
I have mentioned that table just for example purpose. But, the query which I have mentioned is the logic which includes the business needs. In that query, I have to add the paid hrs where the reason is Training. — user10692352, Nov 22 '18 at 19:00
Pro tip: please pay attention to the layout and formatting of your question. People are more likely to read a question which is written in a readable fashion (imagine!). Also be aware that we are volunteers here: you have no SLA with us and your "urgency" is not ours. — APC, Nov 23 '18 at 07:58
I don't really get this. You talk of hours per day. So I take it that one row represents a day. There is no actual hours exceeding 480 in your example, so we use the original values. These add up to 7200. This is more than 1440, so we subtract 1440. That leaves us with 5760. To this you want to add all training hours. This is 480 in your example. So the final result is 6240. Yes? You want to select the value 6240? — Thorsten Kettner, Nov 23 '18 at 08:30
As others pointed out, you should show expected results in your request. Sample data should contain different scenarios, i.e. values less and greater than 480, totals exceeding or not exceeding 1440, training hours below and over 480... — Thorsten Kettner, Nov 23 '18 at 08:36
I have tried with that. even though only actual hrs is getting added and not the paid hrs. Here, I'm getting result as 5760. but expecting 6240. — user10692352, Nov 23 '18 at 09:14
Then check the spelling of 'training' in your table. In your query you have 'training', but in your sample you have 'TRAINING'. Make sure, query and table data match. You can also use `when lower(reason) = 'training' then ...`. — Thorsten Kettner, Nov 23 '18 at 09:19
BTW: In your sample `paid` is always zero except for training rows. If that is guaranteed to be the case, then you don't need `CASE WHEN` to check for training entries, but can simply add up `paid` without further ado. — Thorsten Kettner, Nov 23 '18 at 09:22
I have tried like below. It gives the expected result : 6240. But, I have to try this inside package. Thank you all. with cte as ( select ref_id , sum ( least(actual, 480) ) as tot --sum(paid) as paid from stg where payroll_ref='H1' group by ref_id ), cte1 as ( select ref_id, sum(paid) as paid from stg where ref_id='H1' and reason='TRAINING' group by ref_id ) select case when (cte.tot+cte1.paid) <= 1440 then tot else (cte.tot+cte1.paid) - 1440 end as single_rate from cte, cte1; — user10692352, Nov 23 '18 at 09:26
I just noticed the case on reason after your suggestion. So, I just changed the reason as UPPER case in APC code. now, thats working as expected. I will try this in package. Thank you Thorsten. with cte as ( select ref_id , sum ( least(actual, 480) + case when reason='TRAINING' then paid else 0 end ) as tot from stg where ref_id='H1' group by ref_id ) select ref_id, case when tot <= 1440 then tot else tot - 1440 end as adjusted_tot from cte; — user10692352, Nov 23 '18 at 09:35
Glad to hear that. You should accept APC's answer, by clicking the acception tick, next to their answer. — Thorsten Kettner, Nov 23 '18 at 09:39

APC · Answer 1 · 2018-11-23T08:12:59.503

2

From your business rules, you always want to include actual in the sum() calculation but only include paid when the reason is 'training'. That rule is quite easy to express in a case statement. In addition you have a cap that actual cannot exceed 480. That rule can be satisfied with a least() function:

select ref_id
      , sum ( least(actual, 480) + case when reason = 'training' then paid else 0 end ) as tot
from stg
group by ref_id
/

To be frank I am still not clear what you are trying to achieve with the 1440; life would be easier if you had posted some sample data which covered all the cases you wanted to handle and your desired output derived from that sample data. But I am going to guess anyway:

with cte as (
    select ref_id
          , sum ( least(actual, 480) + case when reason = 'training' then paid else 0 end ) as tot
    from stg
    group by ref_id
    )
select ref_id
       case 
          when tot <= 1440 then tot
          else tot - 1440
       end as adjusted_tot
from cte
/

As before, if this does not provide the answer you want please edit your question. The clearer your requirement the more likely you are to get a timely answer.

edited Nov 23 '18 at 08:12

answered Nov 22 '18 at 18:54

APC

144,005
19
170
281

Thank you APC. As I don't have office laptop, I just tried this with my personal laptop. The results comes as I expected. but, I will try this by tomorrow on office and will tell you whether it works out. Sorry for making you confuse. but, not sure how to explain my question. So that, so much confusion on my question. Thank you so much for your response. – user10692352 Nov 22 '18 at 19:06
Hi APC, I have tried with your suggestion code. but that is not workout that what I want. Also edited my questions. but check and give your suggestion. – user10692352 Nov 23 '18 at 07:10
I have tried like below. It gives the expected result : 6240. But, I have to try this inside package. Thank you APC. with cte as ( select ref_id , sum ( least(actual, 480) ) as tot --sum(paid) as paid from stg where payroll_ref='H1' group by ref_id ), cte1 as ( select ref_id, sum(paid) as paid from stg where ref_id='H1' and reason='TRAINING' group by ref_id ) select case when (cte.tot+cte1.paid) <= 1440 then tot else (cte.tot+cte1.paid) - 1440 end as single_rate from cte, cte1; – user10692352 Nov 23 '18 at 09:28
I just noticed the case on reason after Thorsten's suggestion. So, I just changed the reason as UPPER case in the code which you have suggested. now, thats working as expected. I will try this in package. Really Thank you APC. with cte as ( select ref_id , sum ( least(actual, 480) + case when reason='TRAINING' then paid else 0 end ) as tot from stg where ref_id='H1' group by ref_id ) select ref_id, case when tot <= 1440 then tot else tot - 1440 end as adjusted_tot from cte; – user10692352 Nov 23 '18 at 09:37
We can call SQL in packaged procedures. It's merely a matter of selecting INTO variables (or a record variable) which matches the projection of the query. The PL/SQL documentation and indeed this very site are littered with examples, if you cannot figure it out for yourself. – APC Nov 23 '18 at 09:41

Issue while creating case expression

1 Answers1