cancel
Showing results for 
Search instead for 
Did you mean: 

Query help

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Steffen,

First, are you sure the type of variable @xyz is numeric? I noticed the values you supply are character.

And then, add ELSE everytime you would like to specify new condition.

At last, put END equal to how many times THEN are specified.

So the query should be like,


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]%%')
 
ELSE 
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]%%')
 
ELSE 
(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
END

Hope it helps.

Cheers,

Marini

Answers (1)

Answers (1)

former_member187989
Active Contributor
0 Kudos

hi steffen,

Query will work fine without

SELECT @xyz = T0.Name from [dbo].[@sufu] t0 where T0.U_Name = [%1]

Jeyakanthan

Former Member
0 Kudos

HI Jeyakanthan

hi Marini,

first, i need the


SELECT @xyz = T0.Name from [dbo].[@sufu] t0 where T0.U_Name = [%1]

Cause user need to choose what kind of value they would like to search.

I tried to set my var @xyz to a nvarchar, but i did not change anything.

Regards Steffen

EDIT:

Solved by trying different possibilities for this issue, thanks for your suggestions.