You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

sqr procedures with unions

1 reply [Last post]
Guest's picture
Guest

Hi All,
Can anyone please tell me how we can use unions in sqr procedure. can any one send few examples. i am using 3 unions in the procedure. but it is selecting the data from the first sql and ignoring the remaining two sqls. so Can any one please give your suggestions on this. please provide some examples if you have.

Lepa's picture
User offline. Last seen 2 years 2 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: sqr procedures with unions

You can use the intersect, union, and minus SQL operators in SQR queries by adding them to the SQL statement that follows the FROM and WHERE clauses.

The SELECT list for the secondary SQL statement in the union, intersect, or minus query must match the data type, number of columns, and length of columns selected in the first query. If you select string expressions or literals, make sure that the lengths of the fields in both SELECT lists are the same.

Enter the part of the SQL statement that follows the union, minus, or intersect clauses normally; that is, with commas between column names and without alias names, as shown below:

begin-select
cust_num (1,1) edit 099999
co_name (,9,30)
name (,+2,25)
city (,+2,18)
state (,+2,2)
zip (,+1) edit xxxxx-xxxx
from customers where state in ('OH', 'IN', 'MI')
union select cust_num, co_name, name, city, state, zip
from prospects where state in ('OH', 'IN', 'MI')
and first_contact >= '01-JAN-88'
order by 2
end-select

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog