FW: Using union in SQR

[Nancy Brzezinski <nbrzezinski@ASSISTED.COM>]

Also, get rid of the &Avail, &Selected and &Worked from the second select
statement.

> -----Original Message-----
> From: Pankaj Bedekar [SMTP:BedekarP@ALCONEMARKETING.COM]
> Sent: Tuesday, December 28, 1999 11:27 AM
> To:   SQR-USERS@list.iex.net
> 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
>