Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server-2008 , +
I have a sql that is as under:
 
SELECT ib.branch_no,
           ib.on_hand,
           p.weightedav,
           p.item_code,
    FROM physical p
    INNER JOIN 
           item_branch as ib on p.item_code = ib.item_code
    WHERE ib.on_hand <> 0
This SQL returns only those branch_no that have on_hand <> 0.
 
I am trying to get all the branch_nos irrespective of the on_hand field, but while still using the where on_hand clause.
 
Taking the on_hand clause away solves my problem, but gives me large amount of un-needed rows with 0's.
 
I am using SQL SERVER 2008 R2.
 
Thanks in advance for any guidance. Please apologize if I am missing any information.
--------------------------------SAMPLE RESULTSET --------------
This is the result of using on_hand<>0
branchno weighted_av	item_code   x_value	y_value	on_hand	PhysicalOH
999	 72.00	     80S1211001	    Ink/White	10	16	16
999	 72.00	     80S1211002	    Ink/White	12	19	19
1	 72.00	     80S1211003	    Ink/White	14	-1	17
17	 72.00	     80S1211003	    Ink/White	14	1	17
999	 72.00	     80S1211003	    Ink/White	14	17	17
1	 72.00	     80S1211004	    Ink/White	16	-1	15    
 

--------------------------------SAMPLE RESULTSET --------------
This is the result of NOT using the on_hand<>0 (SHOW ALL - NO FILTERING).
If you look closely this result set has extra branches i.e. Branch numbers 2 3 4 5. By adding the filter I lose these branches which I dont want to. I still want to retain them while I am using a filter.

branch_no weighted_av	item_code	x_value	    y_value on_hand	PhysicalOH
999	   72.00	80S1211001	Ink/White	10	16	16
999	   72.00	80S1211002	Ink/White	12	19	19
1	   72.00	80S1211003	Ink/White	14	-1	17
17	   72.00	80S1211003	Ink/White	14	1	17
999	   72.00	80S1211003	Ink/White	14	17	17
999	   72.00	80S1211003	Ink/White	14	-1	17
2	   72.00	80S1211003	Ink/White	14	0	17
3	   72.00	80S1211003	Ink/White	14	0	17
4	   72.00	80S1211003	Ink/White	14	0	17
5	   72.00	80S1211003	Ink/White	14	0	17 
1	   72.00	80S1211004	Ink/White	16	-1	15
 

Running without a filter results in 300K rows which crashes reporting services.
 
------------------------------------------ENTIRE SQL QUERY ---------------------------------------------
 
    select	
    	 ib.branch_no, 
		p.weighted_av,
		p.item_code, 
		p.x_value, 
		p.y_value, 
		ib.on_hand,
		p.on_hand as PhysicalOH,
		ip.price,
		i.item_code as StyleCode, 
		i.description, 
		i.cat1, 
		i.cat2, 
		i.cat3, 
		i.cat4, 
		np.is_style_yn, 
		si.supplier_code ,
    	        ysv.sort as YSort
    from physical  as p
    	left outer JOIN 
    	item_branch as ib on p.item_code = ib.item_code -- and ib.on_hand <> 0
    	INNER JOIN 
    	item_price as ip on p.item_code = ip.item_code  and ip.price_type = 'P1'
    	INNER JOIN 
    	style_values as sv on p.style_code = sv.style_code and p.x_value = sv.value  
    	INNER JOIN 
    	style_values as ysv on p.style_code = ysv.style_code and p.y_value = ysv.value and ysv.axis = 'Y'  
    	INNER JOIN 
    	ITEM as i on p.style_code = i.item_code 
    	INNER JOIN 
    	NON_PHYSICAL as np ON i.item_code = np.item_code and np.is_style_yn = 1
    	INNER JOIN 
    	supplier_item as si ON i.item_code = si.item_code and si.pref_supp_no = 1
    where --ib.on_hand <> 0 and  
    sv.axis = 'X' and
    i.item_code in 
    	(SELECT ITEM.item_code
    	 FROM ITEM 
    		    INNER JOIN 
    			NON_PHYSICAL ON ITEM.item_code = NON_PHYSICAL.item_code 
    		    LEFT JOIN
    			supplier_item ON Item.item_code = supplier_item.item_code  and	pref_supp_no = 1 						
    	WHERE NON_PHYSICAL.is_style_yn = 1 and  ITEM.cat1 = 'Verge Sportswear Ltd' )
    order by 
    		si.supplier_code, 
    		i.cat4, 
    		i.cat3, 
    		i.cat2, 
    		i.cat1,
    		sv.sort
Posted 19-Dec-12 11:12am
ShaikhM808
Edited 19-Dec-12 11:50am
v2
Comments
André Kraak at 19-Dec-12 17:18pm
   
It might help if you gave a sample (relevant) of the full data you are getting and point out the data you do not want displayed.
 
You can use Improve question to add this information to your question.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This makes no sense. IF you're using the where, then you're filtering. Why do you want to filter on things you don't care about ? Why is it the case that the records you get when you don't filter, are not needed ? Are you getting duplicates and need to use DISTINCT ?
 
What is not clear here is, the data that you're getting is too much, but what data that you're getting, is data you don't want ? What do you want to filter on to get less than 300k rows ?
  Permalink  
v2
Comments
ShaikhM at 19-Dec-12 19:34pm
   
Yup thats where I am stuck. I dont want to filter on anything. And most important data for me is BRANCH_NO. I want all branch_no as I am using them in the RDL. BUt filtering removes some branch_nos which leaves my report incomplete.
Christian Graus at 19-Dec-12 19:35pm
   
Well, if all the data is more than you can handle, and you want it all, the only answer I can see is a report that gives summary info, and then drills down, or pagination ( showing one page of data at a time )
ShaikhM at 19-Dec-12 19:54pm
   
Thats made me think..Cool thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100