I have a Oracle database and a variable that can have a value for A and a value for B like in this example:
cod cat v1 v2 v3
123 A 1 10 11
123 B 0 0 0
111 A 2 3 4
111 B 0 7 0
I have cod in a table RAP and the CAT is A or B if it has values v1,v2 or v3 in two different tables. For the upper result I must have
RAP:
id cod den
1 123 aaa
2 111 bbb
VAL1_A
fk_rap v1 v2 v3
1 1 10 11
2 2 3 4
VAL1_B
fk_rap v1 v2 v3
2 0 7 0
how can I write an script to do that so that if I don't have nothing in the VAL1_B to get an row with zero on B cat for the cod. So I have to get an CAT A and B for every COD. I don't have anywhere the value A or B, it's just a text to put in CAT for the values it has in VAL1_A and VAL1_B.
I'm making something like this:
with cte as
(select rap.cod,
case
when (coalesce(val1_a.v1,0)<>0 or
coalesce(val1_a.v2,0)<>0 or
coalesce(val1_a.v3,0)<>0)
then
'A'
when (coalesce(val1_b.v1,0)<>0 or
coalesce(val1_b.v2,0)<>0 or
coalesce(val1_b.v3,0)<>0)
then
'B'
end
as cat,
coalesce(val1_a.v1,0) as v1_a,
coalesce(val1_a.v2,0) as v2_a,
coalesce(val1_a.v3,0) as v3_a,
coalesce(val1_b.v1,0) as v1_b,
coalesce(val1_b.v2,0) as v2_b,
coalesce(val1_b.v3,0) as v3_b
from rap
full outer join val1_a on val1_a.fk_rap=rap.id
full outer join val1_b on val1_b.fk_rap=rap.id
)
select cod,
cat,
case when cat='A' then v1_a
when cat='B' then v1_b
end
as v1,
case when cat='A' then v2_a
when cat='B' then v2_b
end
as v2,
case when cat='A' then v3_a
when cat='B' then v3_b
end
as v3
from cte
but I have only a value for A or B... I want a value for A and one for B.... How can I do this?
