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?