Home » SQL & PL/SQL » SQL & PL/SQL » DRL for a particular condition
| DRL for a particular condition [message #639319] |
Mon, 06 July 2015 04:01  |
 |
saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai
|
Junior Member |
|
|
HI All,
I need a query to retrieve the data
down the table I need to get the records for the given tranches
first the condition need to check awr_type='award' if exists then it need to retrieve only that
record where awr_type='award' for the given tranches.
if not then it need to check awr_type='fund'
and should retrieve only the record where awr_type='fund'.
other wise it should return null.
drop table t_t cascade constraints;
create table t_t (awr_uid number,awr_type varchar2(100),tranche number);
insert into t_t values (1,'award',145);
insert into t_t values (2,'fund',167);
insert into t_t values (3,'retrive',123);
insert into t_t values (4,'llive',145);
insert into t_t values (5,'cunver',125);
insert into t_t values (6,'rund',123);
for eg:
select * from t_t where tranche in (145,123,167) and ----------;
awr_uid awr_type tranche
1 award 145
select * from t_t where tranche in (167,123,125) and -----------;
awr_uid awr_type tranche
2 fund 167
select * from t_t where tranche in (123,125) and ------;
awr_uid awr_type tranche
if you guys have any doubts reg the question pls comment below
tnx .
[Updated on: Mon, 06 July 2015 04:23] Report message to a moderator |
|
|
|
| Re: DRL for a particular condition [message #639321 is a reply to message #639319] |
Mon, 06 July 2015 04:22  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
SQL> WITH DATA AS(
2 SELECT *
3 FROM t_t
4 WHERE tranche IN (145,123,167)
5 AND
6 CASE
7 WHEN awr_type = 'award'
8 OR awr_type = 'fund'
9 THEN 1
10 END = 1
11 )
12 SELECT awr_uid, awr_type, tranche
13 FROM
14 ( SELECT t.*, row_number() OVER(ORDER BY t.awr_uid) rn FROM DATA t
15 )
16 WHERE rn = 1;
AWR_UID AWR_TYPE TRANCHE
---------- -------- ----------
1 award 145
SQL>
SQL> WITH DATA AS(
2 SELECT *
3 FROM t_t
4 WHERE tranche IN (167,123,125)
5 AND
6 CASE
7 WHEN awr_type = 'award'
8 OR awr_type = 'fund'
9 THEN 1
10 END = 1
11 )
12 SELECT awr_uid, awr_type, tranche
13 FROM
14 ( SELECT t.*, row_number() OVER(ORDER BY t.awr_uid) rn FROM DATA t
15 )
16 WHERE rn = 1;
AWR_UID AWR_TYPE TRANCHE
---------- -------- ----------
2 fund 167
SQL>
SQL> WITH DATA AS(
2 SELECT *
3 FROM t_t
4 WHERE tranche IN (123,125)
5 AND
6 CASE
7 WHEN awr_type = 'award'
8 OR awr_type = 'fund'
9 THEN 1
10 END = 1
11 )
12 SELECT awr_uid, awr_type, tranche
13 FROM
14 ( SELECT t.*, row_number() OVER(ORDER BY t.awr_uid) rn FROM DATA t
15 )
16 WHERE rn = 1;
no rows selected
SQL> |
|
|
|
| Re: DRL for a particular condition [message #639322 is a reply to message #639321] |
Mon, 06 July 2015 04:27 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
You can get rid of that sub-query for ranking, and do it in the WITH clause itself. However, I am assuming that when you get both the rows for 'award' and 'fund', you would always have the awr_uid for 'award' as 1, as the ranking depends on that.
SQL> WITH DATA AS(
2 SELECT t.*, row_number() OVER(ORDER BY t.awr_uid) rn
3 FROM t_t t
4 WHERE tranche IN (145,123,167)
5 AND
6 CASE
7 WHEN awr_type = 'award'
8 OR awr_type = 'fund'
9 THEN 1
10 END = 1
11 )
12 SELECT awr_uid, awr_type, tranche
13 FROM data
14 WHERE rn = 1;
AWR_UID AWR_TYPE TRANCHE
---------- -------- ----------
1 award 145
SQL>
SQL> WITH DATA AS(
2 SELECT t.*, row_number() OVER(ORDER BY t.awr_uid) rn
3 FROM t_t t
4 WHERE tranche IN (167,123,125)
5 AND
6 CASE
7 WHEN awr_type = 'award'
8 OR awr_type = 'fund'
9 THEN 1
10 END = 1
11 )
12 SELECT awr_uid, awr_type, tranche
13 FROM data
14 WHERE rn = 1;
AWR_UID AWR_TYPE TRANCHE
---------- -------- ----------
2 fund 167
SQL>
SQL> WITH DATA AS(
2 SELECT t.*, row_number() OVER(ORDER BY t.awr_uid) rn
3 FROM t_t t
4 WHERE tranche IN (123,125)
5 AND
6 CASE
7 WHEN awr_type = 'award'
8 OR awr_type = 'fund'
9 THEN 1
10 END = 1
11 )
12 SELECT awr_uid, awr_type, tranche
13 FROM data
14 WHERE rn = 1;
no rows selected
SQL>
Regards,
Lalit
|
|
|
|
| Re: DRL for a particular condition [message #639325 is a reply to message #639319] |
Mon, 06 July 2015 05:00 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 data as (
3 select t.*,
4 row_number() over (order by decode(awr_type, 'award',1, 'fund',2)) rn
5 from t_t t
6 where awr_type in ('award','fund')
7 and tranche in (&tranche)
8 )
9 select awr_uid, awr_type, tranche
10 from data
11 where rn = 1
12 /
Enter value for tranche: 145,123,167
AWR_UID AWR_TYPE TRANCHE
---------- -------- ----------
1 award 145
1 row selected.
SQL> /
Enter value for tranche: 167,123,125
AWR_UID AWR_TYPE TRANCHE
---------- -------- ----------
2 fund 167
1 row selected.
SQL> /
Enter value for tranche: 123,125
no rows selected
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 05:02:56 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
