bug with if ... then ... clause in views

On Jan 18, 2006, at 18:18 , Emil Rachovsky wrote:

CREATE OR REPLACE VIEW public.SomeView
as select d.id,
if (true) then d.DocNumber endif from
public.Z_Documents as d;

I get the following error :
syntax error at or near "then" at character 72

Well, one problem is that IF ... ENDIF is the correct syntax. You can
use it in PL/pgSQL, but that's a procedural language, and not what
you're doing here. You're probably looking for CASE, e.g.,

CREATE OR REPLACE VIEW public."SomeView" AS
SELECT d.id
, CASE WHEN TRUE
THEN d."DocNumber"
ELSE something_else
END as "DocNumber"
FROM public."Z_Documents" as d;

Note you need an ELSE clause: you can't have a variable number of
columns in the view (just like you can't have a variable number of
columns in a table). As an aside, you need to double-quote
identifiers if you want them to be case-sensitive: otherwise they'll
be down-cased.

Hope this helps.

Michael Glaesemann
grzm myrealbox com

On Jan 18, 2006, at 4:18 AM, Emil Rachovsky wrote:

While trying to create some views I stumbled on some
problem with using the if-then clause. Here is a
simple example :

CREATE OR REPLACE VIEW public.SomeView
as select d.id,
if (true) then d.DocNumber endif from
public.Z_Documents as d;

I get the following error :
syntax error at or near "then" at character 72

I don't have a clue what is going on here. Any
suggestions?

I see 2 issues.

1) if *what* is true?
2) AFAIK, there is no IF conditional in SQL. Perhaps you want CASE?
e.g.

CREATE OR REPLACE VIEW SomeView
as
select
d.id,
case
when condition = true then d.doc_number
else 'Bad Doc'
end
from
documents as d;
go

Thanks in advance,
Emil

--
Daryl

"Stress rarely has a positive impact on our ability to think.
Never, I'd guess."

-- Ron Jeffries, 2005

Emil Rachovsky wrote:

While trying to create some views I stumbled on some
problem with using the if-then clause. Here is a
simple example :

CREATE OR REPLACE VIEW public.SomeView
as select d.id,
if (true) then d.DocNumber endif from
public.Z_Documents as d;

I get the following error :
syntax error at or near "then" at character 72

I don't have a clue what is going on here. Any
suggestions?

IF / THEN is not part of SQL. it is part of plpgsql.
However you'll find that CASE can do the same thing.
select d.id, case when true then d.docNumber else 'something else' end
as blah, public.Z_documents as d ...

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/