SQL OpenQuery linked server Oracle DateAdd

Question

I am writing a sql query to pull some data from one of our linked oracle servers.

The only problem with this query is with the two date add rows (with them removed the query runs) but i need their data.

I receive the below error: OLE DB provider "MSDAORA" for linked server "MAGINUS" returned message "ORA-00904: "DATEADD": invalid identifier

Could anyone prescribe a syntax for these?

Thanks in advance

Will

DECLARE  @TSQL VARCHAR(8000)
    ,@CUSTOMER_ACCOUNT VARCHAR(20)

SELECT @TSQL1 = '
SELECT * FROM OPENQUERY(MAGINUS,''
SELECT 
 CM.CUSTOMER_ACCOUNT  AS "CustomerAccount"
,CM.CONTACT_NAME      AS "ContactName"
,CM.MEMBERSHIP_NUMBER AS "MembershipNumber"
,P.LONG_DESCRIPTION_1 AS "ProductDescription"
,DATEADD(SECOND, CM.MEMBERSHIP_START_DATE, "19700101")  AS     "MembershipStartDate"
,DATEADD(SECOND, CM.MEMBERSHIP_EXPIRY_DATE, "19700101") AS "MemberhrshipEndDate"
,SH.ORDER_VALUE AS "Price Paid"
FROM MAGINUS.CUSTOMER_MEMBERSHIP CM 
INNER JOIN MAGINUS.PRODUCT  P 
ON CM.PRODUCT_CODE = P.PRODUCT_CODE
INNER JOIN MAGINUS.SALES_HEADER SH
ON CM.CUSTOMER_ACCOUNT = SH.CUSTOMER_ACCOUNT
AND CM.SALES_DOCUMENT_NUM = SH.SALES_DOCUMENT_NUM
WHERE CM.CUSTOMER_ACCOUNT = ''''' + @CUSTOMER_ACCOUNT + ''''''')'
EXEC (@TSQL1)

BeanFrog BeanFrog · Accepted Answer · 2016-09-06T08:40:06

Dateadd is valid for SQL Server, but the query you send to the Oracle db needs to be valid for oracle. So you need to move the dateadd outside of the openquery, and into the SQL Server bit:

DECLARE  @TSQL VARCHAR(8000)
    ,@CUSTOMER_ACCOUNT VARCHAR(20)

SELECT @TSQL1 = '
SELECT   CustomerAccount
        ,ContactName
        ,MembershipNumber
        ,ProductDescription
        ,DATEADD(SECOND, MembershipStartDate, ''19700101'')  AS MembershipStartDate
        ,DATEADD(SECOND, MemberhrshipEndDate, ''19700101'') AS MemberhrshipEndDate
        ,Price Paid
FROM OPENQUERY(MAGINUS,''
SELECT 
 CM.CUSTOMER_ACCOUNT  AS "CustomerAccount"
,CM.CONTACT_NAME      AS "ContactName"
,CM.MEMBERSHIP_NUMBER AS "MembershipNumber"
,P.LONG_DESCRIPTION_1 AS "ProductDescription"
,CM.MEMBERSHIP_START_DATE AS "MembershipStartDate"
,CM.MEMBERSHIP_EXPIRY_DATE AS "MemberhrshipEndDate"
,SH.ORDER_VALUE AS "Price Paid"
FROM MAGINUS.CUSTOMER_MEMBERSHIP CM 
INNER JOIN MAGINUS.PRODUCT  P 
ON CM.PRODUCT_CODE = P.PRODUCT_CODE
INNER JOIN MAGINUS.SALES_HEADER SH
ON CM.CUSTOMER_ACCOUNT = SH.CUSTOMER_ACCOUNT
AND CM.SALES_DOCUMENT_NUM = SH.SALES_DOCUMENT_NUM
WHERE CM.CUSTOMER_ACCOUNT = ''''' + @CUSTOMER_ACCOUNT + ''''''')'
EXEC (@TSQL1)

SQL OpenQuery linked server Oracle DateAdd

1 Answers