Help wit Union SQL

[cbochner]

Hi,

I'm trying to return a single record usin a UNION as Follow:

SELECT '' AS customer_code,
'' AS Cust_Bus_name,
'' AS Bill_Buss,
'' AS Bill_Contact,
'' AS Billto_phone,
'' AS Bill_Add1,
'' AS Bill_Add2,
'' AS Bill_City,
'' AS Bill_State,
'' AS Bill_Zip,
shpr.bus_name AS Ship_Buss,
shpr.contact AS Ship_Contact,
shpr.address1 AS Ship_Add1,
shpr.address2 AS Ship_Add2,
shpr.address3 AS Ship_Add3,
shpr.city AS Ship_City,
shpr.state AS Ship_State,
shpr.zip AS Ship_Zip,
shpr.phone AS Ship_Phone,
shpr.phone_ext AS Ship_Ext
FROM Bhroapse a,
Bhroapsd b,
stoordre c,
Strcustr cust,
Strshipr shpr
WHERE
cust.cust_code = '11450009'
--a.cust_code in ('28084153','683078','11958728','688736','23648610','15605647')
and c.order_date >= "01/01/07"
and c.order_date <= today
and order_status in ('PST','ACT')
and c.state in ('NE','MD','VA','DC')
and a.p_code = b.p_code
and b.cust_code = c.cust_code
and c.cust_code = cust.cust_code
and c.order_type <> 'CRM'
and (c.cust_code = shpr.cust_code
and c.ship_to_code = shpr.ship_to_code)

UNION

SELECT c.cust_code AS customer_code,
cust.bus_name as Cust_Bus_Name,
c.bus_name AS Bill_Buss,
c.contact AS Bill_Contact,
c.phone AS Billto_phone,
c.address1 AS Bill_Add1,
c.address2 AS Bill_Add2,
c.city AS Bill_City,
c.state AS Bill_State,
c.zip AS Bill_Zip,
'' AS Ship_Buss,
'' AS Ship_Contact,
'' AS Ship_Add1,
'' AS Ship_Add2,
'' AS Ship_Add3,
'' AS Ship_City,
'' AS Ship_State,
'' AS Ship_Zip,
'' AS Ship_Phone,
'' AS Ship_Ext
FROM Bhroapse a,
Bhroapsd b,
Stoordre c,
Strcustr cust
WHERE
cust.cust_code = '11450009'
--a.cust_code in ('28084153','683078','11958728','688736','23648610','15605647')
and c.order_date >= "01/01/07"
and c.order_date <= today
and order_status in ('PST','ACT')
and c.state in ('NE','MD','VA','DC')
and a.p_code = b.p_code
and b.cust_code = c.cust_code
and c.cust_code = cust.cust_code
and c.order_type <> 'CRM'

It doesn't work for some reason; I get two records. PLEASE HELP!

Thank You,

CHaim Bochner

 

[carp]

If you are getting two rows, then it is because your queries find two rows that match your criteria yet differ from one another. Once you figure out what that difference is, modify your queries to exclude the row you don't want.

 

[PHV]

It doesn't work for some reason; I get two records
So, it works as expected: one row with the Ship_ values and one row with the Bill_ values.

If you want a single row, then don't use an UNION operator but the DISTINCT predicate.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[carp]

If the returned rows are truly identical, then a UNION should only yield one row. However, looking at the select lists, you're not likely to get identical rows. As an example, just look at the first field (customer_code). The first query will return a null, while the second query will return a non-null value (assuming c.cust_code is not null). This alone is sufficient to give you two rows!