All we need is an easy explanation of the problem, so here it is.
I think I might be overlooking something obvious here. I’m not asking someone to write the whole code for me, I just need a hint or a link to a similar case.
Thanks in advance…
select Client , ProductID, M_POS_TYPE AS Keep_or_Keep_in_Transit, Amount FROM inventory_table inv_table JOIN inventory_position inv_pos ON inv_pos.ProductID=inv_table.ProductID group by Client, ProductID, M_POS_TYPE, Amount
How can I add a new column that checks if the the subtraction of the values in the column:
Amount (for Keep and Keep in Transit) is different from 0 for same ProductID and Client?
Rows 5 and 6 is a perfect case.
Add a new column:
I have tried to use is conditionals, CASE statement, but how can I make sure it will calculate the difference for the same Client and ProductID?
I’m looking for a solution in the generic case, there are thousands of different ProductsIDs and Clients values in the table. I’m a bit stuck in this problem.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Here’s one option.
As you didn’t post test case, I used result of your query as a source (i.e. sample data) (that’s the
SQL> with test (client, prodid, kkit, amount) as 2 (select 'c1', 'it1234', 'Keep' , 100 from dual union all 3 select 'c1', 'fr1234', 'Keep_Transit', 56 from dual union all 4 select 'c2', 'it1234', 'Keep' , 30 from dual union all 5 select 'c2', 'br1234', 'Keep_Transit', 0 from dual union all 6 select 'c3', 'no1234', 'Keep' , 5 from dual union all 7 select 'c3', 'no1234', 'Keep_Transit', 4 from dual union all 8 select 'c4', 'no1234', 'Keep' , 0 from dual union all 9 select 'c4', 'us1234', 'Keep_Transit', 2 from dual 10 ),
diff CTE sums
amount ("Keep" is considered to be positive, "Keep_Transit" negative) per client and product ID:
11 diff as 12 (select client, prodid, 13 sum(case when kkit = 'Keep' then amount 14 when kkit = 'Keep_Transit' then -amount 15 end) as diff 16 from test 17 group by client, prodid 18 having min(kkit) = 'Keep' 19 and max(kkit) = 'Keep_Transit' 20 )
Finally, outer join source data to
diff and use
case expression to display
Y if there’s difference:
21 select t.client, t.prodid, t.kkit, t.amount, 22 case when d.diff is not null then 'Y' end diff 23 from test t left join diff d on d.client = t.client 24 and d.prodid = t.prodid 25 order by t.client, t.prodid; CLIENT PRODID KKIT AMOUNT DIFF ---------- ------ ------------ ---------- ----- c1 fr1234 Keep_Transit 56 c1 it1234 Keep 100 c2 br1234 Keep_Transit 0 c2 it1234 Keep 30 c3 no1234 Keep 5 Y c3 no1234 Keep_Transit 4 Y c4 no1234 Keep 0 c4 us1234 Keep_Transit 2 8 rows selected. SQL>
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂