1

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?

1

I solve it with UNION ALL:

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'
                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
         UNION ALL
         select rap.cod,
                case
                 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.