asked on

Using NVL in query

Hello,
I am trying to replace the null values but it doesn't seem to be picking it up - can anybody tell me why?

This piece of below: (TRIM(UPPER(NVL(dw.HRMR_OB_CD, 'N/A')))) If this field is null, I want to replace it with N/A - which has a corresponding N/A value in the c_lang_list table and should return the cec_id for it - but I still continue to get nulls....

SELECT DISTINCT
dw.dossier_wst_line_item_num AS WasteStreamNumber,
(SELECT LISTAGG(cll.cec_id, '||') WITHIN GROUP (ORDER BY dw.dos_hwmd_sys_entered_yr)
FROM c_lang_list cll
WHERE cll.GROUPE = 'BASEL' AND TRIM(UPPER(cll.code)) = (TRIM(UPPER(NVL(dw.HRMR_OB_CD, 'N/A'))))
)
AS BaselAnnexCodeList
FROM dossier_waste dw
WHERE dw.dos_hwmd_sys_entered_yr = 10
AND dw.dossier_identifier = 325
AND dw.dossier_type_code = 'EXP';

slightwv (䄆 Netminder)

That is the data type of dw.HRMR_OB_CD?

For grins try (the UPPER and outside trim don't really do anything):

NVL(TRIM(dw.HRMR_OB_CD), 'N/A')

Mosquitoe

ASKER

datatype of that field is CHAR
that doesn't work either...

perhaps a case statement?

Sean Stuber

why remove the UPPER?
I agree it doesn't do anything to N/A, but the non-null values of the column should still use it

AND TRIM(UPPER(cll.code)) = NVL(TRIM(UPPER(dw.HRMR_OB_CD)),'N/A')

slightwv (䄆 Netminder)

Then the columns must not be null.

see what is in them:
select ':' || TRIM(dw.HRMR_OB_CD) || ':' from table_name dw;

or

select dump(dw.HRMR_OB_CD) from table_name dw;

slightwv (䄆 Netminder)

>>but the non-null values of the column should still use it

My mistake. I was thinking only about the 'N/A'. sdstuber is corrrect.

Mosquitoe

ASKER

I still get the nulls ...

Sean Stuber

>>> I still get the nulls ...

isn't that what you want?

please post sample data and expected results.

Mosquitoe

ASKER

The where clause is comparing values from two different tables, c_lang_list and dossier_waste.

c_lang_list (tablename)
code cec_id (col)
OP 1
RT 2
N/A 3

dossier_waste (tablename)
HRMR_OB_CD
OP
RT
(null)

I want the null from dossier_waste to be replaced with N/A so that the c_lang_list.cec_id pulled is 3

Instead - I still see null

Swadhin Ray

Try this and check

--CREATE TABLE 
create table nvl_test
  (hrmr_ob_cd char(20),   ---else create  VARCHAR2(20)
  rowno NUMBER 
  );

--INSERT TEST RECORDS
begin 
insert into nvl_test values (null,1);
insert into nvl_test values (null,2);
insert into nvl_test values ('value',3);
commit;
end;

--QUERY 
SQL>select (trim(upper(nvl(dw.hrmr_ob_cd, 'N/A')))) your_condition,
  nvl(trim(dw.hrmr_ob_cd), 'N/A') suggested_by_slightwv,
  NVL(TRIM(UPPER(dw.HRMR_OB_CD)),'N/A') sdstuber_sug,
  nvl(hrmr_ob_cd, 'N/A') t1 ,
  upper(nvl(trim(dw.hrmr_ob_cd), 'N/A')) t2,
  nvl(upper(dw.hrmr_ob_cd),'N/A') t3,
  (nvl(trim(upper(dw.hrmr_ob_cd)),'N/A')) t4
from nvl_test dw;

Open in new window

Sean Stuber

your sample data appears to be missing 5 columns

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

Given the sample data you posted, check out the code below and tell us that is missing.

drop table my_c_lang_list purge;
create table my_c_lang_list( code char(5), cec_id number);

drop table my_dossier_waste purge;
create table my_dossier_waste( HRMR_OB_CD char(5));

insert into my_c_lang_list values('OP',1);
insert into my_c_lang_list values('RT',2);
insert into my_c_lang_list values('N/A',3);

insert into my_dossier_waste values('OP');
insert into my_dossier_waste values('RT');
insert into my_dossier_waste values(null);
commit;


select a.cec_id, a.code, b.hrmr_ob_cd
from my_c_lang_list a, my_dossier_waste b
where nvl(b.hrmr_ob_cd,'N/A') = trim(a.code)
/

Open in new window