using an if statement inside 'where'

[#1]

HI i need a little help here.

Basically I have a variable called owner and I want to use this inside a where clause, however I only want to include this if there is something inside the variable.

At the minute I am using this which is including it regardless of whether there is anything inside owner or not.

     FROM REP_TASK_NF WHERE CANCELLED_DATE = '' AND OWNER = '$(owner)'

I need it to only use owner if it IS NOT NULL, how do i do this?

Thanks

[alexandros17]

You may check OWNER or $(owner) so if you add the condition "AND NOT OWNER is null" or if you are loading data with LOAD : "AND not isnull($(owner))"

let me know

[#3]

FROM REP_TASK_NF WHERE CANCELLED_DATE = '' AND OWNER = '$(owner)' AND NOT OWNER is null

Like this??

But wont that "AND NOT OWNER is null" check to see that the column/field OWNER is empty?? or am i misunderstanding what that does?

owner is essentially a text input which you either put something in or you dont. if its full then i need to include it in the where. if not i can miss the OWNER field out in the where

[flipside]

Hi,

Try dynamic script for your where clause like this ...

let owner = 'A';

IF '$(owner)' = '' or IsNull($(owner)) then

    set vWhere = CANCELLED_DATE = '' AND OWNER = '$(owner)';

ELSE

    set vWhere = CANCELLED_DATE = '';

ENDIF;

... and call the where clause as ... resident Data where $(vWhere);

flipside

[alexandros17]

Ok, if you want to test the variable after the Select use a Load and add a where condition like this

Where not isnull($(owner))

[#6]

IF '$(owner)' = '' THEN

SET oWhere = AND OWNER = '$(owner)'

ELSE

SET oWhere = ''

ENDIF;

SQL SELECT CANCELLED_DATE,CLOSED_DATE,CLOSED_TIME,DEADLINE_DATE,DEADLINE_TIME,DOCUMENT_TYPE,TASK_FAULT_CODE_ID,TASK_ID,TASK_TYPE,X_OPEN,OWNER

FROM REP_TASK_NF

WHERE CANCELLED_DATE = ''

$(oWhere)

AND DOCUMENT_TYPE = '$(documentType)'

AND DEADLINE_DATE <> ''

AND DEADLINE_DATE >= '$(FromDate)';

--

I've used this and even though theres something in $(owner) i have no results 😕

Thanks

[flipside]

Make sure you terminate the SET statements with a semi-colon as in my example earlier.

flipside

[flipside]

Also helps if I give you the code the right way round ...

IF '$(owner)' = '' THEN

    SET oWhere = '';   

ELSE

    SET oWhere = AND OWNER = '$(owner)';

ENDIF;

flipside

[#9]

Yea i just noticed those.

Thats working good now.

One more thing though, I have 2 fields like this. is there a way of concatinating these onto the end of the where if their is data in one of or both of these variables? instead of writing out the long where for each one.

[flipside · Accepted solution]

You could either build them into your IF statement or append field filters to your oWhere variable ...

IF '$(owner)' = '' THEN

    SET oWhere = '';   

ELSE

    SET oWhere = AND OWNER = '$(owner)';

ENDIF;

IF '$(documentType)' = '' THEN

    SET oWhere = '$(oWhere)';   

ELSE

    SET oWhere = $(oWhere) AND DOCUMENT_TYPE = '$(documentType)';

ENDIF;

flipside