Home » SQL & PL/SQL » SQL & PL/SQL » transaction id sql
transaction id sql [message #656968] |
Mon, 24 October 2016 13:34 |
|
suji6281
Messages: 135 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
|
|
|
|
|
|
Re: transaction id sql [message #657004 is a reply to message #656971] |
Tue, 25 October 2016 23:54 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
suji6281 wrote on Mon, 24 October 2016 12:08Hi 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.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:46:34 CDT 2024
|