Re: Unions in SQR

[Art DiRamio <ADiRamio@OPENSOLUTIONS.COM>]

Move the order by under the second select statement and order by numbers
(ie: Order by 1, 2, 3. . .)  where the 1,2,3 refer to the position of the
fields in the select statement.  In your example, E.custodian would be 1,
e.permbldg would be 2, etc. . .


  - - Art


                -----Original Message-----
                From:   Shaver, Richard H [mailto:richard.h.shaver@LMCO.COM]
                Sent:   Thursday, November 04, 1999 11: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