OraFAQ Forum: SQL & PL/SQL » transaction id sql

Home » SQL & PL/SQL » SQL & PL/SQL » transaction id sql

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

transaction id sql [message #656968]

Mon, 24 October 2016 13:34

suji6281
Messages: 134
Registered: September 2014

Senior Member

Hi,

Here i have attached the insert statements and Create Table sqls. Also provided the table data in attached csv file.

With the help of attached csv file,
Can you please help me with the sql that will fetch the req_id, business_unit and transactionid from PV_REQ_AW and PSWORKLIST tables respectively.
the criteria should pick the rows with inststatus as 1 in psworklist table and EOAWSTEP_STATUS as other than 'P' and 'N' in EOAW_STEPINST table.


CREATE TABLE PV_REQ_AW (EOAWTHREAD_ID DECIMAL(15) NOT NULL,
   EOAWPRCS_ID VARCHAR2(30) NOT NULL,
   EOAWDEFN_ID VARCHAR2(30) NOT NULL,
   ORIGINATORID VARCHAR2(30) NOT NULL,
   EOAWREQUESTOR_ID VARCHAR2(30) NOT NULL,
   RECNAME VARCHAR2(15) NOT NULL,
   EOAWTHREAD_STATUS VARCHAR2(1) NOT NULL,
   EOAWPARENT_THREAD DECIMAL(15) NOT NULL,
   EOAWDTTM_MODIFIED tIMESTAMP,
   BUSINESS_UNIT VARCHAR2(5) NOT NULL,
   REQ_ID VARCHAR2(10) NOT NULL,
   LINE_NBR INTEGER NOT NULL,
   COMMENTS CLOB);

CREATE TABLE EOAW_WL (BUSPROCNAME VARCHAR2(30) NOT NULL,
   ACTIVITYNAME VARCHAR2(30) NOT NULL,
   EVENTNAME VARCHAR2(30) NOT NULL,
   WORKLISTNAME VARCHAR2(30) NOT NULL,
   INSTANCEID INTEGER NOT NULL,
   TRANSACTIONID INTEGER NOT NULL,
   EOAWPRCS_ID VARCHAR2(30) NOT NULL,
   EOAWTHREAD_ID DECIMAL(15) NOT NULL,
   EOAWDEFN_ID VARCHAR2(30) NOT NULL,
   EFFDT DATE,
   EOAW_EXTERN_FLAG VARCHAR2(1) NOT NULL,
   EOAWLEVEL SMALLINT NOT NULL,
   EOAWDESCR VARCHAR2(254) NOT NULL,
   WORKLIST_DESCR VARCHAR2(50) NOT NULL);

CREATE TABLE EOAW_STEPINST (EOAWTHREAD_ID DECIMAL(15) NOT NULL,
   EOAWSTEP_INSTANCE DECIMAL(15) NOT NULL,
   EOAWPRCS_ID VARCHAR2(30) NOT NULL,
   EOAWDEFN_ID VARCHAR2(30) NOT NULL,
   EFFDT DATE NOT NULL,
   EOAWPATH_ID VARCHAR2(30) NOT NULL,
   EOAWSTAGE_NBR SMALLINT NOT NULL,
   EOAWSTEP_NBR DECIMAL(5, 2) NOT NULL,
   EOAWSTEP_TYPE VARCHAR2(1) NOT NULL,
   EOAWSTEP_STATUS VARCHAR2(1) NOT NULL,
   EOAWADHOC_BY VARCHAR2(30) NOT NULL,
   EOAW_EXTERN_FLAG VARCHAR2(1) NOT NULL);

CREATE TABLE EOAW_USERINST (EOAWUSTEP_INST_ID DECIMAL(15) NOT NULL,
   EOAWSTEP_INSTANCE DECIMAL(15) NOT NULL,
   EOAWUSER_TYPE VARCHAR2(30) NOT NULL,
   OPRID VARCHAR2(30) NOT NULL,
   EOAWORIG_OPRID VARCHAR2(30) NOT NULL,
   EOAWSTEP_STATUS VARCHAR2(1) NOT NULL,
   DTTM_CREATED tIMESTAMP
   EOAWDTTM_MODIFIED tIMESTAMP
   EOAWDTTM_COMPLETE tIMESTAMP);

CREATE TABLE PSWORKLIST (BUSPROCNAME VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   ACTIVITYNAME VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   EVENTNAME VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   WORKLISTNAME VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   INSTANCEID INTEGER  DEFAULT 999999999 NOT NULL,
   TRANSACTIONID INTEGER  DEFAULT 0 NOT NULL,
   ACTIONDTTM tIMESTAMP,
   OPRID VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   ORIGINATORTYPE SMALLINT  DEFAULT 0 NOT NULL,
   ORIGINATORID VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   INSTSTATUS SMALLINT  DEFAULT 0 NOT NULL,
   INSTAVAILABLEDTTM tIMESTAMP  NOT NULL,
   INSTSELECTEDDTTM tIMESTAMP,
   INSTWORKEDDTTM tIMESTAMP,
   INSTTIMEOUTDTTM tIMESTAMP,
   TIMEDOUT SMALLINT  DEFAULT 0 NOT NULL,
   PREVOPRID VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   COMMENTSHORT VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   WLDAYSTOSELECT DECIMAL(15, 7)  DEFAULT 0 NOT NULL,
   WLDAYSTOWORK DECIMAL(15, 7)  DEFAULT 0 NOT NULL,
   URL VARCHAR2(254)  DEFAULT ' ' NOT NULL,
   DO_REPLICATE_FLAG VARCHAR2(1)  DEFAULT ' ' NOT NULL,
   SYNCID INTEGER,
   LASTUPDDTTM tIMESTAMP,
   WL_PRIORITY VARCHAR2(1)  DEFAULT ' ' NOT NULL,
   DESCR254_MIXED VARCHAR2(254)  DEFAULT ' ' NOT NULL);

Insert into PV_REQ_AW  (EOAWTHREAD_ID,EOAWPRCS_ID,EOAWDEFN_ID,ORIGINATORID,EOAWREQUESTOR_ID,RECNAME,EOAWTHREAD_STATUS,EOAWPARENT_THREAD,EOAWDTTM_MODIFIED,BU SINESS_UNIT,REQ_ID,LINE_NBR,COMMENTS) values (26294681,'Requisition','WF_Requisition_Domestic','A958993','A958993','PV_REQHDR_AW_VW','S',26294681,to_timestamp('24-OCT-16 05.44.33.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'PS001','0003106069',0, EMPTY_CLOB());
Insert into PV_REQ_AW  (EOAWTHREAD_ID,EOAWPRCS_ID,EOAWDEFN_ID,ORIGINATORID,EOAWREQUESTOR_ID,RECNAME,EOAWTHREAD_STATUS,EOAWPARENT_THREAD,EOAWDTTM_MODIFIED,BU SINESS_UNIT,REQ_ID,LINE_NBR,COMMENTS) values (26294682,'Requisition','WF_Requisition_Domestic','A958993','A958993','PV_REQLIN_AW_VW','P',26294681,null,'PS001','0003106069',1, EMPTY_CLOB());
Insert into PV_REQ_AW  (EOAWTHREAD_ID,EOAWPRCS_ID,EOAWDEFN_ID,ORIGINATORID,EOAWREQUESTOR_ID,RECNAME,EOAWTHREAD_STATUS,EOAWPARENT_THREAD,EOAWDTTM_MODIFIED,BU SINESS_UNIT,REQ_ID,LINE_NBR,COMMENTS) values (26294683,'Requisition','WF_Requisition_Domestic','A958993','A958993','PV_REQLIN_AW_VW','A',26294681,null,'PS001','0003106069',2, EMPTY_CLOB());

Insert into EOAW_WL  (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,EOAWPRCS_ID,EOAWTHREAD_ID,EOAWDEFN_ID,EFFDT,EOAW_EXTERN_FLA G,EOAWLEVEL,EOAWDESCR,WORKLIST_DESCR) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098676,3098676,'Requisition',26294681,'WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'N',0,' BUSINESS_UNIT:PS001 REQ_ID:0003106069',' ');
Insert into EOAW_WL  (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,EOAWPRCS_ID,EOAWTHREAD_ID,EOAWDEFN_ID,EFFDT,EOAW_EXTERN_FLA G,EOAWLEVEL,EOAWDESCR,WORKLIST_DESCR) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098677,3098677,'Requisition',26294681,'WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'N',0,' BUSINESS_UNIT:PS001 REQ_ID:0003106069',' ');
Insert into EOAW_WL  (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,EOAWPRCS_ID,EOAWTHREAD_ID,EOAWDEFN_ID,EFFDT,EOAW_EXTERN_FLA G,EOAWLEVEL,EOAWDESCR,WORKLIST_DESCR) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098678,3098678,'Requisition',26294681,'WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'N',0,' BUSINESS_UNIT:PS001 REQ_ID:0003106069',' ');

Insert into EOAW_STEPINST  (EOAWTHREAD_ID,EOAWSTEP_INSTANCE,EOAWPRCS_ID,EOAWDEFN_ID,EFFDT,EOAWPATH_ID,EOAWSTAGE_NBR,EOAWSTEP_NBR,EOAWSTEP_TYPE,EOAWSTEP_STATUS,E OAWADHOC_BY,EOAW_EXTERN_FLAG) values (26294682,26822843,'Requisition','WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'1',10,2,'N','A',' ','N');
Insert into EOAW_STEPINST  (EOAWTHREAD_ID,EOAWSTEP_INSTANCE,EOAWPRCS_ID,EOAWDEFN_ID,EFFDT,EOAWPATH_ID,EOAWSTAGE_NBR,EOAWSTEP_NBR,EOAWSTEP_TYPE,EOAWSTEP_STATUS,E OAWADHOC_BY,EOAW_EXTERN_FLAG) values (26294682,26822844,'Requisition','WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'1',10,3,'N','P',' ','N');
Insert into EOAW_STEPINST  (EOAWTHREAD_ID,EOAWSTEP_INSTANCE,EOAWPRCS_ID,EOAWDEFN_ID,EFFDT,EOAWPATH_ID,EOAWSTAGE_NBR,EOAWSTEP_NBR,EOAWSTEP_TYPE,EOAWSTEP_STATUS,E OAWADHOC_BY,EOAW_EXTERN_FLAG) values (26294682,26822845,'Requisition','WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'1',10,4,'N','N',' ','N');
Insert into EOAW_STEPINST  (EOAWTHREAD_ID,EOAWSTEP_INSTANCE,EOAWPRCS_ID,EOAWDEFN_ID,EFFDT,EOAWPATH_ID,EOAWSTAGE_NBR,EOAWSTEP_NBR,EOAWSTEP_TYPE,EOAWSTEP_STATUS,E OAWADHOC_BY,EOAW_EXTERN_FLAG) values (26294683,26822846,'Requisition','WF_Requisition_Domestic',to_date('22-NOV-14','DD-MON-RR'),'1',10,2,'N','A',' ','N');

Insert into EOAW_USERINST  (EOAWUSTEP_INST_ID,EOAWSTEP_INSTANCE,EOAWUSER_TYPE,OPRID,EOAWORIG_OPRID,EOAWSTEP_STATUS,DTTM_CREATED,EOAWDTTM_MODIFIED,EOAWDTTM_COMPLETE) values (26026490,26822843,'A','TEVIKMD','TEVIKMD','A',to_timestamp('24-OCT-16 04.52.26.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('24-OCT-16 05.44.33.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null);
Insert into EOAW_USERINST  (EOAWUSTEP_INST_ID,EOAWSTEP_INSTANCE,EOAWUSER_TYPE,OPRID,EOAWORIG_OPRID,EOAWSTEP_STATUS,DTTM_CREATED,EOAWDTTM_MODIFIED,EOAWDTTM_COMPLETE) values (26026492,26822844,'A','A173236','A173236','P',to_timestamp('24-OCT-16 05.44.33.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('24-OCT-16 05.44.33.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null);
Insert into EOAW_USERINST  (EOAWUSTEP_INST_ID,EOAWSTEP_INSTANCE,EOAWUSER_TYPE,OPRID,EOAWORIG_OPRID,EOAWSTEP_STATUS,DTTM_CREATED,EOAWDTTM_MODIFIED,EOAWDTTM_COMPLETE) values (26026491,26822846,'A','TEVIKMD','TEVIKMD','A',to_timestamp('24-OCT-16 04.52.27.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('24-OCT-16 05.44.33.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null);

Insert into PSWORKLIST  (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,ACTIONDTTM,OPRID,ORIGINATORTYPE,ORIGINATORID,INSTSTATUS,INS TAVAILABLEDTTM,INSTSELECTEDDTTM,INSTWORKEDDTTM,INSTTIMEOUTDTTM,TIMEDOUT,PREVOPRID,COMMENTSHORT,WLDAYSTOSELECT,WLDAYSTOWORK,URL,DO_REP LICATE_FLAG,SYNCID,LASTUPDDTTM,WL_PRIORITY,DESCR254_MIXED) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098676,3098676,null,'TEVIKMD',0,'A958993',2,to_timestamp('24-OCT-16 04.52.26.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('24-OCT-16 05.36.05.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('24-OCT-16 05.44.33.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null,0,' ',' ',0.0303125,0,' ','Y',159252999,to_timestamp('24-OCT-16 05.44.33.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'2',' ');
Insert into PSWORKLIST  (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,ACTIONDTTM,OPRID,ORIGINATORTYPE,ORIGINATORID,INSTSTATUS,INS TAVAILABLEDTTM,INSTSELECTEDDTTM,INSTWORKEDDTTM,INSTTIMEOUTDTTM,TIMEDOUT,PREVOPRID,COMMENTSHORT,WLDAYSTOSELECT,WLDAYSTOWORK,URL,DO_REP LICATE_FLAG,SYNCID,LASTUPDDTTM,WL_PRIORITY,DESCR254_MIXED) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098677,3098677,null,' ',0,'A958993',0,to_timestamp('24-OCT-16 04.52.27.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null,null,null,0,' ',' ',0,0,' ',' ',159253000,to_timestamp('24-OCT-16 04.52.27.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'2',' ');
Insert into PSWORKLIST  (BUSPROCNAME,ACTIVITYNAME,EVENTNAME,WORKLISTNAME,INSTANCEID,TRANSACTIONID,ACTIONDTTM,OPRID,ORIGINATORTYPE,ORIGINATORID,INSTSTATUS,INS TAVAILABLEDTTM,INSTSELECTEDDTTM,INSTWORKEDDTTM,INSTTIMEOUTDTTM,TIMEDOUT,PREVOPRID,COMMENTSHORT,WLDAYSTOSELECT,WLDAYSTOWORK,URL,DO_REP LICATE_FLAG,SYNCID,LASTUPDDTTM,WL_PRIORITY,DESCR254_MIXED) values ('EOAW_APPROVALS','EOAW_ROUTE','Route','Approval Routing',3098678,3098678,null,'A173236',0,'TEVIKMD',1,to_timestamp('24-OCT-16 05.44.33.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('24-OCT-16 06.02.07.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),null,null,0,' ',' ',0.0121991,0,' ','Y',159253001,to_timestamp('24-OCT-16 06.02.07.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),'2',' ');

The output value should be as below:

Req_id business_unit transactionid
3106069 PS001 3098676

Attachment: Data_worklist.csv
(Size: 3.76KB, Downloaded 1456 times)

Report message to a moderator

Re: transaction id sql [message #656969 is a reply to message #656968]

Mon, 24 October 2016 13:47

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

What SQL have you tried so far? What problem have you encountered?

Report message to a moderator

Re: transaction id sql [message #656971 is a reply to message #656969]

Mon, 24 October 2016 14:08

suji6281
Messages: 134
Registered: September 2014

Senior Member

Hi John,

I have tried with below sql but it is not returning any rows.

select c.Req_id, c.business_unit, w.transactionid
from EOAW_USERINST u, EOAW_STEPINST s, EOAW_WL e, PV_REQ_AW c
where u.EOAWSTEP_INSTANCE = s.EOAWSTEP_INSTANCE
and e.EOAWTHREAD_ID = s.EOAWTHREAD_ID
and e.TRANSACTIONID = w.TRANSACTIONID
and w.INSTSTATUS = 1
and s.EOAWSTEP_STATUS <> 'P'
and c.EOAWTHREAD_ID = s.EOAWTHREAD_ID;

I was facing problem how to join EOAW_STEPINST, EOAW_WL, PV_REQ_AW tables.
since we have EOAWTHREAD_ID as 26294681 in EOAW_WL. But I need to verify all rows of EOAW_STEPINST table by using PV_REQ_AW as reference table to join. for one EOAWTHREAD_ID value there will be multiple rows with same EOAWTHREAD_ID in EOAW_STEPINST table. I am not sure how to verify all the rows in EOAW_STEPINST table with one EOAWTHREAD_ID from PV_REQ_AW table. Pease help me with solution. Thank You.

Report message to a moderator

Re: transaction id sql [message #656992 is a reply to message #656971]

Tue, 25 October 2016 04:43

suji6281
Messages: 134
Registered: September 2014

Senior Member

Hi John,

tried with below sql as well and its returning the row. Can you please help me with query.
The criteria as below:
the query should not pick the value for inststatus as 1 in psworklist table and its corresponding row eoawstep_status as 'P' in either of eoaw_stepinst or eoaw_userinst tables.

I was trying to fetch the transaction id from psworklist for inststatus as 1 and joining it with table eoaw_wl. After that verifying the eoawthread_id with ps_req_aw table and eoaw_stepinst for eoaw_status not equal to 'P'.
Please help me here how to pass eoawthread_id to pv_req_aw and how to put the eoaw_status of eoaw_stepinst table in query.

Requesting you please assist me urgently.

select e.* from psworklist wl, ps_eoaw_wl e
where wl.busprocname = 'EOAW_APPROVALS' and wl.activityname = 'EOAW_ROUTE' and wl.inststatus = 1
and wl.busprocname	= e.busprocname and 
wl.activityname	= e.activityname and 
wl.eventname	= e.eventname and 
wl.worklistname	= e.worklistname and 
wl.instanceid =	e.instanceid and 
wl.transactionid	= e.transactionid and
e.EOAWDESCR like '%0003106069%' and 
exists  (select 'y' from ps_pv_req_aw p, ps_eoaw_stepinst s where  
e.eoawthread_id = p.eoawparent_thread and s.eoawstep_status <> 'P' and
p.eoawthread_id = s.eoawthread_id );

Thanks
sujikar

Report message to a moderator

Re: transaction id sql [message #657004 is a reply to message #656971]

Tue, 25 October 2016 23:54

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

suji6281 wrote on Mon, 24 October 2016 12:08

Hi John,

I have tried with below sql but it is not returning any rows.

select c.Req_id, c.business_unit, w.transactionid
from EOAW_USERINST u, EOAW_STEPINST s, EOAW_WL e, PV_REQ_AW c
where u.EOAWSTEP_INSTANCE = s.EOAWSTEP_INSTANCE
and e.EOAWTHREAD_ID = s.EOAWTHREAD_ID
and e.TRANSACTIONID = w.TRANSACTIONID
and w.INSTSTATUS = 1
and s.EOAWSTEP_STATUS <> 'P'
and c.EOAWTHREAD_ID = s.EOAWTHREAD_ID;

I was facing problem how to join EOAW_STEPINST, EOAW_WL, PV_REQ_AW tables.
since we have EOAWTHREAD_ID as 26294681 in EOAW_WL. But I need to verify all rows of EOAW_STEPINST table by using PV_REQ_AW as reference table to join. for one EOAWTHREAD_ID value there will be multiple rows with same EOAWTHREAD_ID in EOAW_STEPINST table. I am not sure how to verify all the rows in EOAW_STEPINST table with one EOAWTHREAD_ID from PV_REQ_AW table. Pease help me with solution. Thank You.

That query doesn't return no rows as it raises an error due to not having any table with an alias of w.

Even if you fix the query, it appears that there are no rows in your sample data that match your conditions.

Report message to a moderator

Previous Topic:	Referencing Collection elements
Next Topic:	NULL VS ''

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 17:25:37 CDT 2024