Decode function in SAP BODS

[Former Member]

Hi Gurus,

Could u help me with the below decode statement i used in where clause?

Query.CALMONTH = $GV_Calmonth

decode(Query.CALMONTH  is null,Query.CALMONTH=('0'||substr(add_months( sysdate( ),-1) ,6,2)||'.'||substr(sysdate(),1,4)),Query.CALMONTH)

Error:

Invalid WHERE clause. Additional information: <Syntax error at line <7>: <decode(Query.CALMONTH  is null,Query.CALMONTH=('0'||substr(add_months( sysdate( ),-1) ,6,2)||'.'||substr(sysdate(),1,4)),Query.CA>: near <decode> found <identifier>>.

1 error(s), 0 warning(s).

Check and fix the syntax and retry the operation. (BODI-1112394)>. (BODI-1111078)

[mageshwaran_subramanian]

The statement fails because there's no valid condition

Query.CALMONTH = $GV_Calmonth => this looks a valid condition

but the following is not

decode(Query.CALMONTH  is null,Query.CALMONTH=('0'||substr(add_months( sysdate( ),-1) ,6,2)||'.'||substr(sysdate(),1,4)),Query.CALMONTH)

I think you want to do something like this

decode(Query.CALMONTH  is null,('0'||substr(add_months( sysdate( ),-1) ,6,2)||'.'||substr(sysdate(),1,4)),Query.CALMONTH) = $GV_Calmonth

[former_member228519]

I don't think concatenation of zero is required.

decode(Query.CALMONTH  is null,substr(add_months( sysdate( ),-1) ,6,2)||'.'||substr(sysdate(),1,4),Query.CALMONTH) = $GV_Calmonth

[former_member198401]

Hi Mohammed,

Can you try to modify the decode statement.

Query.CALMONTH=('0'||substr(add_months( sysdate( ),-1) ,6,2)||'.'||substr(sysdate(),1,4))

There was an error when converting sysdate to string. i used a convert statement

The equivalent of the above statement is as below in sql server

select '0'+ substring(cast(dateadd(month,-1,getdate()) as CHAR) ,6,2)+'.' + substring(cast(getdate() as CHAR),1,4)

Please tweek the sql as per your requirement

Regards

Arun Sasi