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 Go to next message
chavva.kiru@gmail.com
Messages: 15
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 Go to previous messageGo to next message
JPBoileau
Messages: 80
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
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:03
Michel 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:20
From your previous topics:

BlackSwan wrote on Wed, 11 April 2012 16:43
Please 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 Go to previous messageGo to next message
flyboy
Messages: 1882
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 Go to previous messageGo to next message
Bill B
Messages: 1917
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
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 Go to previous message
Solomon Yakobson
Messages: 2899
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.
Previous Topic: running total query
Next Topic: Oracle 11g: step by step guide to send email via utl_smtp with all requirements from scratch (3 merged)
Goto Forum:
  


Current Time: Tue Dec 10 00:43:50 CST 2019