Been trying for some hours to convert this to a query I can use with OPENQUERY in SQL Server 2014 (to use with Progress OpenEdge 10.2B via ODBC). Can't seem to get the escaping of the quote right. Can anyone offer some assistance? Is there a tool to do it?
(There's a SQL table called @tAPBatches that is used in this, but I omitted it from this code)
DECLARE
@NoDays AS INT = 30
,@Prefix AS VARCHAR(5) = 'M_AP_'
SELECT
@Prefix + LTRIM(CAST(gh.[Batch-Number] AS VARCHAR(20))) AS BatchNo
,gh.[Batch-Number] AS BatchNo8
, aph.[Reference-number] AS InvoiceNo
,aph.[Voucher-Number] AS VoucherNo
,aph.[Amount] AS InvoiceTotal
,gh.[Journal-Number] AS JournalNo
,4 AS FacilityID
,CASE aph.[voucher-type]
WHEN 'DM' THEN 5
ELSE 1
END AS DocType
,apb.[Batch-Desc] AS BatchDesc
,apb.[Posting-Date] AS PostingDate
,apb.[Posting-Period]
,apb.[Posting-Fiscal-Year]
,apb.[Batch-Status]
,apb.[Expected-Count]
,apb.[Expected-Amount]
,apb.[Posted-To-GL-By]
,'Broadview' AS FacilityName
,apb.[Date-Closed] AS BatchDate
,gh.[Posted-by] AS PostUser
,gh.[Posted-Date] AS PostDT
,gh.[Created-Date] AS CreateDT
,gh.[Created-By] AS CreateUser
,aph.[Supplier-Key] AS VendorID
,sn.[Supplier-Name]
,aph.[Invoice-Date] AS InvoiceDate
,-1 AS Total
,-1 AS Discount
,gh.[Posted-by] AS Username
,CASE gt.[Credit-Debit]
WHEN 'CR' THEN LEFT(CAST(gacr.[GL-Acct] AS VARCHAR(20)), 2) + '.' + SUBSTRING(CAST(gacr.[GL-Acct] AS VARCHAR(20)), 3, 6) + '.'
+ RIGHT(CAST(gacr.[GL-Acct] AS VARCHAR(20)), 3)
ELSE NULL
END AS GLCreditAcct
,CASE gt.[Credit-Debit]
WHEN 'DR' THEN LEFT(CAST(gacr.[GL-Acct] AS VARCHAR(20)), 2) + '.' + SUBSTRING(CAST(gacr.[GL-Acct] AS VARCHAR(20)), 3, 6) + '.'
+ RIGHT(CAST(gacr.[GL-Acct] AS VARCHAR(20)), 3)
ELSE NULL
END AS GLDebitAcct
,CASE gt.[Credit-Debit]
WHEN 'CR' THEN gacr.[Report-Label]
ELSE NULL
END AS GLCreditDesc
,CASE gt.[Credit-Debit]
WHEN 'DR' THEN gacr.[Report-Label]
ELSE NULL
END AS GLDebitDesc
,'D' AS [Status]
,aph.[PO-Number] AS PoNo
,aph.[Terms-Code] AS TermsCode
,aph.[Due-Date] AS DueDate
,'' AS Comments
,aph.[Discount-Date] AS DiscountDate
,aph.[Discount-Amount] AS DiscountAmount
,aph.[Discount-Taken] AS DiscountTaken
,aph.[Amount] AS APAmount
,gt.[Amount]
,'BA REGULAR ' AS CheckBookID --ToDO
,0 AS Transferred
,aph.[voucher-type] AS VoucherType
,gt.[Credit-Debit]
,gacr.[Account-type]
,aph.[Freight-Ref-Num]
FROM
[Progress].[GAMS1].pub.[GL-Entry-Header] gh
INNER JOIN [Progress].[GAMS1].pub.[gl-entry-trailer] gt ON gt.[System-ID] = gh.[System-ID] AND gt.[Origin] = gh.[Origin] AND gt.[Journal-Number] = gh.[Journal-Number]
INNER JOIN [Progress].[GAMS1].pub.[apinvhdr] aph ON (gh.[Journal-Number] = aph.[Journal-Number]
OR (gh.[Journal-Num-Reversal-Of] = aph.[Journal-Number] AND aph.[Journal-Number] <> ' ' AND gh.[Journal-Num-Reversal-Of] <> ' '))
AND gh.[system-id] = aph.[system-id-gl]
AND gh.origin = 'inv'
AND gh.[system-id] = 'arcade'
INNER JOIN [Progress].[GAMS1].pub.[APInvoiceBatch] apb ON gh.[Batch-number] = apb.[Batch-number]
AND apb.[system-id] = 'lehigh'
AND apb.[Posted-To-GL] = 1
INNER JOIN [Progress].[GAMS1].pub.[GL-accts] gacr ON gacr.[system-id] = gt.[system-id]
AND gacr.[Gl-Acct-Ptr] = gt.[GL-Acct-Ptr]
INNER JOIN [Progress].[GAMS1].pub.[suppname] sn ON sn.[Supplier-Key] = aph.[Supplier-Key]
AND sn.[system-id] = 'arcade'
WHERE
gh.[Posted-Date] > CAST(DATEADD(DAY, -@NoDays, GETDATE()) AS DATE)
AND case
when CAST(gh."Posting-Period" as int) < 10 then gh."Posting-Year" + '0' + ltrim(gh."Posting-Period")
else gh."Posting-Year" + Ltrim(gh."Posting-Period")
end > '201501'
AND gh.[Batch-number] NOT IN (SELECT
BatchNo COLLATE SQL_Latin1_General_CP1_CI_AS
FROM
@tAPBatches)
TIA
MArk
Here's an example of what's giving me a syntax error. This works, but "M_AP_" is a parameter passed to SP
DECLARE
@NoDays AS INT = 5
,@Prefix AS VARCHAR(5) = 'M_AP_';
DECLARE
@InterestDate AS varchar(20)
SELECT @InterestDate = CAST(CAST(DATEADD(DAY, -@NoDays, GETDATE()) AS DATE) AS VARCHAR(20))
SELECT * FROM OPENQUERY(PROGRESS,
'SELECT TOP 100 ''M_AP_'' + LTRIM(CAST(gh."Batch-Number" AS VARCHAR(20))) AS BatchNo
, gh."Batch-Number"
This works, but when I try to swap in the variable I get Incorrect Syntax near '+'
DECLARE
@NoDays AS INT = 5
,@Prefix AS VARCHAR(5) = 'M_AP_';
DECLARE
@InterestDate AS varchar(20)
SELECT @InterestDate = CAST(CAST(DATEADD(DAY, -@NoDays, GETDATE()) AS DATE) AS VARCHAR(20))
SELECT * FROM OPENQUERY(PROGRESS,
'SELECT TOP 100 '' ' + @Prefix + ' '' + LTRIM(CAST(gh."Batch-Number" AS VARCHAR(20))) AS BatchNo
, gh."Batch-Number"
FROM
"GAMS1".pub."GL-Entry-Header" gh
