PostgreSQL SQL - Using Control Flow Functions in a SELECT Statement

Author

Using Control Flow Functions in a SELECT Statement

Ashish Ahlawat

2006-12-04, 7:19 pm

Hi Team

I am unable to fetch data using following simple query it prompts following
error

*ORA: 00907: Missing right parenthesis*

Query :-

SELECT Name AS Title, StatID AS Status, RatingID AS Rating,

IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify

FROM DVDs

*Table Structure :-*

CREATE TABLE DVDs (

Name VARCHAR(60) NOT NULL,

NumDisks INT NOT NULL ,

RatingID VARCHAR(4) NOT NULL,

StatID CHAR(3) NOT NULL

)

rows has been inserted with different numdisks numbers. pls help

warm R's

Richard Broersma Jr

2006-12-04, 7:19 pm

> *ORA: 00907: Missing right parenthesis*
> Query :-
> SELECT Name AS Title, StatID AS Status, RatingID AS Rating,
> IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify

What kind of error is ORA? Is this an oracle error?

In postgresql I do not believe that the "IF" predicate exists. However the "CASE" predicate does
and will do what you want.

http://www.postgresql.org/files/doc...sql/node44.html

Regards,

Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Ashish Ahlawat

2006-12-04, 7:19 pm

Hi Team

Thanks FOR your prompt response....But USING CASE issue still NOT resolved
.....Oracle prompts same error.

Team its a simple query but really instresting one...

Following data has been updated IN TABLE DVDs

INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)

VALUES('Mash', 2, 'R', 's2'), ('View', 1, 'NR', 's1'), ('Show', 2, 'NR',
's2'), ('Amadeus', 1, 'PG', 's2') , ('Falcon', 1, 'NR', 's2'),

('Africa', 1, 'PG', 's1'), ('Doc', 1, 'G', 's2') , ('Christmas', 1, 'NR',
's1');

On 12/4/06, Bricklen Anderson <banderson@presinet.com> wrote:
>
> Ashish Ahlawat wrote:
>
> i. That's an Oracle error message.
> ii. IF does not belong in an SQL query. Use CASE.
>
>

Richard Broersma Jr

2006-12-04, 7:19 pm

> Thanks FOR your prompt response....But USING CASE issue still NOT resolved
> ....Oracle prompts same error.

I see, was answers to you get from the oracle mailing lists regarding this problem? ;o)

Regards,

Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Bricklen Anderson

2006-12-04, 7:19 pm

Ashish Ahlawat wrote:
>
> Hi Team
>
> I am unable to fetch data using following simple query it prompts
> following error
>
> *ORA: 00907: Missing right parenthesis*
>
> Query :-
>
> SELECT
>
> Name AS Title, StatID AS Status, RatingID AS Rating,
>
> IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify
>

i. That's an Oracle error message.
ii. IF does not belong in an SQL query. Use CASE.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Ragnar

2006-12-05, 7:18 pm

[
removing a bunch of probably uninterested people from CC
]

On mán, 2006-12-04 at 22:12 +0530, Ashish Ahlawat wrote:
> Hi Team
>
> Thanks
>
> FOR your prompt response....But USING CASE issue still NOT
> resolved....Oracle prompts same error.

this is a postgresql mailing list, but
I believe that even with oracle, the case statement
should work. a quick google found
http://www.oracle-base.com/articles/9i/Case9i.php

gnari

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links