Re: SQR and UNIONs

["John Munkberg (Connexus)" <Johnm@CONNEXUSENERGY.COM>]

This works for Oracle, I assume it will work for you too.

BEGIN-SELECT
emplid (+1,1)
purchase type (,10)
amount (,30)
   DO PROCEDURE
   MOVE &emlid to $EMPID
   ADD &amount to #TOTAL
 FROM  some tables
 WHERE  xxx
 GROUP BY emplid purchase type
 UNION
 SELECT emplid, purchase type, amount
 FROM  some other tables
 WHERE xxx
 GROUP BY emplid purchase type
 UNION
 SELECT emplid, purchase type, amount
 FROM same tables as second select
 WHERE  different criteria
 GROUP BY emplid purchase type
END-SELECT


> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ~~~~~~~~~~~
> |   John E. Munkberg    |   CONNEXUS  ENERGY   |
> JohnM@CONNEXUSENERGY.com     |
> |     612.323.2782      |      Ramsey, MN     |
> http://WWW.CONNEXUSENERGY.com  |
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ~~~~~~~~~~~
> Richard,
> I am trying to write an SQR using UNIONs.  I am unsure as to how the
> results will be processed.
> Hopefully the following can explain it clearer:
>
> SELECT emplid, purchase type, amount
> FROM  some tables
> WHERE  xxx
> GROUP BY emplid purchase type
> UNION
> SELECT emplid, purchase type, amount
> FROM  some other tables
> WHERE xxx
> GROUP BY emplid purchase type
> UNION
> SELECT emplid, purchase type, amount
> FROM same tables as second select
> WHERE  different criteria
> GROUP BY emplid purchase type
>
> What I expect back is a maximum of three rows (one from each select)
> for
> each employee.  I need to ensure that the employee has a paysheet,
> then
> update the general deduction table for each result.
> Where should I place my LETs and DOs?  I only want to check the
> paysheet
> once per employee.  If I put the LETs/DOs inside a SELECT, will it
> only
> process the results of that select?  Do I have to put the same
> LETs/DOs
> within each SELECT?  If so, then I will do the employee / paysheet
> check up
> to 3 times per employee.
>
> Thanks
> Rhonda
>  << File: PIC02511.PCX >>  << File: PIC22021.PCX >>