Skip to Content
0
Feb 03, 2011 at 08:56 AM

Survey Variable / Parameter in Query with JOIN SubSelect

61 Views

Dear experts,

I'm trying to show opportunities with three specified related businesspartners (ParterId 2,3 and 12). I want to be able to search for opportunities with a related businesspartner (name) with ParterId 2 AND a businessparner (name) with ParterId 3. To do that I made the following query:

DECLARE @Aannemer AS VARCHAR(100)
SET @Aannemer = /* A.[CardName] */ '%[%0]%'
DECLARE @Architect AS VARCHAR(100)
SET @Architect = /* B.[CardName] */ '%[%1]%'
DECLARE @Opdrachtgever AS VARCHAR(100)
SET @Opdrachtgever = /* C.[CardName] */ '%[%2]%'
DECLARE @Plaats AS VARCHAR(254)
SET @Plaats = /* T0.[U_Plaats] */ '%[%3]%'
DECLARE @Sortering AS VARCHAR(20)
SET @Sortering = /* T2.[U_ItemCode] */ '[%4]'


SELECT T0.[OpprId] 'Opp nr.', T0.[CardCode] 'Zakenpartner', T0.[CardName] 'ZP naam', T0.[U_Naam] 'Opp naam', T0.[U_Plaats] 'Opp plaats', T2.[U_ItemCode] 'Artikel', A.[RelatCard], A.[CardName] 'AannemerNaam', B.[RelatCard], B.[CardName] 'ArchitectNaam', C.[RelatCard], C.[CardName] 'OpdrachtgeverNaam'
FROM OOPR T0
LEFT JOIN (SELECT T10.[OpprId], T11.[RelatCard], T13.[CardName]
FROM OOPR T10 LEFT JOIN OPR2 T11 ON T10.[OpprId] = T11.[OpportId] INNER JOIN OCRD T13 ON T11.[RelatCard] = T13.[CardCode]
WHERE T11.[ParterId]=2 AND (T11.[RelatCard] IS NOT NULL AND T11.[RelatCard] NOT LIKE '')) AS A
ON T0.[OpprId] = A.[OpprId]
LEFT JOIN (SELECT T110.[OpprId], T111.[RelatCard], T113.[CardName]
FROM OOPR T110 LEFT JOIN OPR2 T111 ON T110.[OpprId] = T111.[OpportId] INNER JOIN OCRD T113 ON T111.[RelatCard] = T113.[CardCode]
WHERE T111.[ParterId]=3 AND (T111.[RelatCard] IS NOT NULL AND T111.[RelatCard] NOT LIKE '')) AS B
ON T0.[OpprId] = B.[OpprId]
LEFT JOIN (SELECT T1110.[OpprId], T1111.[RelatCard], T1113.[CardName]
FROM OOPR T1110 LEFT JOIN OPR2 T1111 ON T1110.[OpprId] = T1111.[OpportId] INNER JOIN OCRD T1113 ON T1111.[RelatCard] = T1113.[CardCode]
WHERE T1111.[ParterId]=12 AND (T1111.[RelatCard] IS NOT NULL AND T1111.[RelatCard] NOT LIKE '')) AS C
ON T0.[OpprId] = C.[OpprId]
INNER JOIN OPR4 T2 ON T0.[OpprId] = T2.[OprId]
WHERE T0.[U_Plaats] LIKE @Plaats AND ((@Sortering NOT LIKE '' AND T2.[U_ItemCode]=@Sortering) OR (@Sortering LIKE '' AND T2.[U_ItemCode] LIKE '%')) AND T0.[CardCode]<>'1' AND A.[CardName] LIKE @Aannemer AND B.[CardName] LIKE @Architect AND C.[CardName] LIKE @Opdrachtgever
ORDER BY T0.[U_Plaats]

I end up getting the error:

'Invalid column name 'N''

The problem is Parameter \[%0\], \[%1\] and \[%2\]. When I replace those with the wildcard '%' the query works. Is it possible for SAP BO to use a Parameter on a joined subselect?

Any suggestions?