Filtering Existing Values of Query

[Former Member]

Hi Guys,

I am using the following query to retrieve a batch by its location.

But when i execute the qeury, the "Existing Values" button retrieves all locations to select from.

How can i filter these results in the existing values, so the user can only select the locations from it's own warehouse?

SELECT T0.ItemCode, T0.BatchNum, T0.WhsCode, 
	T1.ItemName, T0.Quantity,         
	T0.Located, T1.SalUnitMsr, T2.Name  
FROM OIBT T0   
	INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode  
	INNER JOIN [@SUBGROUPS] T2 ON T1.U_se_subgrp = T2.Code  
WHERE T0.Located = '[%0]'
	AND  T0.Quantity > 0
	AND  T0.WhsCode = '04'

Let me clear up my question.

When run the query in sbo, it will ask me to select a location as a parameter.

This is parameter '%0'.

So the user will click the "Existing Values" button to select a location.

Now a window will open with all the locations in the table OITB to select from.

Now i want to decrease the number of location that are visible in the "Existing values" window.

I only want to show the locations of warehouse 04.

This way the user is unable to select a location from a different warehouse.

After the user selected the parameter he will click the OK button to run the query.

Thanks in Advance

Edited by: wvaert on Jun 24, 2010 2:38 PM

[Former Member · Accepted solution]

Hi,

The "Existing Values" button will retrieves all locations to select from by default. There is no way to filer it except by a temporary table to filter the value in advance..

Thanks,

Gordon

[zal_parchem2]

Wvaert - did you ever get this to work? I have been watching with interest...

Thanks - Zal

[Former Member]

Hi Zal,

Sorry but I did not get this to work.

[Former Member]

Hi,

Use this Query

It is some wat dry suggesstion but in case of mandatory need you can use this.

Declare @loc as varchar(100)
Create table #temploc(whs as varchar(100))
Insert into #temploc select Location From [dbo].[OWHS] Where WhsCode = '4'
Select @loc = whs From [dbo].[#temploc] where whs = '[%0]'
SELECT T0.ItemCode, T0.BatchNum, T0.WhsCode, 
	T1.ItemName, T0.Quantity,         
	T0.Located, T1.SalUnitMsr, T2.Name  
FROM OIBT T0   
	INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode  
	INNER JOIN [@SUBGROUPS] T2 ON T1.U_se_subgrp = T2.Code  
WHERE T0.Located = @loc
	AND  T0.Quantity > 0
	AND  T0.WhsCode = '04'
Drop table [dbo].[#temploc]

[Former Member]

Tnx Manikandan K

i will try it first thing in the morning!

[Former Member]

Hi Guys,

I tried the following query:

DECLARE @loc AS VARCHAR(100)

CREATE TABLE #tmp_temploc 
	(location VARCHAR(100)) 

Insert into #tmp_temploc 
	SELECT T0.Located
	FROM OIBT T0
	WHERE T0.WhsCode = '01'

SELECT @loc = location From dbo.#tmp_temploc  where location = '[%0]'

SELECT T0.ItemCode, T0.BatchNum, T0.WhsCode, 
	T1.ItemName, T0.Quantity,         
	T0.Located, T1.SalUnitMsr, T2.Name  
FROM OIBT T0   
	INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode  
	INNER JOIN [@SUBGROUPS] T2 ON T1.U_se_subgrp = T2.Code  
WHERE T0.Located = @loc
	AND  T0.Quantity > 0
	AND  T0.WhsCode = '01'
	
DROP TABLE #tmp_temploc

And it gives me the following message in Sap :

'' (SEWSY)

What am i doing wrong??

Edited by: wvaert on Jun 25, 2010 9:54 AM

[Former Member]

Hi,

Check that Query in SQL... To check whether the query is working properly or not... I hope it will work properly

[Former Member]

I already tried that.

It works fine in sql, but somehow sap can't handle it.

[Former Member]

Hi,

Try this...

    DECLARE @loc AS VARCHAR(100)
 
CREATE TABLE [dbo].[#tmp_temploc] 
	(location varchar (100)) 
 
Insert into [dbo].[#tmp_temploc]
	SELECT T0.Located
	FROM [dbo].[OIBT] T0
	WHERE T0.WhsCode = '01'
 
SELECT @loc = location From [dbo].[#tmp_temploc]  where location = '[%0]'
 
SELECT T0.ItemCode, T0.BatchNum, T0.WhsCode, 
	T1.ItemName, T0.Quantity,         
	T0.Located, T1.SalUnitMsr, T2.Name  
FROM [dbo].[OIBT] T0   
	INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode  
	INNER JOIN [@SUBGROUPS] T2 ON T1.U_se_subgrp = T2.Code  
WHERE T0.Located = @loc
	AND  T0.Quantity > 0
	AND  T0.WhsCode = '01'
	
DROP TABLE [dbo].[#tmp_temploc]

[Former Member]

I tried it but i get the following message:

Internal error(201) occured [Message 131-183]

Edited by: wvaert on Jun 25, 2010 12:29 PM

[Former Member]

Hi,

I tested the scenario with almost same as your query its working finely...

Anyway try this, i have modified as exact SAP Acceptable format

DECLARE @loc AS VARCHAR(100)
CREATE TABLE [dbo].[#tmp_temploc] 
	([location] nVARCHAR(100)) 
Insert into [dbo].[#tmp_temploc]
	SELECT T0.[Located]
	FROM [dbo].[OIBT] T0
	WHERE T0.[WhsCode] = '01'
SELECT @loc = Z.[location] From [dbo].[#tmp_temploc] Z where Z.[location] = '[%0]'
SELECT T0.[ItemCode], T0.[BatchNum], T0.[WhsCode], 
	T1.[ItemName], T0.[Quantity],         
	T0.[Located], T1.[SalUnitMsr], T2.[Name]  
FROM [dbo].[OIBT] T0   
	INNER JOIN [dbo].[OITM] T1 ON T0.[ItemCode] = T1.[ItemCode]  
	INNER JOIN [dbo].[@SUBGROUPS] T2 ON T1.[U_se_subgrp] = T2.[Code]  
WHERE T0.[Located] = @loc
	AND  T0.[Quantity] > 0
	AND  T0.[WhsCode] = '01'
DROP TABLE [dbo].[#tmp_temploc]

[Former Member]

Hi,

I tried your query but it comes with the folliwing error:

1). [Microsoft ].[ODBC SQL Server Driver].[SQL Server] Must specify table to select from. 2). [Microsoft ].[ODBC SQL Server Driver].[SQL Server] Statement '' (SEWSY) (S) could not be prepared.

For your information i'm using sap business one 2005 with Sql Server 2005

[Former Member]

Does anyone know what i'm doing wrong?

[Former Member]

Have you tried by [dbo\].[#temploc\] instead of dbo.#temploc?

[Former Member]

Hi Gordon,

I tried that already thank you

[Former Member]

The following code works fine.

But when i replace the location number by

'[%0]'

it stops working.

DECLARE @loc AS VARCHAR(100)

CREATE TABLE [dbo].[#tmp_temploc] 
	([location] nVARCHAR(100)) 
	
Insert into [dbo].[#tmp_temploc]
	SELECT DISTINCT T0.[Located]
	FROM [dbo].[OIBT] T0
	WHERE T0.[WhsCode] = '01'
	
SELECT @loc = Z.[location] From [dbo].[#tmp_temploc] Z where Z.[location] = '01-02/A0/0401'

SELECT T0.[ItemCode], T0.[BatchNum], T0.[WhsCode], 
	T1.[ItemName], T0.[Quantity],         
	T0.[Located], T1.[SalUnitMsr], T2.[Name]  
FROM [dbo].[OIBT] T0   
	INNER JOIN [dbo].[OITM] T1 ON T0.[ItemCode] = T1.[ItemCode]  
	INNER JOIN [dbo].[@SUBGROUPS] T2 ON T1.[U_se_subgrp] = T2.[Code]  
WHERE T0.[Located] = @loc
	AND  T0.[Quantity] > 0
	AND  T0.[WhsCode] = '01'
	
DROP TABLE [dbo].[#tmp_temploc]

Edited by: wvaert on Jun 29, 2010 8:32 AM

Edited by: wvaert on Jun 29, 2010 8:39 AM