Sorry, I should've mentioned that I've already tried that, and it didn't work.
Thanks, Judith
Pankaj Bedekar <BedekarP@ALCONEMARKETING.COM> on 12/28/99 12:26:31 PM
Please respond to sqr-users@list.iex.net
To: SQR-USERS@list.iex.net
cc: (bcc: Judith Cruz-Lancaon/CORPHQ/NYTIMES)
Subject: Re: Using union in SQR
use diffreent alias names in second query .... lke WL1, W1 in place of WL,
W
-----Original Message-----
From: Judith Cruz-Lancaon [mailto:judith.cruz-lancaon@NYTSSC.COM]
Sent: Tuesday, December 28, 1999 9:16 AM
To: SQR-USERS@list.iex.net
Subject: Using union in SQR
The following returns an error:
Begin-SELECT
W.INSTANCEID
W.TRANSACTIONID
WL.ORIGINATORID
WL.OPRID
WL.PREVOPRID
W.APPR_INSTANCE
to_char(WL.INSTAVAILABLEDTTM,'MM/DD/YY HH:MI:SSAM') &Avail
to_char(WL.INSTSELECTEDDTTM,'MM/DD/YY HH:MI:SSAM') &Selected
to_char(WL.INSTWORKEDDTTM,'MM/DD/YY HH:MI:SSAM') &Worked
do Print-REQ-WL
FROM PSWORKLIST WL,
SYSADM.PS_REQ_APPROVAL_WL W
WHERE W.BUSINESS_UNIT = &A.BUSINESS_UNIT
AND W.REQ_ID = &A.REQ_ID
AND W.INSTANCEID = WL.INSTANCEID
AND W.TRANSACTIONID = WL.TRANSACTIONID
AND W.BUSPROCNAME = WL.BUSPROCNAME
AND W.ACTIVITYNAME = WL.ACTIVITYNAME
AND W.EVENTNAME = WL.EVENTNAME
AND W.WORKLISTNAME = WL.WORKLISTNAME
UNION
SELECT
W.INSTANCEID
W.TRANSACTIONID
WL.ORIGINATORID
WL.OPRID
WL.PREVOPRID
W.APPR_INSTANCE
to_char(WL.INSTAVAILABLEDTTM,'MM/DD/YY HH:MI:SSAM') &Avail
to_char(WL.INSTSELECTEDDTTM,'MM/DD/YY HH:MI:SSAM') &Selected
to_char(WL.INSTWORKEDDTTM,'MM/DD/YY HH:MI:SSAM') &Worked
FROM PSWORKLIST WL,
SYSADM.PS_NYT_REQ_PRJ_WL W
WHERE W.BUSINESS_UNIT = &A.BUSINESS_UNIT
AND W.REQ_ID = &A.REQ_ID
AND W.INSTANCEID = WL.INSTANCEID
AND W.TRANSACTIONID = WL.TRANSACTIONID
AND W.BUSPROCNAME = WL.BUSPROCNAME
AND W.ACTIVITYNAME = WL.ACTIVITYNAME
AND W.EVENTNAME = WL.EVENTNAME
AND W.WORKLISTNAME = WL.WORKLISTNAME
end-SELECT
The error is:
(SQR 5528) ORACLE OPARSE error -923 in cursor 3:
ORA-00923: FROM keyword not found where expected
SQL: select W.INSTANCEID, W.TRANSACTIONID, WL.ORIGINATORID, WL.OPRID,
WL.PREVOPRID, W.APPR_INSTANCE, to_char(WL.INSTAVAILABLEDTTM,'MM/DD/YY
HH:MI:SSAM'), to_char(WL.INSTSELECTEDDTTM,'MM/DD/YY HH:MI:SSAM'),
to_char(WL.INSTWORKEDDTTM,'MM/DD/YY HH:MI:SSAM') FROM PSWORKLIST
WL,
SYSADM.PS_REQ_APPROVAL_WL W WHERE W.BUSINESS_UNIT = :1 AND
W.REQ_ID
= :2 AND W.INSTANCEID = WL.INSTANCEID AND W.TRANSACTIONID =
WL.TRANSACTIONID AND W.BUSPROCNAME = WL.BUSPROCNAME AND
W.ACTIVITYNAME = WL.ACTIVITYNAME AND W.EVENTNAME = WL.EVENTNAME AND
W.WORKLISTNAME = WL.WORKLISTNAME UNION SELECT W.INSTANCEID
W.TRANSACTIONID WL.ORIGINATORID WL.OPRID WL.PREVOPRID W.APPR_INSTANCE
to_char(WL.INSTAVAILABLEDTTM,'MM/DD/YY HH:MI:SSAM')
to_char(WL.INSTSELECTEDDTTM,'MM/DD/YY HH:MI:SSAM')
to_char(WL.INSTWORKEDDTTM,'MM/DD/YY HH:MI:SSAM') FROM PSWORKLIST WL,
SYSADM.PS_NYT_REQ_PRJ_WL W WHERE W.BUSINESS_UNIT = :1 AND W.REQ_ID
=
:2 AND W.INSTANCEID = WL.INSTANCEID AND W.TRANSACTIONID =
WL.TRANSACTIONID AND W.BUSPROCNAME = WL.BUSPROCNAME AND
W.ACTIVITYNAME = WL.ACTIVITYNAME AND W.EVENTNAME = WL.EVENTNAME AND
W.WORKLISTNAME = WL.WORKLISTNAME
Error at: .TRANSACTIONID
Error on line 342:
(SQR 3716) Error in SQL statement.
Can anyone tell me what's wrong with it? Please help...
Judith
Title: RE: Using union in SQR
use diffreent alias names in second query .... lke WL1, W1 in place of WL, W
-----Original Message-----
From: Judith Cruz-Lancaon [mailto:judith.cruz-lancaon@NYTSSC.COM]
Sent: Tuesday, December 28, 1999 9:16 AM
To: SQR-USERS@list.iex.net
Subject: Using union in SQR
The following returns an error:
Begin-SELECT
W.INSTANCEID
W.TRANSACTIONID
WL.ORIGINATORID
WL.OPRID
WL.PREVOPRID
W.APPR_INSTANCE
to_char(WL.INSTAVAILABLEDTTM,'MM/DD/YY HH:MI:SSAM') &Avail
to_char(WL.INSTSELECTEDDTTM,'MM/DD/YY HH:MI:SSAM') &Selected
to_char(WL.INSTWORKEDDTTM,'MM/DD/YY HH:MI:SSAM') &Worked
do Print-REQ-WL
FROM PSWORKLIST WL,
SYSADM.PS_REQ_APPROVAL_WL W
WHERE W.BUSINESS_UNIT = &A.BUSINESS_UNIT
AND W.REQ_ID = &A.REQ_ID
AND W.INSTANCEID = WL.INSTANCEID
AND W.TRANSACTIONID = WL.TRANSACTIONID
AND W.BUSPROCNAME = WL.BUSPROCNAME
AND W.ACTIVITYNAME = WL.ACTIVITYNAME
AND W.EVENTNAME = WL.EVENTNAME
AND W.WORKLISTNAME = WL.WORKLISTNAME
UNION
SELECT
W.INSTANCEID
W.TRANSACTIONID
WL.ORIGINATORID
WL.OPRID
WL.PREVOPRID
W.APPR_INSTANCE
to_char(WL.INSTAVAILABLEDTTM,'MM/DD/YY HH:MI:SSAM') &Avail
to_char(WL.INSTSELECTEDDTTM,'MM/DD/YY HH:MI:SSAM') &Selected
to_char(WL.INSTWORKEDDTTM,'MM/DD/YY HH:MI:SSAM') &Worked
FROM PSWORKLIST WL,
SYSADM.PS_NYT_REQ_PRJ_WL W
WHERE W.BUSINESS_UNIT = &A.BUSINESS_UNIT
AND W.REQ_ID = &A.REQ_ID
AND W.INSTANCEID = WL.INSTANCEID
AND W.TRANSACTIONID = WL.TRANSACTIONID
AND W.BUSPROCNAME = WL.BUSPROCNAME
AND W.ACTIVITYNAME = WL.ACTIVITYNAME
AND W.EVENTNAME = WL.EVENTNAME
AND W.WORKLISTNAME = WL.WORKLISTNAME
end-SELECT
The error is:
(SQR 5528) ORACLE OPARSE error -923 in cursor 3:
ORA-00923: FROM keyword not found where expected
SQL: select W.INSTANCEID, W.TRANSACTIONID, WL.ORIGINATORID, WL.OPRID,
WL.PREVOPRID, W.APPR_INSTANCE, to_char(WL.INSTAVAILABLEDTTM,'MM/DD/YY
HH:MI:SSAM'), to_char(WL.INSTSELECTEDDTTM,'MM/DD/YY HH:MI:SSAM'),
to_char(WL.INSTWORKEDDTTM,'MM/DD/YY HH:MI:SSAM') FROM PSWORKLIST WL,
SYSADM.PS_REQ_APPROVAL_WL W WHERE W.BUSINESS_UNIT = :1 AND W.REQ_ID
= :2 AND W.INSTANCEID = WL.INSTANCEID AND W.TRANSACTIONID =
WL.TRANSACTIONID AND W.BUSPROCNAME = WL.BUSPROCNAME AND
W.ACTIVITYNAME = WL.ACTIVITYNAME AND W.EVENTNAME = WL.EVENTNAME AND
W.WORKLISTNAME = WL.WORKLISTNAME UNION SELECT W.INSTANCEID
W.TRANSACTIONID WL.ORIGINATORID WL.OPRID WL.PREVOPRID W.APPR_INSTANCE
to_char(WL.INSTAVAILABLEDTTM,'MM/DD/YY HH:MI:SSAM')
to_char(WL.INSTSELECTEDDTTM,'MM/DD/YY HH:MI:SSAM')
to_char(WL.INSTWORKEDDTTM,'MM/DD/YY HH:MI:SSAM') FROM PSWORKLIST WL,
SYSADM.PS_NYT_REQ_PRJ_WL W WHERE W.BUSINESS_UNIT = :1 AND W.REQ_ID =
:2 AND W.INSTANCEID = WL.INSTANCEID AND W.TRANSACTIONID =
WL.TRANSACTIONID AND W.BUSPROCNAME = WL.BUSPROCNAME AND
W.ACTIVITYNAME = WL.ACTIVITYNAME AND W.EVENTNAME = WL.EVENTNAME AND
W.WORKLISTNAME = WL.WORKLISTNAME
Error at: .TRANSACTIONID
Error on line 342:
(SQR 3716) Error in SQL statement.
Can anyone tell me what's wrong with it? Please help...
Judith