Dear All,
I have been struggling some time on some way to change the parameter descriptions in the Query - Selection Criteria screen.
While doing numerous tests on trial-and-error, I accidentally hit upon a solution which appears to work!
To change a parameter description, we simply have to reverse the order in which the WHERE clause is written.
Please refer to the Query below:
I used Customization Tools to create a UDT called @SCANNING
and a UDF called U_ourref
Title : ourref
Description : Our Reference
-
If I use WHERE clause the conventional way, ie
WHERE
T1.[ImportLog] = @ourref
I get the following Selection Criteria screen:
---------------------------------------------------- Query - Selection Criteria Screen ---------------------------------------------------- Import Log ......................... [OK] [Cancel]
-
If I write WHERE clause by interchanging the 2 sides of the equality, i.e
WHERE
@ourref = T1.[ImportLog]
I get this screen:
---------------------------------------------------- Query - Selection Criteria Screen ---------------------------------------------------- Our Reference ......................... [OK] [Cancel]
Well! Is this not a way of changing the parameter description?
And it works!
-
My Problem
The solution is nice. However, I wanted to use LIKE instead of EQUAL TO
in order to allow Query to search for similar items.
With the conventional clause, I can use '%[%1]%' in declaration and LIKE in the WHERE clause.
With the reverse clause, I cannot figure out how to do it.
Could anybody help?
Thanks
Leon Lai
.
.
.
-
declare @ourref nvarchar (30) set @ourref = /*select T2.U_ourref FROM [dbo].[@SCANNING] T2 where T2.U_ourref*/ '[%1]' SELECT T0.[TaxDate] AS 'Doc Dt', T0.[DocNum] AS 'SAP Ref.', T0.[CardName] AS 'Supplier Name', T0.[DocTotal] AS 'Amount', T1.[ImportLog] AS 'Our Ref', T1.[BlockNum] AS 'Reqn No.' FROM [dbo].[OPCH] T0 INNER JOIN [dbo].[PCH1] T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN [dbo].[OJDT] T2 ON T0.[TransID] = T2.[TransID] WHERE @ourref = T1.[ImportLog]