NVL on many columns - Oracle-CodePudding

create the ORACLE SQL Script statements like below:

drop table temp_table;
create table temp_table as (
select
customer_id ,
max (decode (car_type, 'TOYOTA', 'Y', 'N') as Toyota ,
max (decode (car_type, 'BMW', 'Y', 'N') as BMW ,
max (decode (car_type, 'WV', 'Y', 'N') as WV
from purchase
group by customer_id ) ;

alter table main_cust_table
add (
toyota varchar(2) ,
bmw varchar(2) ,
wv varchar(2) ) ;

update main_cust_table c
set ( toyota, bmw, wv ) =
( select toyota, bmw, wv from temp_table d
where d. customer_id = c. customer_id ) ;
commit;

some customers still have blanks that need to fill 'N' on those columns that same as other customer has detail from temp_table

What is the best way to put 'nvl'?

I don't feel like creating each column for the individual "update" script statement

CodePudding user response：

I'd say that you don't need NVL, but fix DECODE. You're missing 'Y' for the BMW so it says: "if customer drives a BMW, set it to N. Else, set it to NULL".

SQL> with purchase (car_type, customer_id) as
  2    (select 'TOYOTA', 1 from dual union all
  3     select 'BMW', 2 from dual union all
  4     select 'WV', 3 from dual union all
  5     select 'BMW', 3 from dual
  6    )
  7  select
  8    customer_id ,
  9    max (decode (car_type, 'TOYOTA', 'Y', 'N')) as Toyota ,
 10    max (decode (car_type, 'BMW'   ,      'N')) as BMW ,  --> missing 'Y'
 11    max (decode (car_type, 'WV'    , 'Y', 'N')) as WV
 12  from purchase
 13  group by customer_id;

CUSTOMER_ID TOYOTA     BMW        WV
----------- ---------- ---------- ----------
          1 Y                     N             --> NULL for BMW
          2 N          N          N
          3 N          N          Y

SQL>

When fixed:

  7  select
  8    customer_id ,
  9    max (decode (car_type, 'TOYOTA', 'Y', 'N')) as Toyota ,
 10    max (decode (car_type, 'BMW'   , 'Y', 'N')) as BMW ,  --> added 'Y'
 11    max (decode (car_type, 'WV'    , 'Y', 'N')) as WV
 12  from purchase
 13  group by customer_id;

CUSTOMER_ID TOYOTA     BMW        WV
----------- ---------- ---------- ----------
          1 Y          N          N          --> no NULL any more, and the whole
          2 N          Y          N          --  BMW column is now fixed
          3 N          Y          Y

SQL>

CodePudding user response：

I got found the solution to add the next new statement as per this link
https://stackoverflow.com/questions/9633398/oracle-sql-update-query-only-update-values-if-they-are-null

update main_cust_table
set 
toyota = coalesce (toyota, 'N') ,
bmw = coalesce (bmw, 'N') ,
wv = coalesce (wv, 'N') 

where 
toyota is null or
bmw is null or
wv is null;

commit;

yes still require typing individual columns per each line
hopefully, I got this right this time.
Thank you to all who read my question and helped me in the right direction!