Home » RDBMS Server » Server Administration » query
query [message #370753] Mon, 31 January 2000 00:37 Go to next message
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;
Re: query [message #370755 is a reply to message #370753] Mon, 31 January 2000 14:38 Go to previous messageGo to next message
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 ---|-
Re: query [message #370756 is a reply to message #370753] Mon, 31 January 2000 16:11 Go to previous messageGo to next message
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
Re: query [message #370761 is a reply to message #370753] Tue, 01 February 2000 15:54 Go to previous messageGo to next message
MikeG
Messages: 14
Registered: August 1999
Junior Member
select d1.deptno, d1.dname, nvl(d1.loc,d2.loc) loc
from dept d1, dept d2
where d2.loc = 'BOSTON';

This assumes only 1 row with BOSTON
Re: query [message #371110 is a reply to message #370753] Tue, 29 August 2000 03:14 Go to previous message
Jaya Kumar NM
Messages: 11
Registered: August 2000
Junior Member
You can try this
select * from dept
where loc like '%'||nvl('&loc','%')||'%'
Previous Topic: Re: Multiple Oracles on NT
Next Topic: triggers and C functions
Goto Forum:
  


Current Time: Fri Mar 29 02:58:45 CDT 2024