Union Stored Procedure

[thecricketer]

Could someone please give me an example of a simple procedure in which 2 tables have a union join and all records retrieved.

The reason is I have a stored procedure to do this, which when run in the query analyser works. However I get a syntax error when trying to use this data in Crystal Reports. Can anyone tell me what may be wrong?

http://www.ais-lims.com

 

[osjohnm]

Hi

I wasn't sure if you wanted something like this

select name, surname, job_title from employees
where dept ='Admin'
union
select name, surname, job_title from employees
where dept = 'Finance'
order by name

or if you actually want a stored proc example like this:

CREATE PROCEDURE [dbo].[SP_ORDER_ACTIVITY] @startdate as datetime, @enddate as datetime
AS

select g.order_group_no, u.cost_centre, a.name[Authoriser], g.authorised_value[Auth Amount], sum(po.quantity * po.value)[PO Amount]
from order_group g, order_header h, purchase_header ph, purchase_order po, user_info u, user_info a
where g.order_group_no = h.order_group_no
and h.order_no = ph.order_no
and ph.po_no = po.po_no
and ph.status_code <> 'cancelled'
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id
and g.authoriser = a.user_id
group by g.order_group_no, u.cost_centre, a.name, g.authorised_value
UNION
select g.order_group_no, u.cost_centre, a.name[Authoriser], g.authorised_value[Auth Amount], 0
from order_group g, order_header h, user_info u, user_info a
where g.order_group_no = h.order_group_no
and h.order_no not in (select order_no from purchase_header where order_no is not null)
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id
and g.authoriser = a.user_id
and g.order_group_no not in
(select g.order_group_no from order_group g, order_header h, purchase_header ph, purchase_order po, user_info u
where g.order_group_no = h.order_group_no
and h.order_no = ph.order_no
and ph.po_no = po.po_no
and ph.status_code <> 'cancelled'
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id)
group by g.order_group_no, u.cost_centre, a.name, g.authorised_value
order by g.order_group_no
GO

unfortunately I can't help you with crystal reports since we don't use crystal reports but maybe if you posted your union statement someone could have a look at it.