Re: Using union in SQR

[Judith Cruz-Lancaon <judith.cruz-lancaon@NYTSSC.COM>]

Thank you so much Kristin.  It worked...   I'm not even going to tell you how
long I've been trying to figure out what's wrong with this SQL statement.

Thanks again,
Judith





"Love, Kristin" <KLove@MSA.COM> on 12/28/99 12:45:36 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








Actually, don't use any alias names in the second part of the union.
Also, put commas after your columns in the second part of the union.
HTH
:) Kristin

> -----Original Message-----
> From: Judith Cruz-Lancaon [SMTP:judith.cruz-lancaon@NYTSSC.COM]
> Sent: Tuesday, December 28, 1999 12:16 PM
> 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')
         , 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 = &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