Home » SQL & PL/SQL » SQL & PL/SQL » Count of Pending, Failed, Running, Completed records (Oracle 12c )
| Count of Pending, Failed, Running, Completed records [message #672423] |
Mon, 15 October 2018 04:43  |
 |
user_5678
Messages: 5
Registered: October 2018
|
Junior Member |
|
|
Hi Experts,
I have the fallowing main table (Tbl_Main) data as fallowing
ID SUB_ID XYZ_ID TYPE_ID
----------------------------------------------------------
XYZ_A_346 XYZ_A_346_P1_556 647 A
XYZ_A_346 XYZ_A_346_P2_557 648 A
XYZ_A_346 XYZ_A_346_P3_558 649 A
XYZ_A_346 XYZ_A_346_P4_559 650 A
XYZ_A_346 XYZ_A_346_P5_560 651 A
XYZ_A_479 XYZ_A_479_P1_771 867 A
XYZ_A_479 XYZ_A_479_P2_772 868 A
XYZ_A_479 XYZ_A_479_P3_773 869 A
XYZ_A_479 XYZ_A_479_P4_774 870 A
XYZ_A_479 XYZ_A_479_P5_775 871 A
XYZ_B_498 XYZ_B_498_P1_802 899 B
XYZ_B_498 XYZ_B_498_P2_803 900 B
XYZ_B_498 XYZ_B_498_P3_804 901 B
XYZ_B_498 XYZ_B_498_P4_805 902 B
XYZ_B_498 XYZ_B_498_P5_806 903 B
XYZ_B_346 XYZ_B_346_P1_560 899 C
XYZ_B_346 XYZ_B_346_P2_559 900 C
XYZ_B_346 XYZ_B_346_P3_557 901 C
XYZ_B_346 XYZ_B_346_P4_556 902 C
XYZ_B_346 XYZ_B_346_P5_558 903 C
Running table (Tbl_Running) data as fallowing
Note : the relation between main table(Tbl_Main) and Tbl_Running is XYZ_ID and XYZ_NAME(here XYZ_NAME 'XYZ_' is concatenated with XYZ_ID)
XYZ_NAME STATUS
-------------------
XYZ_650 RUNNING
XYZ_651 RUNNING
XYZ_902 RUNNING
XYZ_903 RUNNING
Pending table data (Tbl_Pending) data as fallowing
ID SUB_ID XYZ_ID STATUS
----------------------------------------------------------
XYZ_A_346 XYZ_B_346_P1_560 652 PENDING
XYZ_A_479 XYZ_A_479_P1_771 867 PENDING
XYZ_A_479 XYZ_A_479_P1_773 869 PENDING
Failed table data (Tbl_Failed) data as fallowing
ID SUB_ID XYZ_ID STATUS
----------------------------------------------------------
XYZ_B_498 XYZ_B_498_P1_802 899 FAILED
XYZ_A_479 XYZ_A_479_P2_772 868 FAILED
XYZ_A_479 XYZ_A_479_P3_774 870 FAILED
i need to get the output from the main table as
ID TYPE PENDING RUNNING FAILED COMPLETED
XYZ_A_346 A 0 2 0 3
XYZ_A_346 C 1 0 0 4
XYZ_A_479 A 2 0 2 1
XYZ_B_498 B 0 2 1 2
Please find the fallowing table script and insert scripts
Create Table Tbl_Main
(
Id Varchar2(30),
Sub_Id Varchar2(30),
XYZ_Id Number(18),
Type Varchar2(1)
)
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P1_556',647,'A')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P2_557',648,'A')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P3_558',649,'A')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P4_559',650,'A')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P5_560',651,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P1_771',867,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P2_772',868,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P3_773',869,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P4_774',870,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P5_775',871,'A')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P1_802',899,'B')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P2_803',900,'B')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P3_804',901,'B')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P4_805',902,'B')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P5_806',903,'B')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P1_560',652,'C')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P2_559',653,'C')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P3_557',654,'C')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P4_556',655,'C')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P5_558',656,'C')
Create Table Tbl_Running
(
XYZ_Name Varchar2(40),
Status Varchar2(50)
)
Insert Into Tbl_Running Values('XYZ_650','RUNNING');
Insert Into Tbl_Running Values('XYZ_651','RUNNING');
Insert Into Tbl_Running Values('XYZ_902','RUNNING');
Insert Into Tbl_Running Values('XYZ_903','RUNNING');
Create Table Tbl_Pending
(
Id Varchar2(30),
Sub_Id Varchar2(40),
XYZ_Id Number(18),
Status Varchar2(50)
)
Insert Into Tbl_Pending Values('XYZ_A_346','XYZ_B_346_P1_560',652,'PENDING');
Insert Into Tbl_Pending Values('XYZ_A_479','XYZ_A_479_P1_771',867,'PENDING');
Insert Into Tbl_Pending Values('XYZ_A_479','XYZ_A_479_P1_773',869,'PENDING');
Create Table Tbl_Failed
(
Id Varchar2(30),
Sub_Id Varchar2(40),
XYZ_Id Number(18),
Status Varchar2(50)
)
Insert Into Tbl_Failed Values('XYZ_B_498','XYZ_B_498_P1_802',899,'FAILED');
Insert Into Tbl_Failed Values('XYZ_A_479','XYZ_A_479_P2_772',868,'FAILED');
Insert Into Tbl_Failed Values('XYZ_A_479','XYZ_A_479_P3_774',870,'FAILED');
please help me with this.
Thanks in advance.
-
Attachment: output.JPG
(Size: 21.65KB, Downloaded 679 times)
|
|
|
|
| Count of Pending, Failed, Running, Completed records [message #672425 is a reply to message #672423] |
Mon, 15 October 2018 05:24  |
|
user_5678
Messages: 5
Registered: October 2018
|
Junior Member |
|
|
Hi Experts,
I have the main table (Tbl_Main) data as fallowing
ID SUB_ID XYZ_ID TYPE
------------- ------------------ ------ ----
XYZ_A_346 XYZ_A_346_P1_556 647 A
XYZ_A_346 XYZ_A_346_P2_557 648 A
XYZ_A_346 XYZ_A_346_P3_558 649 A
XYZ_A_346 XYZ_A_346_P4_559 650 A
XYZ_A_346 XYZ_A_346_P5_560 651 A
XYZ_A_479 XYZ_A_479_P1_771 867 A
XYZ_A_479 XYZ_A_479_P2_772 868 A
XYZ_A_479 XYZ_A_479_P3_773 869 A
XYZ_A_479 XYZ_A_479_P4_774 870 A
XYZ_A_479 XYZ_A_479_P5_775 871 A
XYZ_B_498 XYZ_B_498_P1_802 899 B
XYZ_B_498 XYZ_B_498_P2_803 900 B
XYZ_B_498 XYZ_B_498_P3_804 901 B
XYZ_B_498 XYZ_B_498_P4_805 902 B
XYZ_B_498 XYZ_B_498_P5_806 903 B
XYZ_B_346 XYZ_B_346_P1_560 899 C
XYZ_B_346 XYZ_B_346_P2_559 900 C
XYZ_B_346 XYZ_B_346_P3_557 901 C
XYZ_B_346 XYZ_B_346_P4_556 902 C
XYZ_B_346 XYZ_B_346_P5_558 903 C
Running table (Tbl_Running) data as fallowing
XYZ_NAME STATUS
--------- --------
XYZ_650 RUNNING
XYZ_651 RUNNING
XYZ_902 RUNNING
XYZ_903 RUNNING
Note: The main table (Tbl_Main) and Running table (Tbl_Running) relation is XYZ_ID and XYZ_NAME('XYZ_' concatenated with XYZ_ID)
Pending table (Tbl_Pending) data as fallowing
ID SUB_ID XYZ_ID STATUS
------------ ----------------- ---------- ----------
XYZ_A_346 XYZ_B_346_P1_560 652 PENDING
XYZ_A_479 XYZ_A_479_P1_771 867 PENDING
XYZ_A_479 XYZ_A_479_P1_773 869 PENDING
Failed table (Tbl_Failed) data as fallowing
ID SUB_ID XYZ_ID STATUS
------------ ------------------- --------- -----------
XYZ_B_498 XYZ_B_498_P1_802 899 FAILED
XYZ_A_479 XYZ_A_479_P2_772 868 FAILED
XYZ_A_479 XYZ_A_479_P3_774 870 FAILED
Out put
---------
from the above tables we need to get the fallowing output
count count count count
of of of of
ID TYPE PENDING RUNNING FAILED COMPLETED
----------- ------- ---------- --------- -------- -------------
XYZ_A_346 A 0 2 0 3
XYZ_A_346 C 1 0 0 4
XYZ_A_479 A 2 0 2 1
XYZ_B_498 B 0 2 1 2
Please find the below table and insert scripts
Create Table Tbl_Main
(
Id Varchar2(30),
Sub_Id Varchar2(30),
XYZ_Id Number(18),
Type Varchar2(1)
)
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P1_556',647,'A')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P2_557',648,'A')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P3_558',649,'A')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P4_559',650,'A')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_A_346_P5_560',651,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P1_771',867,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P2_772',868,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P3_773',869,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P4_774',870,'A')
Insert Into Tbl_Main Values('XYZ_A_479','XYZ_A_479_P5_775',871,'A')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P1_802',899,'B')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P2_803',900,'B')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P3_804',901,'B')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P4_805',902,'B')
Insert Into Tbl_Main Values('XYZ_B_498','XYZ_B_498_P5_806',903,'B')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P1_560',652,'C')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P2_559',653,'C')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P3_557',654,'C')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P4_556',655,'C')
Insert Into Tbl_Main Values('XYZ_A_346','XYZ_B_346_P5_558',656,'C')
Create Table Tbl_Running
(
XYZ_Name Varchar2(40),
Status Varchar2(50)
)
Insert Into Tbl_Running Values('XYZ_650','RUNNING');
Insert Into Tbl_Running Values('XYZ_651','RUNNING');
Insert Into Tbl_Running Values('XYZ_902','RUNNING');
Insert Into Tbl_Running Values('XYZ_903','RUNNING');
Create Table Tbl_Pending
(
Id Varchar2(30),
Sub_Id Varchar2(40),
XYZ_Id Number(18),
Status Varchar2(50)
)
Insert Into Tbl_Pending Values('XYZ_A_346','XYZ_B_346_P1_560',652,'PENDING');
Insert Into Tbl_Pending Values('XYZ_A_479','XYZ_A_479_P1_771',867,'PENDING');
Insert Into Tbl_Pending Values('XYZ_A_479','XYZ_A_479_P1_773',869,'PENDING');
Create Table Tbl_Failed
(
Id Varchar2(30),
Sub_Id Varchar2(40),
XYZ_Id Number(18),
Status Varchar2(50)
)
Insert Into Tbl_Failed Values('XYZ_B_498','XYZ_B_498_P1_802',899,'FAILED');
Insert Into Tbl_Failed Values('XYZ_A_479','XYZ_A_479_P2_772',868,'FAILED');
Insert Into Tbl_Failed Values('XYZ_A_479','XYZ_A_479_P3_774',870,'FAILED');
Please help me with this
Thanks in advance.
-
Attachment: output.JPG
(Size: 21.65KB, Downloaded 569 times)
[Updated on: Mon, 15 October 2018 05:35] Report message to a moderator |
|
|
|
|
|
| Re: Count of Pending, Failed, Running, Completed records [message #672427 is a reply to message #672423] |
Mon, 15 October 2018 05:34 |
 |
Michel Cadot
Messages: 68447
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Thanks for the test case (take care it run with SQL*Plus, some semi-colons are missing).
Please read How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
SQL> select 'XYZ_'||m.id id, m.type,
2 count(p.xyz_id) pending,
3 count(r.xyz_name) running,
4 count(f.xyz_id) failed,
5 count(*)-count(p.xyz_id)-count(r.xyz_name)-count(f.xyz_id) completed
6 from tbl_main m, tbl_running r, tbl_pending p, tbl_failed f
7 where r.xyz_name (+) = 'XYZ_'||m.xyz_id
8 and p.xyz_id (+) = m.xyz_id
9 and f.xyz_id (+) = m.xyz_id
10 group by m.id, m.type
11 order by 1, 2
12 /
ID T PENDING RUNNING FAILED COMPLETED
---------------------------------- - ---------- ---------- ---------- ----------
XYZ_XYZ_A_346 A 0 2 0 3
XYZ_XYZ_A_346 C 1 0 0 4
XYZ_XYZ_A_479 A 2 0 2 1
XYZ_XYZ_B_498 B 0 2 1 2 |
|
|
|
|
|
| Re: Count of Pending, Failed, Running, Completed records [message #672437 is a reply to message #672428] |
Mon, 15 October 2018 09:50 |
|
user_5678
Messages: 5
Registered: October 2018
|
Junior Member |
|
|
cookiemonster,
Yes its completed if the records is not there in any one of pending, running and failed tables.
Michel Cadot,
I have run the code you provided but result is not exact.
i am sorry for not providing proper details
my oracle version is
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
John Watson,
Yes, there is no proper relation between the tables but i need to show the fallowing data.
please help me in this regard, if it can be done in any other way.
i am uploading the image for your reference, please check the image.
Thanks in advance.
|
|
|
|
|
|
| Re: Count of Pending, Failed, Running, Completed records [message #672487 is a reply to message #672423] |
Tue, 16 October 2018 08:02 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
To put it bluntly, your data model is junk.
Why do you have three tables, RUNNING, PENDING, and FAILED, each with a "status" column whose values simply replicate the table name? All this should be in a single table, where the STATUS column actually has some meaning, and you are not (essentially) moving data from one table to another as its status changes.
|
|
|
|
| Re: Count of Pending, Failed, Running, Completed records [message #672503 is a reply to message #672487] |
Wed, 17 October 2018 00:39 |
|
user_5678
Messages: 5
Registered: October 2018
|
Junior Member |
|
|
Thanks for the quick reply Michel Cadot,
Your query is working fine, to get the desired output which i am looking for i may need to add some extra conditions for my tables.
really you guys are amazing in responding to a problem.
EdStevens, yes the tables are not designed properly i agree with that, but they are old tables.
Once again thank you very much guys for spending your time on this.
cookiemonster, Michel, John Watson and EdStevens
Thanks.
|
|
|
|
Goto Forum:
Current Time: Sun May 28 18:47:00 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
