Thread: Using Control Flow Functions in a SELECT Statement
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
> *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.
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.
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');
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.
> 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.
[ 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