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 Go to next message
user_5678
Messages: 4
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 240 times)
Count of Pending, Failed, Running, Completed records [message #672425 is a reply to message #672423] Mon, 15 October 2018 05:24 Go to previous messageGo to next message
user_5678
Messages: 4
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 114 times)

[Updated on: Mon, 15 October 2018 05:35]

Report message to a moderator

Re: Count of Pending, Failed, Running, Completed records [message #672426 is a reply to message #672425] Mon, 15 October 2018 05:32 Go to previous messageGo to next message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
So it's completed if there's no entry for the ids in the running, pending and failed tables?
Re: Count of Pending, Failed, Running, Completed records [message #672427 is a reply to message #672423] Mon, 15 October 2018 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
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 #672428 is a reply to message #672427] Mon, 15 October 2018 05:36 Go to previous messageGo to next message
John Watson
Messages: 7954
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

I don't understand your data. You seem to be using ZYZwhatever as both a column name and a column value. The relationships between your tables don't work. If you start again, formatting your code properly with [code] tags, and include the primary key and foreign key constraints then it may become clear.

ps - I have merged your two topics, please do not post duplicates.
Re: Count of Pending, Failed, Running, Completed records [message #672437 is a reply to message #672428] Mon, 15 October 2018 09:50 Go to previous messageGo to next message
user_5678
Messages: 4
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.

  • Attachment: Relations.JPG
    (Size: 207.88KB, Downloaded 222 times)
Re: Count of Pending, Failed, Running, Completed records [message #672439 is a reply to message #672437] Mon, 15 October 2018 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What do you mean by "not exact"? it seems to me it EXACTLY matches the output you posted.

Re: Count of Pending, Failed, Running, Completed records [message #672487 is a reply to message #672423] Tue, 16 October 2018 08:02 Go to previous messageGo to next message
EdStevens
Messages: 1082
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 Go to previous message
user_5678
Messages: 4
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.
Previous Topic: Pulling hair out trying to use table function
Next Topic: SQL Query to be grouped based on Column values
Goto Forum:
  


Current Time: Sun Jul 21 16:22:11 CDT 2019