topic Oracle Null syntax help in QlikView

Oracle Null syntax help

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Oracle Null syntax help

vesnabb — Thu, 10 May 2018 10:58:00 GMT

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);

Re: Oracle Null syntax help

sasiparupudi1 — Thu, 10 May 2018 11:10:21 GMT

Did you try

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

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

Re: Oracle Null syntax help

keerthika — Thu, 10 May 2018 11:29:58 GMT

Hi,

you can also try like this,

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