Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using Control Flow Functions in a SELECT Statement

Using Control Flow Functions in a SELECT Statement

From
"Ashish Ahlawat"
Date:
 
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

Re: Using Control Flow Functions in a SELECT Statement

From
Richard Broersma Jr
Date:
> *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/documentation/books/aw_pgsql/node44.html

Regards,

Richard Broersma Jr.

Re: Using Control Flow Functions in a SELECT Statement

From
Bricklen Anderson
Date:
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.

Re: Using Control Flow Functions in a SELECT Statement

From
"Ashish Ahlawat"
Date:

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:
>
> 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.


Re: Using Control Flow Functions in a SELECT Statement

From
Richard Broersma Jr
Date:
> 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.

Re: Using Control Flow Functions in a SELECT Statement

From
Ragnar
Date:
[
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