Skip to Content
avatar image
Former Member

Filtering Existing Values of Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Jun 24, 2010 at 01:19 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 24, 2010 at 01:30 PM

    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]
    
    
    

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • Jul 13, 2010 at 01:59 PM

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

    Thanks - Zal

    Add comment
    10|10000 characters needed characters exceeded