Home » SQL & PL/SQL » SQL & PL/SQL » Co-related Subquery Throwing Error While Using rank function
Co-related Subquery Throwing Error While Using rank function [message #677636] |
Tue, 01 October 2019 12:37 |
|
chavva.kiru@gmail.com
Messages: 23 Registered: April 2012 Location: hyderabad
|
Junior Member |
|
|
Hi,
Create table A
(
a_id number,
a_first_clinical_owner varchar2(25),
a_policy_type varchar2(25)
)
create table B
(
b_id number,
b_a_pk varchar2(25)
)
create table C
(
c_id number,
c_a_pk number,
c_b_pk number,
trans_date date
)
insert into A(a_id,a_first_clinical_owner,a_policy_type) values(1654545,'MSclinician','Retail');
insert into B(b_id,b_a_pk)values(14636,1654545);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12181,1654545,14636,sysdate);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12182,1654545,14636,sysdate-1);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12183,1654545,14636,sysdate-2);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12184,1654545,14636,sysdate-3);
select c.C_ID,c.C_A_PK,c.C_B_PK, (select count(*) from (select rank() over(Order by trans_date desc) from C c1
where a.A_ID=c1.C_A_PK
and b.B_ID=c1.C_B_PK)where rn=1) rn from A a,B b,C c
where a.A_ID=B.B_A_PK
and a.A_ID=c.C_A_PK
and b.B_ID=c.C_B_PK
Throwing error as B.B_ID as Invalid Identifier.
How Can We rewrite The Query Such That I have to get O/P as
12181 1654545 14636 01-10-19 22:30
12182 1654545 14636 30-09-19 22:30
12183 1654545 14636 29-09-19 22:30
12184 1654545 14636 28-09-19 22:30
12185 1654545 14636 27-09-19 22:31
latest Record of 12185 to be displayed out of 2
|
|
|
Re: Co-related Subquery Throwing Error While Using rank function [message #677637 is a reply to message #677636] |
Tue, 01 October 2019 12:59 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
Your wanted logic/output of "latest Record of 12185 to be displayed out of 2" is hard to understand, you may want to rephrase it.
As for getting the data by some type of order, you can modify the "order by" in the "rank" statement in the below query:
select
c.C_ID,
c.C_A_PK,
c.C_B_PK,
c.trans_date,
rank() over (order by trans_date desc)
from A a,B b,C c
where
a.A_ID=c.C_A_PK and
b.B_ID=c.C_B_PK
order by
5;
JP
[Updated on: Tue, 01 October 2019 12:59] Report message to a moderator
|
|
|
Re: Co-related Subquery Throwing Error While Using rank function [message #677638 is a reply to message #677636] |
Tue, 01 October 2019 13:09 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Wed, 25 September 2019 08:04
From your previous topics:
Michel Cadot wrote on Wed, 25 September 2019 08:03Michel Cadot wrote on Thu, 06 October 2016 07:30
From your previous topic:
Michel Cadot wrote on Mon, 19 January 2015 07:22
From your previous topic:
Michel Cadot wrote on Thu, 16 August 2012 16:20From your previous topics:
BlackSwan wrote on Wed, 11 April 2012 16:43Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/
BlackSwan wrote on Wed, 11 April 2012 20:29...
READ & FOLLOW the Posting Guidelines! http://www.orafaq.com/forum/t/88153/0/
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
Regards
Michel
In addition, Barbara helped you so much in your previous topics providing you codes and examples and you did never feedback and thank her.
Are you the kind of parasite we saw too much in these days?
Or are you a valuable person who deserves to be helped?
|
|
|
Re: Co-related Subquery Throwing Error While Using rank function [message #677640 is a reply to message #677636] |
Wed, 02 October 2019 00:45 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
I wonder why you do not continue in your previous thread with the same question: http://www.orafaq.com/forum/t/206102/
It is nice to see the column B.B_A_PK used in sample query was added to test case DDL.
Unfortunately, its data type is VARCHAR2(25), so its use to equi join it with A.A_ID (data type NUMBER) is dubious.
Is this column really that important to include it?
Also, function COUNT is now used in the sample query instead of RANK.
However, as the required output still does not match the sample query and text description - it is just input data ordered by TRANS_DATE with one extra row coming out of nowhere - it is really impossible to suggest anything.
Good luck.
|
|
|
Re: Co-related Subquery Throwing Error While Using rank function [message #677643 is a reply to message #677640] |
Wed, 02 October 2019 08:01 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Your example makes no sense. You example data indicates the the value in column "C_ID" from table "C" needs to contains the value 12185, but your example inserts end at 12184. Where does the 12185 come from. if you are trying to only display the latest TRANS_DATE for the specific C_ID then the following will do the job
SELECT C_id,
C_a_pk,
C_b_pk,
Trans_date
FROM (
SELECT C.C_id,
C.C_a_pk,
C.C_b_pk,
C.Trans_date,
ROW_NUMBER () OVER (PARTITION BY C.C_id ORDER BY Trans_date DESC) RN
FROM A A, B B, C C
WHERE A.A_id = C.C_a_pk AND B.B_id = C.C_b_pk)
WHERE RN = 1
ORDER BY 1
|
|
|
Re: Co-related Subquery Throwing Error While Using rank function [message #677644 is a reply to message #677643] |
Wed, 02 October 2019 09:44 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
OP is looking for extra row. Something like:
with t as (
select *
from a,
b,
c
where b.b_a_pk = a.a_id
and c.c_a_pk = a.a_id
and c.c_b_pk = b.b_id
)
select nvl(c_id,max(c_id) over() + 1) c_id,
nvl(a_id,max(a_id) over()) a_id,
nvl(b_id,max(b_id) over()) b_id,
nvl(trans_date,last_value(trans_date ignore nulls) over(order by c_id) - 1 + 1 / 24 / 60) trans_date
from t
group by grouping sets((),(c_id,a_id,b_id,trans_date))
/
C_ID A_ID B_ID TRANS_DATE
---------- ---------- ---------- --------------
12181 1654545 14636 01-10-19 22:30
12182 1654545 14636 30-09-19 22:30
12183 1654545 14636 29-09-19 22:30
12184 1654545 14636 28-09-19 22:30
12185 1654545 14636 27-09-19 22:31
SQL>
SY.
|
|
|
Re: Co-related Subquery Throwing Error While Using rank function [message #677645 is a reply to message #677644] |
Wed, 02 October 2019 09:54 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Another solution:
with t as (
select c_id,
a_id,
b_id,
trans_date,
max(c_id) over() max_c_id
from a,
b,
c
where b.b_a_pk = a.a_id
and c.c_a_pk = a.a_id
and c.c_b_pk = b.b_id
)
select c_id,
a_id,
b_id,
trans_date
from t
union all
select c_id + 1,
a_id,
b_id,
trans_date - 1 + 1 / 24 / 60
from t
where c_id = max_c_id
/
C_ID A_ID B_ID TRANS_DATE
---------- ---------- ---------- --------------
12181 1654545 14636 01-10-19 22:30
12182 1654545 14636 30-09-19 22:30
12183 1654545 14636 29-09-19 22:30
12184 1654545 14636 28-09-19 22:30
12185 1654545 14636 27-09-19 22:31
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Sat Jun 15 16:15:43 CDT 2024
|