[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How to correct the union function

Posted on 2008-01-27
8
Medium Priority
?
158 Views
Last Modified: 2010-03-20
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

0
Comment
Question by:ammartahir1978
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 20753722
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
 

Author Comment

by:ammartahir1978
ID: 20753733
shall i replace all the UNION fuctions with UNION ALL or do i have to just use UNION ALL once?

please help
0
 
LVL 28

Expert Comment

by:TextReport
ID: 20753741
Each UNION needs to be a UNION ALL, depending though where the issue is.
Cheers, Andrew
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 

Author Comment

by:ammartahir1978
ID: 20753752
i have done it and this storeprocedure is giving me only results from buttom two queries
0
 
LVL 26

Expert Comment

by:ee_rlee
ID: 20753762
try to remove GO before UNION
0
 

Author Comment

by:ammartahir1978
ID: 20753781
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
 
LVL 26

Accepted Solution

by:
ee_rlee earned 2000 total points
ID: 20753824
you have this condition in your 1st 2 statments

 al.level1_desc = '@Season'

try to change it to

 al.level1_desc = @Season
0
 
LVL 39

Expert Comment

by:appari
ID: 20753831
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question