Skip to Content
avatar image
Former Member

Variable For Subquery In Where Clause

Dear All

I got a little problem with following Query:

There is a need for the user to select the customer.

The query works fine if I put the customersname in the where clause (table 'a') manually like this: '%customer%'

But it doens't work with variables: '%[%0]%'

How can I solve this matter?

Thanks ahead.

select * from (

--Rahmen
SELECT distinct 
'A'						AS 'Belegart',
isnull(t10.Number,'')				AS 'KO-Nr. SAP',
isnull(t10.U_docnumold,'')			AS 'BW-Nr.',
''						AS 'Rg.-Nr.',
t10.StartDate			AS 'Belegdat.',
t10.EndDate				AS 'Enddat.',
t10.BpCode				AS 'Kundennr.',	
t10.BpName				AS 'Kunde',
t11.ItemCode			AS 'Artikel',
t11.ItemName			AS 'Artikelbez.',
t12.FrgnName			AS 'Bez. 2',
t11.UnitPrice			AS 'VK-Preis',
t11.Currency			AS 'Währung',
t11.PlanQty				AS 'KO-Menge',
isnull(t11.U_rest,1)	AS 'Menge',
isnull(t10.U_FWKursKopf,0)	AS 'Kurs',
t71.PymntGroup			AS [Zahlungsbed.],

CASE 
When 
t10.U_FWKursKopf= '0'
THEN t11.U_rest * t11.UnitPrice
ELSE t11.U_rest * t11.UnitPrice / t10.U_FWKursKopf
END as [Warenwert EUR]
 
FROM OOAT t10
INNER JOIN OAT1 t11
ON t10.AbsID = t11.AgrNo
INNER JOIN OITM t12
ON t11.ItemCode = t12.ItemCode
LEFT JOIN OCRD t70 ON t70.CardCode = t10.BpCode
INNER JOIN OCTG t71 ON t70.GroupNum = t71.GroupNum

WHERE t10.BpCode >= 80000 AND
T10.[Status] = 'A' AND
T10.[Status] <> 'C' AND  
T11.[LineStatus] = 'O' AND
isnull(t11.U_rest,0) > '0'

UNION ALL

SELECT 
'WE'						AS 'Belegart',

CASE
when t1.AgrNo IS NOT NULL THEN t51.number
ELSE t1.U_rahmen
END						AS 'KO-Nr. SAP', 

CASE 
when t1.AgrNo IS NOT NULL THEN 
	(
	select 
	case 
	when t20.u_docnumold <> '' then t20.u_docnumold 
	else t20.number 
	end
	from OOAT t20 WHERE t1.AgrNo = t20.AbsID
		)
	when t1.AgrNo IS NULL OR t1.AgrNo='0' THEN 
		(
		select 
		case 
		when t20.u_docnumold <> '' then t20.u_docnumold 
		else t20.number 
		end
		from OOAT t20 WHERE t1.U_rahmen = t20.AbsID
			)
ELSE 0
END						AS 'BW-Nr.',
isnull(T0.[DocNum],'')	AS 'Rg.-Nr.',

T0.[DocDate]			AS 'Belegdat.', 
null					AS 'Enddat.',
T0.[CardCode]			AS 'Kundennr.',	 
T0.[CardName]			AS 'Kunde', 
T1.[ItemCode]			AS 'Artikel', 
T2.ItemName				AS 'Artikelbez.',
T2.FrgnName			 	AS 'Bez. 2',
T1.[Price]				AS 'VK-Preis',
T1.[Currency]			AS 'Währung', 
0						AS 'KO-Menge', 
T1.[Quantity]			AS 'Menge',
isnull(t1.Rate,1)	AS 'Kurs',
t61.PymntGroup			AS [Zahlungsbed.],


CASE 
When 
t1.Rate = '0'
THEN T1.[Quantity] * T1.[Price]
ELSE T1.[Quantity] * T1.[Price] / t1.Rate
END as [Warenwert EUR]


FROM OPDN T0  
INNER JOIN PDN1 T1 
ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN OITM T2
ON T1.[ItemCode] = T2.[ItemCode]
LEFT OUTER JOIN OOAT t51 ON t1.AgrNo = t51.AbsID
--LEFT JOIN OAT1 t52 ON t52.AgrNo = t51.AbsID
LEFT JOIN OCRD t60 ON T0.CardCode = t60.CardCode
INNER JOIN OCTG t61 ON t60.GroupNum = t61.GroupNum

where T0.[CANCELED] = 'N' 

) as a


where 
a.[Kunde] Like '%[%0]%' 


ORDER BY 'Kundennr.', 'Artikel','Belegart' asc,'Belegdat.'asc,'KO-Nr. SAP'asc
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 05, 2017 at 02:15 AM

    You need to declare the variable outside the select query and link the [%0] to it.

    Then call the variable inside the query.

    For example:

    declare @acctname as varchar(100)

    set @acctname=(select max(a.acctname) from oact a where a.acctname like '%[%0]%')

    select x.AcctName, x.AcctCode from oact x where x.AcctName =@acctname

    Add comment
    10|10000 characters needed characters exceeded