OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query [message #370753]

Mon, 31 January 2000 00:37

shailedra
Messages: 2
Registered: January 2000

Junior Member

I need help to solve following query in oracle.
We have scott/tiger login.
We have DEPT table containing 4 records.
I want following query in sigle sql statement-
IF (LOC IS NULL) THEN
SELECT * FROM DEPT;
ELSE SELECT * FROM DEPT WHERE LOC='BOSTON';
END IF;

Report message to a moderator

Re: query [message #370755 is a reply to message #370753]

Mon, 31 January 2000 14:38

Roger D. Cornejo
Messages: 5
Registered: January 2000

Junior Member

How about:
SELECT *
FROM dept
WHERE NVL(loc, 'BOSTON') = 'BOSTON'
;
This will give you the row if it's NULL or it's BOSTON.
Perhaps your problem statement needs more clarification, because it suggests that if a row value is null, then let me see all the records in the table. Otherwise let me see only some of the values (where loc=boston).
With this more strict interpretation I'd probably write:
SELECT * FROM dept
WHERE EXISTS
(SELECT 'x' FROM dept WHERE loc IS NULL)
/*if this evaluates to true, all rows return*/
/*if it evaluates to false, ... */
OR
/* return rows where loc = BOSTON */
loc = 'BOSTON'
;

Is this a real problem or just a quiz?

Regards,
Roger ---|-

Report message to a moderator

Re: query [message #370756 is a reply to message #370753]

Mon, 31 January 2000 16:11

Paul
Messages: 164
Registered: April 1999

Senior Member

Roger,
I've must have looked at this one twenty times, really think it's either a very strange excercise, or a joke - if I'm reading the spec's right, the only way to do what is being requested
would be with PL/SQL - as in this pseudocode:

Create a cursor to return loc from dept
Create a cursor to return all columns from dept
Create a cursor to return all columns from dept where loc = 'BOSTON'
begin
open the 'loc' cursor
loop fetching each record
if loc is null
open the 'all - all' cursor
loop fetching each record
output the cursor content
end loop
close the 'all - all' cursor
else
open the 'all - Boston' cursor
loop fetching each record
output the cursor content
end loop
close the 'all - Boston' cursor
end if
end loop
close the 'loc' cursor
end

But why would anyone want to do this kind of processing? (Besides, using the 'stock' SCOTT/TIGER DEPT table, it would only output 4 iterations of the single row where loc='BOSTON').

You may be right, your solution, or just
SELECT *
FROM DEPT
WHERE LOC = 'BOSTON'
OR LOC IS NULL;

may be what is really desired; but if this is the case, the request sure doesn't make it clear.

Regards,
Paul

Report message to a moderator