beginner
New User
Joined: 30 Jun 2006
Posts: 1
|
|
|
|
Hi,
I need help regarding the SQL that I am creating.
Actually it's a query. Given a combination of criteria, the SQL should be able to return the record that meets the highest criteria.
Ex:
Record 1 = passes hierarcy 3
Record 2 = passes hierarcy 2
Record 3 = passes hierarcy 1
So when the SQL is executed it should be able to return Record 3.
I'm required not to use a user defined SQL function and I should be able to use one SQL to perform such requirement.
I tried the following SQL
SELECT * FROM
(SELECT NVL(ID,'NONE')
FROM TABLE A, TABLE B
WHERE A.ID = B.ID
AND B.TYPE = HIERARCY 1),
(SELECT NVL(ID,'NONE')
FROM TABLE A, TABLE B
WHERE A.ID = B.ID
AND B.TYPE = HIERARCY 2),
(SELECT NVL(ID,'NONE')
FROM TABLE A, TABLE B
WHERE A.ID = B.ID
AND B.TYPE = HIERARCY 3)
I was expecting that this SQL will return an ID if the select criteria has been met and a 'NONE' value otherwise. This works fine when all 3 select statements have been met. But when one of the select doesn't return an ID, the whole select returns 3 NULL values (one for each select statement).
Can anyone please please help me? |
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
You have us somewhat confused as to what you're trying to do. Can you please give us more information about the table structure, and some examples of data in the table. Please come back, we would like to assist if we can.
Dave |
|
