Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhavvibudagam
Creator II
Creator II

Oracle Null syntax help

Hi Experts,

can any one please help me on this.

I have p.product1 field in my oracle database. I have to convert null values in p.product1 to NULL text like if p.product1 = ' ' then it ned to show NULL text else p.product1.

Please help me to write the syntax under select statement only.

In the ODAG the selections are coming from one app to another app.

For example

Load

DATE,

PRODUCT1,

PRODUCT2;

Sql select

to_Char(p.date,'DD/MM/YYYY') as p.date,

p.product1,

p.product2

from product p where to_Char(p.date,'DD/MM/YYYY') in $(WHEREPART);

in this $(WHEREPART) i  am passing 10/05/2018 from selection app by creating subroutine on top.


Already by using oracle syntax in p.date 10/05/2018 00:00:00 is converted to 10/05/2018 then it will reload the app.


Same like by using oracle syntax i am looking for how to convert null values in p.product1 field into NULL text from selection app i will pass NULL text into here.


Converting in Load not working for odag.


Could you please help me on this.


Thanks in advance.

petter-s Could you please help on this

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

Did you try

NVL(TO_CHAR(p.product1), 'NULL')

OR

Case When  LENGTH(TRIm(p.product1)) =0  then 'NULL' else p.product1 End as p.product1

View solution in original post

3 Replies
vesnabb
Contributor
Contributor

maybe try:

select

to_Char(p.date,'DD/MM/YYYY') as p.date,

nvl (p.product1,' '),

p.product2

from product p where to_Char(p.date,'DD/MM/YYYY') in $(WHEREPART);

sasiparupudi1
Master III
Master III

Did you try

NVL(TO_CHAR(p.product1), 'NULL')

OR

Case When  LENGTH(TRIm(p.product1)) =0  then 'NULL' else p.product1 End as p.product1

keerthika
Creator II
Creator II

Hi,

    you can also try like this,

     if(length(trim(p.product1))=0,'NULL',p.product1)