Skip to Content
0
Jun 10, 2020 at 04:38 PM

Query to find account with a city made in SAP B1

101 Views

I wrote this query but it will not execute in in B1 when I use the [%0] variable, but if I replace with text it works. What needs to be done to fix this?

SELECT distinct bp.[CardCode], bp.[CardName], 
bp.[MailAddres], bp.[MailZipCod], bp.[Phone1], bp.[Phone2], bp.[Fax], 
bp.[CntctPrsn], bp.[MailCity], bp.[MailCountr],
bp.[State2],
invoice.DocNum AS 'Last Order Num',invoice.DocTotal AS 'Last Total',
invoice.DocDate AS 'Last Order Date'
FROM                 (SELECT                    CardCode, 
                                         MAX(oinv.DocEntry) AS 'DocEntry'
                     FROM                 OINV
                     GROUP BY             CardCode) AS latesti
LEFT JOIN            OINV AS invoice ON (invoice.DocEntry = latesti.DocEntry)
INNER JOIN           OCRD AS bp ON invoice.cardcode = bp.cardcode
WHERE (bp.[MailCITY]  liKE '%%[%0]%%' ) 
and bp.[FROZENFOR] = 'N' 


UNION ALL


SELECT distinct bp2.[CardCode], bp2.[CardName], 
bp2.[MailAddres], bp2.[MailZipCod], bp2.[Phone1], bp2.[Phone2], bp2.[Fax], 
bp2.[CntctPrsn], bp2.[MailCity], bp2.[MailCountr],
bp2.[State2],
transfer.DocNum AS 'Last Order Num',transfer.DocTotal AS 'Last Total',
transfer.DocDate AS 'Last Order Date'
FROM                 (SELECT                    CardCode, 
                                         MAX(owtr.DocEntry) AS 'DocEntry'
                     FROM                 OWTR
                     GROUP BY             CardCode) AS latesttrans
LEFT JOIN            OWTR AS transfer ON (transfer.DocEntry = latesttrans.DocEntry)
INNER JOIN           OCRD AS bp2 ON transfer.cardcode = bp2.cardcode
WHERE (bp2.[MailCITY]  liKE '%%[%0]%%' ) 
and bp2.[FROZENFOR] = 'N'
Order by CardCode