Re: Unions in SQR - THANKS

["Shaver, Richard H" <richard.h.shaver@LMCO.COM>]

Thanks to all of you who provided input.  I now have the report working
based on your advice.
Rick Shaver
LM Data Systems
1-360.396.8488



                -----Original Message-----
                From:   Manoj Gurjar [mailto:mgurjar@HOTMAIL.COM]
                Sent:   Thursday, November 04, 1999 8:56 AM
                To:     Multiple recipients of list SQR-USERS
                Subject:        Re: Unions in SQR

                Second select field list not required to be left justified.

                Also ORDER BY reqd after last select in UNION with Order
Numbers
                of the Columns selected instead of Column names.

                Manoj


                >From: "Love, Kristin" <klove@MSA.COM>
                >Reply-To: SQR-USERS@list.iex.net
                >To: Multiple recipients of list SQR-USERS
<SQR-USERS@list.iex.net>
                >Subject: Re: Unions in SQR
                >Date: Thu, 4 Nov 1999 11:28:58 -0500
                >
                >Put commas after the columns in your second SELECT
statement.
                >           UNION
                >             SELECT
                >         E1.CUSTODIAN,
                >         E1.PERMBLDG,
                >         etc.
                >         but not after the last one....
                >
                >         also, make sure everything is left justified
except for your SQR
                >commands.
                >
                >:) Kristin
                >
                > > -----Original Message-----
                > > From: Shaver, Richard H [SMTP: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

                ______________________________________________________
                Get Your Private, Free Email at http://www.hotmail.com