Re: Using union in SQR

[Kelly Raymer <raymer@TCONL.COM>]

I have had that same problem and Kristin's solution worked for me.

Have a good day!
Kelly : )

----- Original Message -----
From: Love, Kristin <KLove@MSA.COM>
To: <SQR-USERS@list.iex.net>
Sent: Tuesday, December 28, 1999 11:45 AM
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