the function CASE in a view...

[Guest_imported]

hi folks,

when I execute this query:
replace view vw_sales as
select a.anvr_nummer
,a.naam
,a.adres
,a.plaats
,a.postkode
,a.sorteerkode
,res.reserveringsnummer
,res.gidscode
,res.reissom
,CASE res.scope
when 's' then 1
else 0
END 'scope'
,nr.pax
,nr.seizoen
from oad.agent a
inner join oad.reservering res
on a.anvr_nummer = res.anvr_nummer
inner join view_resnummers nr
on res.reserveringsnummer = nr.reserveringsnummer

in Queryman 5.1.1, it returns this error:

3706: Syntax error: expected something between the 'END'keyword and the string 's'keyword.

But when I remove the first line ('replace... as') the query executes fine. Are CASE's prohibited in views or something?

 

[BillDHS]

Put parenthesis around the case statement, and take the quotes off the column name.

,res.reissom
,(CASE res.scope
when 's' then 1
else 0
END) as scope
,nr.pax

You don't need quotes on the alias unless its a reserved word, I don't think scope is, then I believe you use double quotes.

 

[Guest_imported]

Thank you!