Re: Using union in SQR

["Bencke, Gina" <GBencke@KICHLER.COM>]

Judith,

When working with SQRs SELECT paragraph, keep in mind that SQR expects
the SQL between the 'FROM' clause and the End-SELECT to be syntactically
correct. Thus your UNION clause
must use commas between the select list items.  Also, SQR will create
column variables only for the select list items for your 'main' select,
use of &variables in the UNION should also cause an error.

Hope this helps.

Gina Bencke
Bencke Consulting Corporation
gina@bencke.com

-----Original Message-----
From: Judith Cruz-Lancaon [mailto: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')  &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