Home » SQL & PL/SQL » SQL & PL/SQL » Query Not giving Proper Output and How to write Query In effective way
Query Not giving Proper Output and How to write Query In effective way [message #677659] Thu, 03 October 2019 14:38 Go to next message
chavva.kiru@gmail.com
Messages: 23
Registered: April 2012
Location: hyderabad
Junior Member
Hi,

Create table Matrix_Request
(
ID NUMBER(10,0),
MATRIX_REQUEST_ID VARCHAR2(255 CHAR) NOT NULL ENABLE,
"MATRIX_CLINICIAN_OWNER" VARCHAR2(255 CHAR),
"MATRIX_GROUP" VARCHAR2(255 CHAR),
"MATRIX_TYPE" VARCHAR2(255 CHAR)
)

Create table Matrix_ProcedureDetails
(
ID Number(10,0),
MATRIX_SETTING VARCHAR2(255 CHAR),
"REQUEST_PK" NUMBER(10,0)
)

Create table Matrix_Event_LOG
(
Request_PK NUMBER(10,0),
Procedure_details_PK NUMBER(10,0),
status VARCHAR2(255 CHAR),
EVENT_TIMESTAMP TIMESTAMP (6),
CUSTOM_6 VARCHAR2(256 BYTE),
EVENT_CODE VARCHAR2(32 CHAR)
)

INSERT INTO MATRIX_REQUEST VALUES
(1652313,'U19077AAAD','msClinician','ERS2','Group / ECCO');

INSERT INTO Matrix_ProcedureDetails VALUES(14567,'HCSCTS01',1652313);
INSERT INTO Matrix_ProcedureDetails VALUES(12897,'HCSCTS01',1652313);
INSERT INTO Matrix_ProcedureDetails VALUES(12887,'HCSCTS01',1652313);
INSERT INTO Matrix_ProcedureDetails VALUES(12893,'HCSCTS01',1652313);
INSERT INTO Matrix_ProcedureDetails VALUES(12896,'HCSCTS01',1652313);


INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12893,'Pend','18-MAR-19 08.46.23.110000000 AM',NULL,'ASSI_CLI_ADD');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12897,'Pend','18-MAR-19 08.50.31.230000000 AM',NULL,'ASSI_CLI_ADD');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','03-JUL-19 02.47.38.680000000 PM',NULL,'ASSI_REQ_FOR_EDI');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','03-JUL-19 05.58.24.510000000 PM',NULL,'ASSI_REQ_FOR_EDI');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','03-JUL-19 06.00.17.170000000 PM',NULL,'ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12896,'Pend','18-MAR-19 08.47.46.340000000 AM',NULL,'ASSI_CLI_ADD');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','18-MAR-19 08.44.20.590000000 AM',NULL,'ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','18-MAR-19 08.44.20.590000000 AM',NULL,'ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','18-MAR-19 08.39.12.160000000 AM',NULL,'ASSI_NEW_REQ_INT');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.11.480000000 PM','msClinician','REV');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.24.530000000 PM','msClinician','ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.51.480000000 PM','msClinician','DISPSTN_RQST');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.54.360000000 PM','msClinician','ASSI_CLO_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.54.360000000 PM','msClinician','ASSI_REQ');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 06.04.41.550000000 PM',NULL,'MEMBER_OUTCOME');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','16-SEP-19 05.28.30.570000000 PM',NULL,'MEMBER_OUTCOME');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Pend','16-SEP-19 05.36.48.120000000 PM',NULL,'MEMBER_OUTCOME');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.40.02.430000000 PM','msClinician','REV_LET_GEN');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.23.910000000 PM','msClinician','DISPSTN_RQST');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,12887,'Approve','16-SEP-19 05.41.33.910000000 PM','msClinician','DISPSTN_RQST');
INSERT INTO MATRIX_EVENT_LOG(Request_PK,Procedure_details_PK,status,EVENT_TIMESTAMP,CUSTOM_6,EVENT_CODE )
Values(1652313,14567,'Pend','16-SEP-19 05.41.23.910000000 PM',NULL,'MEMBER_OUTCOME');

Query:1

select Matrix_owner,Matrix_setting,Matrix_group,Matrix_type,CLOSED_REQUEST_COUNT from(
select Matrix_owner,Matrix_setting,Matrix_group,Matrix_type,CLOSED_REQUEST_COUNT,count(*) from
(Select Request.MATRIX_CLINICIAN_OWNER as Matrix_OWNER,
PROC.MATRIX_SETTING as Matrix_Setting,
Request.Matrix_GROUP As Matrix_GROUP,
Request.Matrix_TYPE as Matrix_TYPE,
PROC.ID,
(select count(*) from Matrix_EVENT_LOG Event1,Matrix_EVENT_LOG Event2 where
Request.id=Event1.Request_PK
and Request.id=Proc.REQUEST_PK
-- and PROC."ID"= Event1.PROCEDURE_DETAILS_PK
--AND Event.APPIAN_REQUEST_ID = 'U19077AAAD'
AND Event1.status <> 'Pend'
And Trunc(Event1."EVENT_TIMESTAMP")=trunc(Sysdate)-17
AND Event1.CUSTOM_6 = Request.MATRIX_CLINICIAN_OWNER
AND EVENT1.EVENT_CODE IN ('DISPSTN_RQST')
and Event1.Request_PK=Event2.Request_PK
and Event1.Procedure_details_PK=Event2.Procedure_details_PK
-- and Event1.Event_Timestamp>Event2.Event_Timestamp
AND Event2.status <> 'Pend'
And Trunc(Event2."EVENT_TIMESTAMP")=trunc(Sysdate)-17
AND Event2."CUSTOM_6" = Request."MATRIX_CLINICIAN_OWNER"
AND EVENT2."EVENT_CODE" IN ('DISPSTN_RQST')
) CLOSED_REQUEST_COUNT From Matrix_Request Request,
"Matrix_ProcedureDetails" PROC,
Matrix_Event_LOG Event
Where Request."MATRIX_REQUEST_ID" = 'U19077AAAD'
and Request.id=Event.Request_PK
and Request.id=Proc.REQUEST_PK
and PROC."ID"= Event.PROCEDURE_DETAILS_PK
)
group by Matrix_owner,Matrix_setting,Matrix_group,Matrix_type,CLOSED_REQUEST_COUNT)



Query:2
select * from MATRIX_EVENT_LOG Event where REQUEST_PK=1652313
AND Event.status <> 'Pend'
And Trunc(Event."EVENT_TIMESTAMP")=trunc(Sysdate)-17
-- AND Event.CUSTOM_6 = Request.MATRIX_CLINICIAN_OWNER
AND EVENT.EVENT_CODE IN ('DISPSTN_RQST')

My Requirement is I have to get Latest 1 record as closed_Request_count from Query1
Which satisfies data from Query2 as sample records with latest_timestamp as 16-SEP-19 05.41.51.480000000 PM for Request_pk=1652313

But Iam getting Output as 9.How to write Query Effectively and neately.Requesting your Immediate help

I have to get o/p in such a way that MATRIX_EVENT_LOG
for Event_code,Request_PK,Procedure_details_PK combination I have to get the latest record as CLOSED_REQUEST_COUNT

[Updated on: Thu, 03 October 2019 14:49]

Report message to a moderator

Re: Query Not giving Proper Output and How to write Query In effective way [message #677660 is a reply to message #677659] Thu, 03 October 2019 14:51 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Maybe it's a language/cultural thing, but to native English speakers (and this is an English language forum) the term "immediate" means "drop everything you are doing and attend to this right now". That's now how these forums work. We're just all peer volunteers with jobs of our own.

That said I don't understand your description of either the desired or actual output.
Re: Query Not giving Proper Output and How to write Query In effective way [message #677703 is a reply to message #677659] Tue, 08 October 2019 04:06 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hi again,

I wonder why you did not follow your previous threads with seemingly the same question:
http://www.orafaq.com/forum/t/206102/
http://www.orafaq.com/forum/t/206123/
Well, never mind, if you replied in any of these threads, I could answer a few days sooner.

Looking at provided queries, maybe you should stop using tools like this: https://lingojam.com/sPoNgEbobMoCkINgtexTgenEratoR for code editing and concisely use lowercase where possible (=everywhere except data literals).
Moreover, the code is not formatted. Could you kindly follow instruction in the post How to use [code] tags and make your code easier to read as you were asked to do so multiple times?
Without these adjustments, I can only spot that first query will fail with ORA-00942: "table or view does not exist" as "Matrix_ProcedureDetails" is not valid table name.

Instead of posting queries returning doubtful results commented with confused incomplete sentences, maybe you should start from the very beginning.
So far you provided some sample data. It does not seem to be very representative, but let us assume it suffices for now.
You also included two queries returning intermediate(?) results. However, based on this thread title, it is not clear whether they return correct results.

Still your post is missing the most important part at all:
What is the expected output of the required query from this data?
How does it look like?
Which exact values does it contain?
How many columns shall it have? Are its values directly taken from input table rows or is some transformation needed? If so, which one?
How many rows shall it have? How are they related to input table rows? Is its relationship 1:1 (one table row displayed as one row in query result)?
Are you able to tell required output from data provided in posted tables?
If so, why did you not post it yet? If you will, please, do not to forget to format it like described here: http://www.orafaq.com/forum/t/205234/
If not, how could you judge correctness of any query returning anything?

I am starting to be quite tired of this continuous guessing game. Maybe others will have more patience. Good luck.
Previous Topic: Aggregate operation on array - get totals
Next Topic: Factorial Function Confusing.
Goto Forum:
  


Current Time: Thu Mar 28 11:17:17 CDT 2024