• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

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';
0
Mosquitoe
Asked:
Mosquitoe
  • 4
  • 4
  • 3
  • +1
1 Solution
 
slightwv (䄆 Netminder) Commented:
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')
0
 
MosquitoeAuthor Commented:
datatype of that field is CHAR
that doesn't work either...

perhaps a case statement?
0
 
sdstuberCommented:
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')
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
slightwv (䄆 Netminder) Commented:
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;
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
MosquitoeAuthor Commented:
I still get the nulls ...
0
 
sdstuberCommented:
>>> I still get the nulls ...

isn't that what you want?

please post sample data and expected results.
0
 
MosquitoeAuthor Commented:


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
0
 
Swadhin RaySenior Technical Engineer Commented:
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

0
 
sdstuberCommented:
your sample data appears to be missing 5 columns
0
 
sdstuberCommented:
I tried filling in the missing fields

this seems to work  (the WITH is just to provide sample data,  you would only use the query portion below that because you ahve the actual table)


WITH c_lang_list
     AS (SELECT 'OP' code, 1 cec_id, 'BASEL' groupe FROM DUAL
         UNION ALL
         SELECT 'RT', 2, 'BASEL' FROM DUAL
         UNION ALL
         SELECT 'N/A', 3, 'BASEL' FROM DUAL),
     dossier_waste
     AS (SELECT 'test1' dossier_wst_line_item_num,
                'OP' hrmr_ob_cd,
                10 dos_hwmd_sys_entered_yr,
                325 dossier_identifier,
                'EXP' dossier_type_code
           FROM DUAL
         UNION ALL
         SELECT 'test2' dossier_wst_line_item_num, 'RT', 10, 325, 'EXP' FROM DUAL
         UNION ALL
         SELECT 'test3' dossier_wst_line_item_num, NULL, 10, 325, 'EXP' FROM DUAL)
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)) = NVL(TRIM(UPPER(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';
0
 
slightwv (䄆 Netminder) Commented:
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

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now