Home » SQL & PL/SQL » SQL & PL/SQL » sql to return a flag rather than records
sql to return a flag rather than records [message #270906] Fri, 28 September 2007 05:49 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I want my sql query to return a flag rather than I check whether resultset has rows or not


select employee_id from employees where age=20

This query would return list of employee_id whose age is 20
I want a query which would return ture if query returned result
how can I do this ie if atleast one employee age is 20 retrun true if not false .
Re: sql to return a flag rather than records [message #270908 is a reply to message #270906] Fri, 28 September 2007 06:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SQL won't return a Boolean datatype - that is a Pl/Sql type, not an SQL one.

You can get your SQL to return a Y/N like this:
SELECT DECODE(count(*),0,'N','Y')
FROM   employees
WHERE  age = 20
Re: sql to return a flag rather than records [message #270933 is a reply to message #270906] Fri, 28 September 2007 07:54 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Don't forget about exists.
Re: sql to return a flag rather than records [message #270949 is a reply to message #270906] Fri, 28 September 2007 09:59 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
 select nvl(max(flag),'N')
 from
 (
 select 'Y' flag
 from dual
 where exists
 (select null
 from EMPLOYEES
 where AGE=20));


This is a quick query because exists will stop looking as soon as it finds any employyee with an age of 20.
Re: sql to return a flag rather than records [message #271118 is a reply to message #270906] Sat, 29 September 2007 10:20 Go to previous messageGo to next message
aketi
Messages: 26
Registered: October 2005
Junior Member
or

SELECT DECODE(count(*),0,'N','Y')
FROM employees
WHERE age = 20
and rowNum=1
Re: sql to return a flag rather than records [message #271120 is a reply to message #271118] Sat, 29 September 2007 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why "rownum=1"? Do you think the query can return more than 1 row?

Regards
Michel
Re: sql to return a flag rather than records [message #271133 is a reply to message #271120] Sat, 29 September 2007 22:15 Go to previous messageGo to next message
aketi
Messages: 26
Registered: October 2005
Junior Member
>Do you think the query can return more than 1 row?

No.
return row is one.

However
If employees has a lot of rows whose age equals 20,
"RowNum=1" will use CountStop.

Therefore, we can decrease the cost.

for instance,
create table employees(age number(3));

begin
    for i in 1..9999999 Loop
        insert into employees values(mod(i,30));
        commit;
    end Loop;
end;
/

set timi on

SELECT DECODE(count(*),0,'N','Y')
FROM employees
WHERE age = 20
  and RowNum=1;

SELECT DECODE(count(*),0,'N','Y')
FROM employees
WHERE age = 20;


Query with RowNum=1 is sometimes faster.

Re: sql to return a flag rather than records [message #271145 is a reply to message #271133] Sun, 30 September 2007 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If employees has a lot of rows whose age equals 20,
"RowNum=1" will use CountStop

It wil use stopcount but not for the employees, for the final result set and is useless here.

If you want to enhance the query in this way there you have to use:
SELECT DECODE(count(*),0,'N','Y')
FORM (SELECT empid FROM employees WHERE age = 20 and rowNum=1)
/

Regards
Michel
Re: sql to return a flag rather than records [message #271161 is a reply to message #271145] Sun, 30 September 2007 04:59 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Psst, [whisper]Michel FROM not FORM Thumbs Up but I don't think anyone noticed[/whisper] Very Happy

OK, Now this makes NO SENSE Laughing

[Updated on: Sun, 30 September 2007 05:00]

Report message to a moderator

Previous Topic: grant required to create the table through API
Next Topic: Getting this ora-06550
Goto Forum:
  


Current Time: Sun Dec 04 04:43:25 CST 2016

Total time taken to generate the page: 0.08242 seconds