hoping this is simple... I have 4 fields with case statements to return a value, eg. when per type shows 20 then show value else null, when per type shows 40 then value else null.
My issue is that it breaks these into separate lines, I am doing a union query and have the case statements on sub queries.
booking 20 40 40hq 40rf to booking 20 40 40hq 40rf
abc 100 abc 100 200 300 400
abc 200
abc 300
abc 400
Put a total on each of the fields in the UNION query.
thanks for quick replies BigChris... I did this but I got 'sqlPrepareWithOptions' status='-9'. error so not sure what the issue is I think because it is text instead of number... ?
That could be it - I was getting some -9 errors yesterday that just disappeared about 10 minutes later. I didn't change anything...the report just worked again after it had been failing.
Anyway, if you can post the actual error message we might be able to shed some light on it for you.
below is what I get
RQP-DEF-0177
An error occurred while performing operation 'sqlOpenResult' status='-9'.
my case statement is below, the Amount/percentage should be numeric.
case [PER Code]
when in ('20','D2','R2','O2','F2','T2','P2') THEN [Amount/Percentage(%)]
ELSE NULL
end
Ahhh...I think I can see the problem (but I could be wrong)
Trycase
when [PER Code] in ('20','D2','R2','O2','F2','T2','P2') THEN [Amount/Percentage(%)]
ELSE NULL
end
hmm changed that but now I get a -16 instead of -9 error?