Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a sql that is as under:

SQL
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
SQL
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.

SQL
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 ---------------------------------------------

SQL
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
Updated 19-Dec-12 11:50am
v2
Comments
André Kraak 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

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 ?
 
Share this answer
 
v2
Comments
ShaikhM 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 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 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900