OraFAQ Forum: SQL & PL/SQL » Question on SQL query with COUNT, DECODE and DISTINCT

Home » SQL & PL/SQL » SQL & PL/SQL » Question on SQL query with COUNT, DECODE and DISTINCT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Question on SQL query with COUNT, DECODE and DISTINCT [message #38726]

Wed, 08 May 2002 12:01

Ram Natarajan
Messages: 1
Registered: May 2002

Junior Member

Hi,
My table looks like:

create table "Track_tb"
( "ProcName" varchar2(20)
, "PartnerName" varchar2(20)
, "ToPartnerName" varchar2(20)
, "InstanceStatus" varchar2(1)
, "Direction" varchar2(1)
, "ProcID" varchar2(20))
/

Sample data:
Sample data:

ProcName InstanceStatus ProcID
-------------------------------------------------------
PName1 W PId1
PName1 W PId1
PName1 E PId2

Current query looks like:
SELECT DISTINCT "A"."ProcName", COUNT(DECODE("ProcName","A"."ProcName",
DECODE('ABC',"PartnerName", DECODE("Direction", 'R',
DECODE("InstanceStatus",'W', 1)), "ToPartnerName",
DECODE("Direction",'R',NULL, DECODE("InstanceStatus",'W', 1)) )) )
warning_count,
COUNT(DECODE("ProcName","A"."ProcName", DECODE('ABC',"PartnerName", DECODE("Direction",'R', DECODE("InstanceStatus", 'E', 1)), "ToPartnerName",
DECODE("Direction",'R', NULL, DECODE("InstanceStatus",'E', 1)) )) ) error_count
FROM "Track_tb" "A" WHERE ("PartnerName" = 'ABC' AND "Direction" = 'R') OR ("ToPartnerName" = 'ABC' AND "Direction" != 'R') GROUP BY "A"."ProcName"

and the output for the above query is:
ProcName #Warnings #Errors
-----------------------------------------------------
PName1 2 1

My desired output is:
ProcName #Warnings #Errors
------------------------------------------------
PName1 1 1

The result is based on #Warnings and #Errors for distinct ProcId. i.e. if we can do a COUNT(DISTINCT "ProcId".......) then we should get the desired result.
I know using subqueries will make it easy but we are running Oracle 8.05 and 8.17. Since 8.05 does not seem to support subqueries, I cannot use them.

Any help on this is appreciated.

Thanks
Ram

Report message to a moderator

Re: Question on SQL query with COUNT, DECODE and DISTINCT [message #38735 is a reply to message #38726]

Thu, 09 May 2002 09:08

Jon
Messages: 483
Registered: May 2001

Senior Member

Heaven knows why you want to do this (count by ProcID within Procname but put out ProcName on the report), but I think this might get you what you want:

12:54:45 ==> select procname, sum(warnings), sum(errors) from
12:57:29 2 ( select procname, decode(status, 'W',1,0) warnings,
12:57:29 3 decode(status,'E',1,0) errors
12:57:29 4 from
12:57:29 5 (select distinct procname, status, ProcID from tabletest)
12:57:29 6 )
12:57:29 7 group by procname
12:57:29 8 ;

PROCNAME SUM(WARNINGS) SUM(ERRORS)
-------------------- ------------- -----------
Pname1 1 1

Report message to a moderator

Previous Topic:	Need info. about dbms_pipe
Next Topic:	Help with design

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 07 19:59:47 CDT 2024