Conditionning a where restriction

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

Try something like:

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

LOAD ...

RESIDENT ...

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

;

[maxgro]

you can build a variable for the whole where clause

and use the variable in the load

LOAD ...

RESIDENT ...

$(variable)