Re: SQR and UNIONs

["Korbel, Gordon E." <GEKORBEL@NORSTAN.COM>]

One Trick I have used is as follows:

begin-Select
'A' &Source
Field1
Field2

 If &Source  = 'A'
   !Dependent processing
 end-if

 If &Source = 'B'
   !Dependent processing
 end-if

From
Tablea
Where ...
UNION
select 'B', Field1, Field2 from Tableb Where...
end-select
In general, union clauses, after the first, are coded as you would write
them in a query tool.  All of the 'SQR stuff' goes in the first select
clause.  The column names assume the name of the column in the first select
clause via ordinal position.

Hth,

-Gordon


-----Original Message-----
From: Richard McCutcheon [mailto:Richard_McCutcheon@HCM.HONDA.COM]
Sent: Monday, May 10, 1999 9:04 AM
To: Multiple recipients of list SQR-USERS
Subject: SQR and UNIONs


Good Afternoon.

A colleague of mine asked me the below question, and I was hoping that one
of you expert SQR programmers could help to answer it for me.

We are using SQR 3, on IBM AIX, and our Database platform is DB2.

I look forward to any help.  Thanks.
---------------------- Forwarded by Richard McCutcheon/HCM/AM/HONDA on
05/07/99 01:56 PM ---------------------------

 (Embedded     Rhonda Handley
 image moved   05/07/99 12:49 PM
 to file:      (Embedded image moved to file:
 PIC02511.PCX) PIC22021.PCX)




To:   Richard McCutcheon/HCM/AM/HONDA@HONDAAM
cc:
Subject:  SQR and UNIONs

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