ORACLE IS NULL

[darrellbutler]

Hi in my load script I want to add a condition on a field.

In ORACLE SQL the command would be 'CANCEL_REASON IS NULL'

How do I acheive this in QV ?

PS: I've tried CANCEL_REASON = '' but this doesn't appear to work.

Many thanks

Darrell

[pat_agen]

Hi,

qlikview has a function isnull() but this doesn't always give the right result. see the following discussion http://community.qlik.com/forums/t/40879.aspx

so best use len() to test the lenght of the field. If this returns 0, this equates to false() so you should be able to write

where len(CANCEL_REASON)=0.

see if it works.

[Anonymous]

What is it that you want to achieve using this command?

Regards,

Dinesh.

[darrellbutler]

Dinesh,

I'm basically trying to replicate the following SQL in a where clause. I have an odd situation on ORACLE whereby when i run the sql for data i want in a qv app it runs fine. But when i create a view then query that view the performance is rotten. I thought I could then try and do this via the load script in QV

Don't concern yourself with the actual script i'm just interested to know how I do j.Cancel_Reason IS NULL in QV !

WHERE

(

j.state <>'Cancelled'

OR

(

j.State = 'Cancelled'

AND

(

(j.Cancel_Reason IS NULL

AND

(

d.Note_Text IS NULL

OR

Substr(d.Note_Text, 1, 4) IN ('FM02', 'FM03', 'FM04', 'FM05', 'FM06', 'FM07'

,

'FM08', 'FM09', 'FM10', 'FM11', 'FM12', 'FM13'

,

'FM14', 'FM15', 'FM16', 'FM17', 'FM18', 'FM19'

,

'FM21', 'FM22', 'FM23', 'FM24', 'FM26', 'FM28'

,

'FM29', 'FM30', 'FM31', 'FM32', 'FM33', 'FM35'))) OR

j.Cancel_Reason

IN ('FM02', 'FM03', 'FM04', 'FM05', 'FM06', 'FM07', 'FM08', 'FM09', 'FM10', 'FM11', 'FM12', 'FM13', 'FM14', 'FM15', 'FM16', 'FM17', 'FM18', 'FM19', 'FM21', 'FM22', 'FM23', 'FM24', 'FM26', 'FM28', 'FM29', 'FM30', 'FM31', 'FM32', 'FM33', 'FM35'))))Many thanks

Darrell

[Anonymous]

If you simply want to check for NULLS in the LOAD statement AFTER SQL has returned rows, you can always use ISNULL and/or LEN functions as other folks have advised.

However if you have a performance problem in your SQL statement due to a VIEW, you probably want to do an EXPLAIN PLAN on your query in Oracle. The LOAD statements come into play after the rows from SQL statement has been returned and ISNULL or LEN is probably not going to help you.

Regards,

Dinesh.

[Anonymous]

For some reason, I was not able to see your query the first time. Your performance may indeed degrade if you bring in lot more rows which are NULL in your SQL statement and finally discard the NULL rows in the LOAD statement. Anyhow, please use ISNULL or LEN and let us know your results.

Regards,

Dinesh.