Db2 Sql error

building semantic layer
1

I am trying to define a predefined filter.But my Sql is not working(syntax error).
My requirement is to get the data or prior month when the input(prompted to the user) is a time span.

For eg User could input time span = “Month Ending 01 Jan 2001”,“Month Endind 02 Feb 2004” etc.If it is jan then i need like dec of prior year eg for “Month Ending 01 Jan 2001” it should return “Month Ending 12 Dec 2000”.Input is of character data type.
My sql goes like this PS_Z_PERIOD.Z_PERIOD_SPAN=CASE WHEN substr (@variable( ‘Time Span:’),14,2)=‘01’ THEN SUBSTR(@Variable(‘Time Span:’),1,13) || "12 Dec " || CHAR(INTEGER(SUBSTR(@Variable(‘Time Span:’),LENGTH(@Variable(‘Time Span:’)) - 3, 4)) - 1) ELSE SUBSTR(@Variable(‘Time Span:’),1,13) ||CHAR(INTEGER(SUBSTR(@Variable(‘Time Span:’),14,2)-1) || " " || substr ((MONTHNAME((DATE(‘9999’ || substr (@variable( ‘Time Span:’),14,2) || ‘01’)- 1 MONTH))),1,3) || substr (@variable( ‘Time Span:’),(length (@variable(‘Time Span:’))-3),4 ) .

There is a problem with the last line of code (length (@variable(‘Time Span:’))-3),4 ).error shows unexpected token")" , expected token “)”.

rocky (BOB member since 2005-03-29)

2

On the surface it looks like some bracket is not balanced. I’m not sure but maybe that colun (:slight_smile: is causing problem. Once I had the problem of prompt label where it was not parsing if I would use even number of commas in the prompt label text! :crazy_face:

salam :pakistan: (BOB member since 2005-01-27)

3

This One line of code is not working properly

DATE(‘9999’ || substr (@variable( ‘Time Span:’),14,2) || ‘01’).Should it be DATE(’(‘9999’ ||’-’|| substr (@variable( ‘Time Span:’),14,2) ||’-’ ||‘01’)’).Please advise.

rocky (BOB member since 2005-03-29)