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
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
Thanks for your reply Kris. Will check if this works.