Skip to Content
0
Former Member
Mar 27, 2009 at 11:11 AM

Query help

22 Views

Hi @ll,

i got this query, but it does not work. Any idea ?

DECLARE @xyz numeric(18,0)
SELECT @xyz = T0.Name from [dbo].[@sufu] t0 where T0.U_Name = [%1]

SELECT CASE WHEN @xyz = '1'  THEN 
(SELECT DISTINCT T0.[CardCode] , T0.[CardName], T1.[Name], T2.[Street], 
T2.[ZipCode], T2.[City], T0.[U_altekdnr]  
FROM OCRD T0  
INNER JOIN OCPR T1 ON T0.CardCode = T1.CardCode 
INNER JOIN CRD1 T2 ON T0.CardCode = T2.CardCode 
WHERE T2.[ZipCode]  LIKE '[%var]%%')

WHEN @xyz = '2'  THEN
(SELECT DISTINCT T0.[CardCode] , T0.[CardName], T1.[Name], T2.[Street], 
T2.[ZipCode], T2.[City], T0.[U_altekdnr]  
FROM OCRD T0  
INNER JOIN OCPR T1 ON T0.CardCode = T1.CardCode 
INNER JOIN CRD1 T2 ON T0.CardCode = T2.CardCode 
WHERE T1.[Name]  LIKE '[%var]%%')

WHEN @xyz = '3'  THEN
(SELECT DISTINCT T0.[CardCode] , T0.[CardName], T1.[Name], T2.[Street], 
T2.[ZipCode], T2.[City], T0.[U_altekdnr] 
FROM OCRD T0 
INNER JOIN OCPR T1 ON T0.CardCode = T1.CardCode 
INNER JOIN CRD1 T2 ON T0.CardCode = T2.CardCode 
WHERE T0.[U_altekdnr]  LIKE '[%var]%%')

END

EDIT :

This query is for searching BP code. B1 does not support to search business partners by zip codes or by contact persons. I'd like to make both fields searchable, but in only one fms for one field.