Re: Unions in SQR

["Love, Kristin" <klove@MSA.COM>]

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