Re: Unions in SQR

["Lu, Clint" <Clint.Lu@MDMNETWORK.COM>]

Richard,

Your unions select statements look fine except when you call procedures that
should not be at first column in select statements. Try to indent the call
procedure it might work.

begin-procedure RCVD_RPT1
begin-select
E.CUSTODIAN              &CUSTODIAN1               () ON-BREAK PRINT=NEVER
E.PERMBLDG               &PERMBLDG1                () ON-BREAK PRINT=NEVER
E.TAGNUM                    &TAGNUM1                    (+1,5,15)
E.MODELNUM               &MODELNUM1              (,15)
E.PARTNUM                  &PARTNUM1                 (,33)
E.SERIALNUM               &SERIALNUM1              (,45)
E.NOMENCLATURE      &DESCRIPTION1          (,55)
E.RECEIVEDDATE        &RECEIVEDDATE1       (,80) EDIT MM/DD/YY
E.PERMAREA                &PERMAREA1               (,90)

   do something
   do something-else

   FROM V_EQUIPMENT E
   WHERE E.LOCATION = $UIC
         AND E.CUSTODIAN LIKE [$TARGET]
         AND E.RECEIVEDDATE >= $START AND E.RECEIVEDDATE <= $STOP
    ORDER BY   E.LOCATION,
                  E.CUSTODIAN,
                  E.PERMBLDG,
                  E.TAGNUM

     UNION
    SELECT
E1.CUSTODIAN
E1.PERMBLDG
E1.TAGNUM
E1.MODELNUM
E1.PARTNUM
E1.SERIALNUM
E1.NOMENCLATURE
E1.RECEIVEDDATE
E1.PERMAREA
   FROM V_EQUIPMENT E1 , XACTION T
   WHERE T.TAGNUM = E1.TAGNUM
         AND E1.LOCATION = $UIC
         AND T.OLDVAL  LIKE  [$TARGET]
        AND T.CHGDATE >= $START AND T.CHGDATE <= $STOP
         AND T.XACTCODE  =  'CCM'
end-select
end-procedure

---------------------------------------------
Clint Lu
Caremark Rx, Inc.
Phone: 847-559-4717
Fax    : 847-559-4840




-----Original Message-----
From: Shaver, Richard H [mailto:richard.h.shaver@LMCO.COM]
Sent: Thursday, November 04, 1999 10:10 AM
To: Multiple recipients of list SQR-USERS
Subject: Re: Unions in SQR


OK, I don't get it.  I was just about to put a question on Unions out to the
group yesterday when I looked up and there was already a thread going on
about it.  I have tried several variations of what was advised, but no luck.

Both selects work fine by themselves.  But, when I try to use a Union the
following error is returned:
(SQR 5528) ODBC SQL dbexec: SQLExecute error 170 in cursor 1:
   [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near '.'.

I am running Visual Sqribe 4.3 ODBC against SQL Server 6.5

Can someone please spare some time and take a look?
Thanks
Rick
---------------------------------------------------


begin-procedure RCVD_RPT1
begin-select
E.CUSTODIAN              &CUSTODIAN1               () ON-BREAK PRINT=NEVER
do something
E.PERMBLDG               &PERMBLDG1                () ON-BREAK PRINT=NEVER
do something else
E.TAGNUM                    &TAGNUM1                    (+1,5,15)
E.MODELNUM               &MODELNUM1              (,15)
E.PARTNUM                  &PARTNUM1                 (,33)
E.SERIALNUM               &SERIALNUM1              (,45)
E.NOMENCLATURE      &DESCRIPTION1          (,55)
E.RECEIVEDDATE        &RECEIVEDDATE1       (,80) EDIT MM/DD/YY
E.PERMAREA                &PERMAREA1               (,90)
   FROM V_EQUIPMENT E
   WHERE E.LOCATION = $UIC
         AND E.CUSTODIAN LIKE [$TARGET]
         AND E.RECEIVEDDATE >= $START AND E.RECEIVEDDATE <= $STOP
    ORDER BY   E.LOCATION,
                  E.CUSTODIAN,
                  E.PERMBLDG,
                  E.TAGNUM

     UNION
    SELECT
E1.CUSTODIAN
E1.PERMBLDG
E1.TAGNUM
E1.MODELNUM
E1.PARTNUM
E1.SERIALNUM
E1.NOMENCLATURE
E1.RECEIVEDDATE
E1.PERMAREA
   FROM V_EQUIPMENT E1 , XACTION T
   WHERE T.TAGNUM = E1.TAGNUM
         AND E1.LOCATION = $UIC
         AND T.OLDVAL  LIKE  [$TARGET]
        AND T.CHGDATE >= $START AND T.CHGDATE <= $STOP
         AND T.XACTCODE  =  'CCM'
end-select
end-procedure



Rick Shaver
LM Data Systems
1-360.396.8488



                -----Original Message-----
                From:   Thorsten Lau [mailto:lau@EUROWINGS.DE]
                Sent:   Thursday, November 04, 1999 4:51 AM
                To:     Multiple recipients of list SQR-USERS
                Subject:        Re: Unions in SQR

                Manoj Gurjar schrieb:
                >
                > David,
                >
                > Only some more restrictions like this in SQR UNION Select
:
                >
                > begin-SELECT
                > col1   (+1, 5)
                > col2   (, 15)
                > do extra-processing
                > from a , b
                > union select col1, col2 from c,d
                > end-SELECT

                Hi,

                I want to mention, that these extra-processings are also
applied to all
                data that is selected by the second select, therefore using
the column
                names of the first select. The only problem I hit, is that
an ORDER BY
                statement didnīt work with column names , but only with
column numbers.

                Greetings,
                        Thorsten Lau


                --
                Thorsten Lau | mailto:lau@eurowings.de
                Eurowings AG | IT
                Telefon      | +49 231 9245 7624