Is there a limit to how many UNIONs in one statement?

[BJ9]

I can't seem to find any information on this. I didn't think there was a limit but I can't get my code to work. It ignores one of the unions. Here is my code:

select store.short_name as "Store", 
		store.store_id,
		store.sort_id,
		category.description as "Description", 
		sum(sale_item.selling_price) as sales,		
		sum(sale_item.cost) as costsales,
		sum(sale_item.selling_price - sale_item.cost) as gp,
		CASE WHEN(sum(sale_item.selling_price)) <> 0 
			THEN(((sum(sale_item.selling_price) - sum(sale_item.cost)))/ sum(sale_item.selling_price))*100 
			ELSE '0'
			END AS gppercent
			
	FROM store
	
	join sale_item on store.store_id = sale_item.store_id 
	join category on sale_item.category_number = category.category_number AND sale_item.store_id = category.store_id
	join sale on store.store_id = sale.store_id and
		sale.transaction_number = sale_item.transaction_number and sale.date = sale_item.date
		where store.store_id = 71 and
		sale_item.date between '2006-07-01' and '2006-07-31' and
		sale_item.refund_qty = 0 and
		sale_item.selling_price <> 0 and
		sale.is_voided = 'N' and
		category.category_number not in('079998','329998','359998','369998')
		
	group by 1,2,3,4
	
	UNION ALL
	
	select store.short_name as "Store", 
		store.store_id,
		store.sort_id,
		category.description as "Description", 
		sum(sale_item.price) as sales,		
		sum(sale_item.cost) as costsales,
		sum(sale_item.price - sale_item.cost) as gp,
		CASE WHEN(sum(sale_item.price)) <> 0 
			THEN(((sum(sale_item.price) - sum(sale_item.cost)))/ sum(sale_item.price))*100 
			ELSE '0'
			END AS gppercent
			
	FROM store
	
	join sale_item on store.store_id = sale_item.store_id 
	join category on sale_item.category_number = category.category_number AND sale_item.store_id = category.store_id
	join sale on store.store_id = sale.store_id and
		sale.transaction_number = sale_item.transaction_number and sale.date = sale_item.date
		where store.store_id = 71 and
		sale_item.date between '2006-07-01' and '2006-07-31' and
		sale_item.selling_price = 0 and
		sale_item.refund_qty = 0 and
		sale.is_voided = 'N' and
		category.category_number not in('079998','329998','359998','369998')
		
	group by 1,2,3,4
	
	UNION
	
	select store.short_name as "Store", 
		store.store_id,
		store.sort_id,
		category.description as "Description", 
		sum((sale_item.selling_price + sale.adjust1)*sale_item.qty_sold) as sales,
		sum(sale_item.cost) as costsales,
		sum(((sale_item.selling_price + sale.adjust1)*sale_item.qty_sold) - sale_item.cost) as gp,
		CASE WHEN(sum(sale_item.selling_price + sale.adjust1)) <> 0 
			THEN((sum((sale_item.selling_price + sale.adjust1)*sale_item.qty_sold) - sum(sale_item.cost))/ sum((sale_item.selling_price + sale.adjust1)*sale_item.qty_sold))*100 
			ELSE '0'
			END AS gppercent
			
	FROM store
	
	join sale_item on store.store_id = sale_item.store_id 
	join category on sale_item.category_number = category.category_number AND sale_item.store_id = category.store_id
	join sale on store.store_id = sale.store_id and
		sale.transaction_number = sale_item.transaction_number and sale.date = sale_item.date
		where store.store_id = 71 and
		sale_item.date between '2006-07-01' and '2006-07-31' and
		sale_item.refund_qty = 0 and
		sale.is_voided = 'N' and
		category.category_number in('079998','329998','359998','369998')
	
	group by 1,2,3,4
	order by 3,6 desc

Can anyone see why it would be ignoring the second union. I've been staring at this for too long. There must be something I'm missing.

Any help would be appreciated.

Thanks
BJ

 

[feherke]

Hi

No, I never heared about such limitation and I am sure the only limit is the machines capacity.

The first and the second [tt]select[/tt]s are quite similar, the difference is only in calculation of gp an gppercent. Are you sure there is no strange situation that those expressions evaluates to the same value in both the first and the second [tt]select[/tt] ? In this case use [tt]union all[/tt] to not discard repeated tuples.

Feherke.

http://rootshell.be/~feherke/

 

[BJ9]

Thanks for the reply feherke.

I have used UNION ALL between the first two selects and it's made no difference.

The difference between the two selects is the first one is pulling sales from sale_item.selling_price where selling_price is > 0. The second is pulling sales from sale_item.price where selling_price = 0.

Any other things I could try?

Thanks
BJ

 

[feherke]

Hi

No more proper idea. At this point I would start to split, chop or any other way to simplify that huge query until I find the reason of its misbehaving.

Feherke.

http://rootshell.be/~feherke/

 

[BJ9]

Thanks feherke, I appreciate it.

I have taken it apart. The strange thing is when I run each SQL statement separately they each work fine.

It's only when I put them together that it ignores the second statement. I've tried with just statement one and two, all I get is the data from statement one, etc. I've tried every combination I could think of.

I even tried it using a CASE in the statement to simplify it. I know the data's there, it just won't pull into my report.

BJ