I have a question regarding the IF BEGIN END SQL statement in Cognos. I have a pass through query that looks something like this
DECLARE @facility_code INT
SET @facility_code = '-7'
IF @facility_code = '-1'
BEGIN
(query)
END
ELSE IF @facility_code = '-2'
BEGIN
(query)
END
ELSE.......etc
I think you get the idea. It will validate ok, but when I go to run the report, it gives me the "RQP-DEF-0192 At least one expression in the Select clause is missing the AS clause to make it a proper alias" error. Has anyone had this problem before? Can you use the IF BEGIN in Cognos? If so, did you do it differently than this, or should I just give up and stick it into a stored procedure? Thanks in advance for looking at this.
I would not try to put a procedural T-sql script in a pass-through query. Cognos expects a non-procedural set-based script. If you need procedural logic I would put this in a stored procedure or a database function and keeps things entirely on the database side. Other than that, what are you trying to do?
Validation is probably endorsed as the code is tested against the database for semantics, which are probably okay..
Yeah, that's what I was afraid of. I will just slap this into a stored procedure, and go from there. I appreciate your help.