UDA-SQL-0358 Line 92: Syntax error near "=" - Need help

[tven77]

Hi Gurus,

I need your help guys. Here is my case.

I don't think I have a syntax error in my code but everytime I use the substring operator in the if statement, it always give me the error:

An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 92: Syntax error near "=".


Below is my code in the filter.


CASE ?P_CriteriaType?   
WHEN 'AcctId'    THEN  ( [Account Id2] in ?P_AcctId?)
WHEN 'PerfDept' THEN  ( if (substring(?P_CtryCmp?,1,3) = '760') then ([Division Code] = ?P_DivCode? and [Department] in ?P_PerfDept?) else ([Department] in ?P_PerfDept?))
WHEN 'BPMgrSerCurr' THEN  ([Bp Mgr Ser C] in ?P_EmpSer?)   
WHEN 'BPMgrSerHist' THEN  ([Bp Mgr Ser H] in  ?P_EmpSer? )   
END

I already tried using 'starts with' and 'like' operator instead of substring in the "if (substring(?P_CtryCmp?,1,3) = '760')"  part, but I encountered the same error.


Any response from you will be highly appreciated.

Regards,
tven

[fredtheviking]

Well, I am not sure why your statement isn't working.  It is difficult to read and understand, but I did not find a syntax error per say.  Using Case statements inside a filter is a bad idea.  Case statement should only be used to create a custom data item.  I would try something like:

(?P_CriteriaType? = 'AcctId
and [Account Id2] in ?A_AcctId?)
or (
   ?P_CriteriaType? = 'PerfDept'
   and
      ( (substring(?P_CtryCmp,1,3) = '760'
         and [Division Code] = ?P_DivCode?
         and [Department] in ?P_PerfDept?)
         or ([Department] in ?P_PerfDept?) ) )
or ( ?P_CriteriaType? = 'BPMgrSerCurr'
   and [Bp Mgr Ser C] in ?P_EmpSer?)
or ( ?P_CriteriaType? = 'BPMgrSerHist'
   and [Bp Mgr Ser H] in ?P_EmpSer?)

The sample code I provided isn't the cleaniest approach.  You could try playing around with custom data items to create flags.  For example.
"AcctId Flag" =
If ([Account Id2] in ?P_AcctId?) then (1) ELSE (0)

Then you would create a "PrefDept Flag", "BPMgrSerCurr Flag", and "BPMgrSerHist Flag".

Then your filter would look this:
(?P_CriteriaType? = 'AcctId'
and [AcctId Flag] = 1)
or (?P_CriteriaType? = 'PerfDept'
   and [Perf Dept Flag] = 1)
...

I hope this helps.

Regards,

Kris

[tven77]

Thanks for your reply Kris. Will check if this works.