Conditionning a where restriction

Wed, 26 Aug 2015 12:55:22 GMT

Hi everyone,

I have a script problem to adapt my "where" to the value of a dynamic variable.

More explicitly :

I have the variable "v_Payment_Reserve" that could be equal to "Payment" or to "Reserve".

So with a if condition, I would like to adapt my "where" to this variable.

That means that I would like something like that (in red):

TEMP_CRE_ISIE :

NoConcatenate

REPLACE LOAD

[TPA Number] as [Claim Folder Number],

[Month Report] as [Accounting cut-off date],

L_FLEET_NAME_CRE as [Product code],

'l1' as [Line product] ,

[Risk code] ,

'112' as TCA,

'ITL' as Country,

Date([Date of accident] ,'YYMM')&'ITF' as [Occurrence date],

sum(account_Amount_139) as [Total recoveries reserve]

RESIDENT TEMP_CAL_CRE

where

if ( '$(v_Payment_Reserve)' = 'Payment' ,

[Month Report] = '$(v_Month_report)' ,

[Month Report] < '$(v_Month_report)' )

group by [TPA Number] , [Month Report] , [Risk code] , [Date of accident] , L_FLEET_NAME_CRE , L_FLEET_START_PERIOD , type_benificiary;

But, of course, my red code doesn't work.

Do you have the solution please ?

Thanks a lot.

Re: Conditionning a where restriction

Gysbert_Wassenaar — Wed, 26 Aug 2015 17:19:06 GMT

Try something like:

LET vOperator = if('$(v_Payment_Reserve)' = 'Payment' , '=', '<');

LOAD ...

RESIDENT ...

WHERE [Month Report] $(vOperator) '$(v_Month_report)'

;

maxgro — Wed, 26 Aug 2015 18:39:30 GMT

you can build a variable for the whole where clause

and use the variable in the load

LOAD ...

RESIDENT ...

$(variable)