How to correct the union function

hi everyone,

i have this union function in a store procedure, its actually four different queries which takes the same parameter and give the results, but its only giving top two right now
Create Procedure Mark_down_report
 
@week Int,
@season Varchar(80)
As
 
begin
/*MAIL ORDER Season*/
 
(select  'MAIL_ORDER_SEASON',
	 al.level1_desc,
	 'Total_sales' =((SUM(ph.Sales_spvalue))-((SUM(ph.REFND_SPVALUE)))),
	 'POS_DISC' = (Sum(ph.SALES_DISC_VALUE)-(SUM(ph.REFND_DISC_VALUE)))/1000,
 	 'MARK_DOWN_DISC' = ((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))/1000,
	 'TOTAL_REDUCTION' = (((Sum(ph.SALES_DISC_VALUE)-Sum(ph.refnd_DISC_VALUE)))+(((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))))/1000,
	 'REDUCED_TOTAL_%' = (((Sum(ph.SALES_DISC_VALUE)-Sum(ph.refnd_DISC_VALUE))+((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))))/((SUM(ph.Sales_spvalue))-((SUM(ph.REFND_SPVALUE)))) * 100,
	 'SOLD_UNITS'=Sum(sales_units)-sum(refnd_units)
from product_history ph,product_detail pd,
anal_level al,branch b,colour c,
product_master pm
where ph.week_selector = @week
and ph.branch_id = b.branch_id
and ph.sku_id = pd.sku_id
and pm.prod_id = pd.product_id
and al.j_code = pm.j_code_link
and al.level_no = 4
and c.colour_id = pd.colour_id
and al.level1_desc = '@Season'
and ph.branch_id in ('51')
group by ph.Week_selector,al.level1_desc)
 
UNION
 
/*RETAIL SEASON*/
 
(select'RETAIL_SEASON',
 al.level1_desc,
'Total_sales' =((SUM(ph.Sales_spvalue))-((SUM(ph.REFND_SPVALUE)))),
'POS_DISC' = (Sum(ph.SALES_DISC_VALUE)-(SUM(ph.REFND_DISC_VALUE)))/1000,
'MARK_DOWN_DISC' = ((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))/1000,
'TOTAL_REDUCTION' = (((Sum(ph.SALES_DISC_VALUE)-Sum(ph.refnd_DISC_VALUE)))+(((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))))/1000,
'REDUCED_TOTAL_%' = (((Sum(ph.SALES_DISC_VALUE)-Sum(ph.refnd_DISC_VALUE))+((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))))/((SUM(ph.Sales_spvalue))-((SUM(ph.REFND_SPVALUE)))) * 100,
'SOLD_UNITS'=Sum(sales_units)-sum(refnd_units)
from product_history ph,product_detail pd,
anal_level al,branch b,colour c,
product_master pm
where ph.week_selector = @week
and ph.branch_id = b.branch_id
and ph.sku_id = pd.sku_id
and pm.prod_id = pd.product_id
and al.j_code = pm.j_code_link
and al.level_no = 4
and c.colour_id = pd.colour_id
and al.level1_desc = '@season'
and ph.branch_id not in ('51')
group by ph.Week_selector,al.level1_desc)
go 
Union
 
/* MAIL ORDER */
 
(select 
 
	'MAIL ORDER',
	'Season' = 0,
	'Total_sales' =((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))),
	'POS_DISC' = (Sum(SALES_DISC_VALUE)-(SUM(REFND_DISC_VALUE)))/(((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))))*100,
	'MARK_DOWN' = (SUM(sales_mkdn_value)-SUM(refnd_mkdn_value))/1000,
	'TOTAL_REDUCTION' = ((Sum(SALES_DISC_VALUE)-Sum(refnd_DISC_VALUE))+((SUM(sales_mkdn_value)-SUM(refnd_mkdn_value))))/1000,
	'REDUCED_TOTAL_%' = ((((Sum(SALES_DISC_VALUE)-Sum(refnd_DISC_VALUE))+((SUM(sales_mkdn_value)-SUM(refnd_mkdn_value)))))/((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))) * 100),
	'XX' = 0
from product_history
where week_selector = @week
and branch_id not in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20',
'21','22','23','24','25','26','27','28','29','32','30','31','33',
'34','35','36','37','38','39','40','41','42','43','44','48','49','50','59'
))
go
UNION
                   /* RETAIL*/
(select 
 
	'RETAIL','Season' = 0,
	'Total_sales' =((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))),
	'POS_DISC' = (Sum(SALES_DISC_VALUE)-(SUM(REFND_DISC_VALUE)))/(((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))))*100,
	'MARK_DOWN' = (SUM(sales_mkdn_value)-SUM(refnd_mkdn_value))/1000,
	'TOTAL_REDUCTION' = ((Sum(SALES_DISC_VALUE)-Sum(refnd_DISC_VALUE))+((SUM(sales_mkdn_value)-SUM(refnd_mkdn_value))))/1000,
	'REDUCED_TOTAL_%' = ((((Sum(SALES_DISC_VALUE)-Sum(refnd_DISC_VALUE))+((SUM(sales_mkdn_value)-SUM(refnd_mkdn_value)))))/((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))) * 100),
	'XX' = 0
from product_history
where week_selector = @week
and branch_id not in ('51','32','33','43')
)
 
end

Open in new window

ammartahir1978Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
You may wish to use UNION ALL instead of UNION and UNION will eliminate duplicate rows.
Failing that check each SELECT individually.
Cheers, Andrew
0
ammartahir1978Author Commented:
shall i replace all the UNION fuctions with UNION ALL or do i have to just use UNION ALL once?

please help
0
TextReportCommented:
Each UNION needs to be a UNION ALL, depending though where the issue is.
Cheers, Andrew
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

ammartahir1978Author Commented:
i have done it and this storeprocedure is giving me only results from buttom two queries
0
ee_rleeCommented:
try to remove GO before UNION
0
ammartahir1978Author Commented:
i have changed it but still its giving me last two query results
CREATE Procedure Mark_down_report
 
@week Int,
@season Varchar(80)
As
 
begin
/*MAIL ORDER Season*/
 
(select  'MAIL_ORDER_SEASON',
	 al.level1_desc as SEASON,
	 'Total_sales' =((SUM(ph.Sales_spvalue))-((SUM(ph.REFND_SPVALUE)))),
	 'POS_DISC' = (Sum(ph.SALES_DISC_VALUE)-(SUM(ph.REFND_DISC_VALUE)))/1000,
 	 'MARK_DOWN_DISC' = ((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))/1000,
	 'TOTAL_REDUCTION' = (((Sum(ph.SALES_DISC_VALUE)-Sum(ph.refnd_DISC_VALUE)))+(((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))))/1000,
	 'REDUCED_TOTAL_%' = (((Sum(ph.SALES_DISC_VALUE)-Sum(ph.refnd_DISC_VALUE))+((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))))/((SUM(ph.Sales_spvalue))-((SUM(ph.REFND_SPVALUE)))) * 100,
	 'SOLD_UNITS'=Sum(sales_units)-sum(refnd_units)
from product_history ph,product_detail pd,
anal_level al,branch b,colour c,
product_master pm
where ph.week_selector = @week
and ph.branch_id = b.branch_id
and ph.sku_id = pd.sku_id
and pm.prod_id = pd.product_id
and al.j_code = pm.j_code_link
and al.level_no = 4
and c.colour_id = pd.colour_id
and al.level1_desc = '@Season'
and ph.branch_id in ('51')
group by ph.Week_selector,al.level1_desc)
 
UNION All
 
/*RETAIL SEASON*/
 
(select'RETAIL_SEASON',
 al.level1_desc as SEASON,
'Total_sales' =((SUM(ph.Sales_spvalue))-((SUM(ph.REFND_SPVALUE)))),
'POS_DISC' = (Sum(ph.SALES_DISC_VALUE)-(SUM(ph.REFND_DISC_VALUE)))/1000,
'MARK_DOWN_DISC' = ((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))/1000,
'TOTAL_REDUCTION' = (((Sum(ph.SALES_DISC_VALUE)-Sum(ph.refnd_DISC_VALUE)))+(((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))))/1000,
'REDUCED_TOTAL_%' = (((Sum(ph.SALES_DISC_VALUE)-Sum(ph.refnd_DISC_VALUE))+((SUM(ph.sales_mkdn_value)-SUM(ph.refnd_mkdn_value)))))/((SUM(ph.Sales_spvalue))-((SUM(ph.REFND_SPVALUE)))) * 100,
'SOLD_UNITS'=Sum(sales_units)-sum(refnd_units)
from product_history ph,product_detail pd,
anal_level al,branch b,colour c,
product_master pm
where ph.week_selector = @week
and ph.branch_id = b.branch_id
and ph.sku_id = pd.sku_id
and pm.prod_id = pd.product_id
and al.j_code = pm.j_code_link
and al.level_no = 4
and c.colour_id = pd.colour_id
and al.level1_desc = '@season'
and ph.branch_id not in ('51')
group by ph.Week_selector,al.level1_desc)
 
Union all
 
/* MAIL ORDER */
 
(select 
 
	'MAIL ORDER',
	'Season' = 0,
	'Total_sales' =((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))),
	'POS_DISC' = (Sum(SALES_DISC_VALUE)-(SUM(REFND_DISC_VALUE)))/(((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))))*100,
	'MARK_DOWN' = (SUM(sales_mkdn_value)-SUM(refnd_mkdn_value))/1000,
	'TOTAL_REDUCTION' = ((Sum(SALES_DISC_VALUE)-Sum(refnd_DISC_VALUE))+((SUM(sales_mkdn_value)-SUM(refnd_mkdn_value))))/1000,
	'REDUCED_TOTAL_%' = ((((Sum(SALES_DISC_VALUE)-Sum(refnd_DISC_VALUE))+((SUM(sales_mkdn_value)-SUM(refnd_mkdn_value)))))/((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))) * 100),
	'SOLD_UNITS' = Sum(sales_units)-sum(refnd_units)
from product_history
where week_selector = @week
and branch_id not in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20',
'21','22','23','24','25','26','27','28','29','32','30','31','33',
'34','35','36','37','38','39','40','41','42','43','44','48','49','50','59'
))
 
UNION All
                   /* RETAIL*/
(select 
 
	'RETAIL','Season' = 0,
	'Total_sales' =((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))),
	'POS_DISC' = (Sum(SALES_DISC_VALUE)-(SUM(REFND_DISC_VALUE)))/(((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))))*100,
	'MARK_DOWN' = (SUM(sales_mkdn_value)-SUM(refnd_mkdn_value))/1000,
	'TOTAL_REDUCTION' = ((Sum(SALES_DISC_VALUE)-Sum(refnd_DISC_VALUE))+((SUM(sales_mkdn_value)-SUM(refnd_mkdn_value))))/1000,
	'REDUCED_TOTAL_%' = ((((Sum(SALES_DISC_VALUE)-Sum(refnd_DISC_VALUE))+((SUM(sales_mkdn_value)-SUM(refnd_mkdn_value)))))/((SUM(Sales_spvalue))-((SUM(REFND_SPVALUE)))) * 100),
	'SOLD_UNITS' = Sum(sales_units)-sum(refnd_units)
from product_history
where week_selector = @week
and branch_id not in ('51','32','33','43')
)
 
end
GO

Open in new window

0
ee_rleeCommented:
you have this condition in your 1st 2 statments

 al.level1_desc = '@Season'

try to change it to

 al.level1_desc = @Season
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
appariCommented:
are you sure all the four queries do return the data?
remove union/union all and run the procedure. it should res\turn you four result sets. check if all the four are returning the data or not. if there is atleast one record in each of the resultsets it should return all the results together if you use union. if any of the resultsets are not returning the data check that query or post sample data and the results you are expecting.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.