Home » SQL & PL/SQL » SQL & PL/SQL » Writing Query but not getting desired output
Writing Query but not getting desired output [message #677533] Tue, 24 September 2019 13:47 Go to next message
chavva.kiru@gmail.com
Messages: 22
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_tx_setting)values(14636,HCSCTSO);
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);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12185,1654545,14636,sysdate- 4);


I want to get a o/p
after Joining a,b,c tables with total no of Latest_record from table C.

Actually My requirement is to count the closed_tickets for a Particular Person.

I Used Co related subquery to get the latest record by using rank Function and Iam getting error as b.B_ID not found.
what is the other way to write the query?

select a.A_ID,c.C_ID,(select * from (select rank() over(order by c.trans_date desc) rn
from C c where a.A_ID=b.B_A_PK
and c.c_a_PK=a.A_ID
and c.c_b_PK=b.B_ID) where rn=1)
Closed_ticket_count from A a,B b,C c
where a.A_ID=b.B_A_PK
and c.c_a_PK=a.A_ID
and c.c_b_PK=b.B_ID
/




Re: Writing Query but not getting desired output [message #677534 is a reply to message #677533] Tue, 24 September 2019 14:03 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
You should actually TEST all posted SQL to ensure they are correct since multiple problems exist in posted SQL above
Re: Writing Query but not getting desired output [message #677535 is a reply to message #677533] Tue, 24 September 2019 14:16 Go to previous messageGo to next message
chavva.kiru@gmail.com
Messages: 22
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_tx_setting)values(14636,'HCSCTSO');
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);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12185,1654545,14636,sysdate- 4);


I want to get a o/p
after Joining a,b,c tables with total no of Latest_record from table C.

Actually My requirement is to count the closed_tickets for a Particular Person.I want to get only 1 row containing all closed ticket count..

I Used Co related subquery to get the latest record by using rank Function and Iam getting error as b.B_ID not found.
what is the other way to write the query?

select a.A_ID,c.C_ID,(select * from (select rank() over(order by c.trans_date desc) rn
from C c where a.A_ID=b.B_A_PK
and c.c_a_PK=a.A_ID
and c.c_b_PK=b.B_ID) where rn=1)
Closed_ticket_count from A a,B b,C c
where a.A_ID=b.B_A_PK
and c.c_a_PK=a.A_ID
and c.c_b_PK=b.B_ID
/




[/quote]
Re: Writing Query but not getting desired output [message #677537 is a reply to message #677534] Tue, 24 September 2019 20:00 Go to previous messageGo to next message
chavva.kiru@gmail.com
Messages: 22
Registered: April 2012
Location: hyderabad
Junior Member
Hi,


Actually I tested it.Can you Please test the desired o/p

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_tx_setting)values(14636,HCSCTSO);
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);
insert into C(c_id,c_a_pk,c_b_pk,trans_date)values(12185,1654545,14636,sysdate- 4);


I want to get a o/p
after Joining a,b,c tables with total no of Latest_record from table C.

Actually My requirement is to count the closed_tickets for a Particular Person.

I Used Co related subquery to get the latest record by using rank Function and Iam getting error as b.B_ID not found.
what is the other way to write the query?

select a.A_ID,c.C_ID,(select * from (select rank() over(order by c.trans_date desc) rn
from C c where a.A_ID=b.B_A_PK
and c.c_a_PK=a.A_ID
and c.c_b_PK=b.B_ID) where rn=1)
Closed_ticket_count from A a,B b,C c
where a.A_ID=b.B_A_PK
and c.c_a_PK=a.A_ID
and c.c_b_PK=b.B_ID
/
Re: Writing Query but not getting desired output [message #677539 is a reply to message #677537] Tue, 24 September 2019 20:55 Go to previous messageGo to next message
clementstore
Messages: 11
Registered: December 2011
Junior Member
Are u sending this to every one in private and also in the public board ? As I received it and also see the post here. It is highly undesirable to send to others query randomly just to get their attention or answers. It is a bleaching of the rules for the forum.
Re: Writing Query but not getting desired output [message #677541 is a reply to message #677537] Wed, 25 September 2019 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67236
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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: Writing Query but not getting desired output [message #677547 is a reply to message #677537] Wed, 25 September 2019 05:50 Go to previous message
flyboy
Messages: 1886
Registered: November 2006
Senior Member
Hi,

Quote:
Can you Please test the desired o/p
Can you please provide desired output for posted data. I am unable to deduce it from the below statement.
Quote:
after Joining a,b,c tables with total no of Latest_record from table C.
The posted query does not contain anything from table B in the SELECT clause. Shall be table B used here?
What is the desired value of CLOSED_TICKET_COUNT for posted data? Which rules shall be applied there?

I would say the posted query should fail due to non-existence of column B.B_A_PK in sample tables.
The condition on it in WHERE clause looks superfluous. Is your data model really that redundant?

Even if the query would not fail, it would return the value "1" in the column CLOSED_TICKET_COUNT due to the condition in the subquery (SELECT RN ... WHERE RN=1).
Quote:
Actually My requirement is to count the closed_tickets for a Particular Person.
I have no idea what are you talking about and how is it related to posted data.
Previous Topic: co related subquery written in Analytical functions
Next Topic: SQL to find Consecutive Halves
Goto Forum:
  


Current Time: Thu Jul 09 14:04:41 CDT 2020