Re: Unions in SQR

[John Milardovic <milardj@SX.COM>]

First thing I see off the bat is that the columns in the second select must
be comma separated.

HTH
John Milardovic
> -----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