NVL on many columns - Oracle

Question

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.

Why have the temp table - why not update (or merge) the real table directly from the query? And will you have customers without any purchases? This approach seems wrong overall though - adding a column to the customer table for every make will be painful and maintaining it will add work too - you'll have to update that table when a customer purchases a new car (and potentially when they sell one?). Why not have a view that calculates the Y/N flags on demand? — Alex Poole, Jan 14 '23 at 10:15
Different columns for each car manufacturer? That will cause you lots of trouble. — jarlh, Jan 14 '23 at 12:33
@AlexPoole it is just sample data very different to the actual data at work. I need the correct SQL statement query, which I am seeking guidance from you and other this website — Lizzie, Jan 14 '23 at 23:22

score 0 · Answer 1 · answered Jan 14 '23 at 09:17

0

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>

answered Jan 14 '23 at 09:17

Littlefoot

131,892
15
35
57

eerrr... thank you for pinpointing my error typing that missed out 'Y' , actually, my boss suggested using "create table" instead of "with" due to the huge database hence to minimise running time to execute the query, since 'create table' has many column blanks that need to fill out after a few subquery – Lizzie Jan 14 '23 at 13:58
I used a CTE as a *shortcut* (as you didn't provide any sample data). – Littlefoot Jan 14 '23 at 16:49
hi again,you mean CTE as WITH statement? – Lizzie Jan 14 '23 at 22:47
I thought I already provided an example statement on top because the first one 'create table' has many max(decode) then the next statement in 'alter table' to add new columns based on 'create table' into the main table then the next to update column 'update main_cust_table' to use between those two tables, which some particular customer that is not on the temp_table become blank detail that I would like to use NVL to fill those blank become 'N' after combined result... – Lizzie Jan 14 '23 at 23:01
oh...I just got new bulb that might need new statement like this link https://stackoverflow.com/questions/9633398/oracle-sql-update-query-only-update-values-if-they-are-null – Lizzie Jan 14 '23 at 23:01
I apologize; yes, you provided sample data which I used as a CTE (the WITH factoring clause) instead of creating an actual table. I see you got the answer yourself. – Littlefoot Jan 15 '23 at 07:42
thank you for helping me here as usual. I am learning 'technical' SQL to get the resulting output right no idea the term how it works despite google confusing me such as NVL and COALESCE :) – Lizzie Jan 15 '23 at 21:35

Lizzie · Answer 2 · 2023-01-16T04:54:24.213

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!

to add a note: there is more information about NVL, and COALESCE that skewed my mind at moment SQL general functions | NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL and NANVL

NVL on many columns - Oracle

2 Answers2