SQL Query to Display Items in a Bin Location

[Former Member]

Hi SQL Experts. I've written a query to display items in a particular Row and can choose eg A to D rows. What I want to do now is to be able to select the sub levels and select eg: A12 to D14.

I'm on V9 PL07 Can anyone help? This is what I have so far:

select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as 'On Hand', t2.BinCode, t2.SL1Code, t2.SL2Code, t0.WhsCode from OITW T0

inner join OITM t1 on t0.ItemCode = t1.ItemCode

inner join OBIN t2 on t2.AbsEntry = T0.DftBinAbs

left outer join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode

where t2.SL1Code between '[%0]' and '[%1]'

order by t2.SL1Code, t2.SL2Code, t0.ItemCode

Thanks

[former_member662620]

you can use standard report in B1. Bin Location Content List

[Former Member]

It's not as easy as that! I need to show items that should be in the bins if we had stock. The standard report will only show items that are in stock.

[Former Member]

Hi,

Could you show the result of your current query? What parameter you have put in?

Thanks,

Gordon

[Former Member]

Hi Gordon. I get a selection box where I can choose a row by letter:

I would now like to add a sub level like A2 to D4

Thanks

[Former Member]

I've been trying to add in SL3 Code but this doesn't seem to work. Any ideas Gordon?

Thanks

[Former Member]

First try:

select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as 'On Hand', t2.BinCode, t2.SL1Code, t2.SL2Code, t0.WhsCode from OITW T0

inner join OITM t1 on t0.ItemCode = t1.ItemCode

inner join OBIN t2 on t2.AbsEntry = T0.DftBinAbs

left outer join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode

where t2.SL2Code LIKE '[%0]%'

order by t2.SL1Code, t2.SL2Code, t0.ItemCode

You may enter 'A', 'B', 'C', or 'D' to see what you get. Your range is not an actual range from data view. A work around is need to create the condition. I don't have the environment to test for you.