Among other prompts, we have a Month prompt, and a Year prompt.
A data item is added with a Case When to calculate a Fiscal Year ...
Case
When ?p_TMonth? IN (10,11,12) Then ?p_TCalYr?
When ?p_TMonth? IN (1,2,3,4,5,6,7,8,9) Then ?p_TCalYr? - 1
End
The ?p_TMonth? uses the number of a Month but displays the Month Name.
There are NO dates in the Query object, instead Month Names, and Calendar/ Fiscal Years.
It fails when trying to subtract 1 from ?p_TCalYr?
Using _add_years requires making a timestamp, but we would rather just subtract 1
from the entered ?_p_TCalYr? prompt.
Gotta be simple?
TIA, Bob
Hi,
Did you try the below expression?
Case
When ?p_TMonth? IN (10,11,12) Then ?p_TCalYr?
When ?p_TMonth? IN (1,2,3,4,5,6,7,8,9) Then cast(cast(?p_TCalYr?, int)-1),varchar(4))
End
Good luck
New guy
Thank you New guy.
Just tried ...
Case
When ?p_TMonth? IN (10,11,12) Then ?p_TCalYr?
When ?p_TMonth? IN (1,2,3,4,5,6,7,8,9) Then cast(cast(?p_TCalYr?, int)-1),varchar(4))
End
It gave a parsing error near position 125 which begins at ...
varchar(4))
Hi,
Try this
cast(((cast(?p_TCalYr?, int))-1),varchar(4))
Good luck
New guy
THat DOES calculate the correct year, bur for some reason calling that DEF, and ...
_make_timestamp([DEF],10,1) errors as ...
'SQLPrepareWithOptions' status='-56'
I tried, again ...
Case
When ?p_TMonth? IN (10,11,12) Then ?p_TCalYr?
When ?p_TMonth? IN (1,2,3,4,5,6,7,8,9) Then ?p_TCalYr? - 1
End
... calling that ABC, and doing
_make_timestamp([ABC],10,1) ... works fine.
I will continue to use the Cast info and determine why it errored as '-56'
THANK you for the Cast info, Bob