Kellylee22
asked on
Complex OpenQuery in Sql Server 2008 R2 Connection to 11g Oracle Database
Hi,
I am trying to build an OpenQuery statement in SQL Server 2008 R2 that connects to a 11g Oracle database, which will require a complex where statement. I keep getting a lot of errors when I use complex where statement.
The start of the OpenQuery is SELECT * FROM OPENQUERY(MEMBER_PROD,<ins ert select statement here>')
Query that i need to use in OpenQuery is below.
I am trying to build an OpenQuery statement in SQL Server 2008 R2 that connects to a 11g Oracle database, which will require a complex where statement. I keep getting a lot of errors when I use complex where statement.
The start of the OpenQuery is SELECT * FROM OPENQUERY(MEMBER_PROD,<ins
Query that i need to use in OpenQuery is below.
SELECT DISTINCT
m.FIRST_NAME,
m.LAST_NAME,
m.MIDDLE_NAME,
m.name_suffix,
m.CUSTOMERSHIP_ID,
TO_CHAR('620'||'240'||m.CUSTOMERSHIP_ID||m.ASSOCIATE_ID) CUSTOMERSHIP_ID_15,
m.ASSOCIATE_ID,
m.CUSTOMER_KY,
to_char(m.customer_id) customer_id,
m.CUSTOMER_EXPIRATION_DT,
m.JOIN_CLUB_DT,
ms.CUSTOMERSHIP_KY,
m.CUSTOMER_TYPE_CD,
m.BILLING_CATEGORY_CD,
m.RENEW_METHOD_CD,
ms.coverage_level_cd,
m.EMAIL,
m.STATUS,
ms.PHONE,
ms.address_line1,
ms.address_line2,
ms.CITY,
ms.STATE,
substr(ms.ZIP,1,5) zip,
ms.delivery_route,
trunc(sysdate) process_date,
NVL(m.send_bill_to,'N') send_bill_to,
r.dues_cost_at,
ROW_NUMBER() OVER (ORDER BY m.CUSTOMERSHIP_ID,m.ASSOCIATE_ID) RowNumber
FROM CZ_CUSTOMERSHIP ms
INNER JOIN CZ_CUSTOMER m
ON ms.CUSTOMERSHIP_KY = m.CUSTOMERSHIP_KY
AND ms.CUSTOMERSHIP_ID = m.CUSTOMERSHIP_ID
AND m.BILLING_CD >= ' '
AND m.CUSTOMER_TYPE_CD IN('P','A','J')
INNER JOIN CZ_Rider r
ON m.CUSTOMER_KY = r.CUSTOMER_KY
AND m.CUSTOMERSHIP_KY = r.CUSTOMERSHIP_KY
and r.RIDER_COMP_CD='BS'
WHERE ms.STATUS IN ('A','P')
AND m.STATUS IN ('A','P')
AND NVL(ms.BAD_ADDRESS_FL,'N') = 'N'
AND NVL(m.DO_NOT_RENEW_FL,'N') = 'N'
AND m.BILLING_CATEGORY_CD NOT IN ('02','04','06','07','09')
AND NVL(m.email_optout_fl,'N') = 'N'
AND NOT EXISTS
(SELECT 'x'
FROM CZ_CUSTOMERSHIP_CODE mc
WHERE m.CUSTOMERSHIP_KY = mc.CUSTOMERSHIP_KY
AND mc.CODE IN ('FLM', 'PNTM', 'YSDM')
)
AND EXISTS
(
select
'Y'
from CZ_ter t, CZ_brc b, CZ_clb c
where b.brc_ky = t.brc_ky
and b.clb_cd = '240'
and b.clb_cd = c.clb_cd
and b.reg_cd in('001','002')
AND substr(t.zip,1,5) = substr(ms.ZIP,1,5)
and substr(t.zip,1,5) not in('09175','96386','96349','01343','01521')
);
ASKER
MEMBER_PROD is correct. I used the following statement below substituting ' for " and I get the following error: OLE DB provider "OraOLEDB.Oracle" for linked server "MEMBER_PROD" returned message "ORA-00904: "J": invalid identifier".
SELECT * FROM OPENQUERY(MEMBER_PROD,
'SELECT DISTINCT
m.FIRST_NAME,
m.LAST_NAME,
m.MIDDLE_NAME,
m.name_suffix,
m.CUSTOMERSHIP_ID,
TO_CHAR("620"||"240"||m.CUSTOMERSHIP_ID||m.ASSOCIATE_ID) CUSTOMERSHIP_ID_15,
m.ASSOCIATE_ID,
m.CUSTOMER_KY,
to_char(m.customer_id) customer_id,
m.CUSTOMER_EXPIRATION_DT,
m.JOIN_CLUB_DT,
ms.CUSTOMERSHIP_KY,
m.CUSTOMER_TYPE_CD,
m.BILLING_CATEGORY_CD,
m.RENEW_METHOD_CD,
ms.coverage_level_cd,
m.EMAIL,
m.STATUS,
ms.PHONE,
ms.address_line1,
ms.address_line2,
ms.CITY,
ms.STATE,
substr(ms.ZIP,1,5) zip,
ms.delivery_route,
trunc(sysdate) process_date,
NVL(m.send_bill_to,"N") send_bill_to,
r.dues_cost_at,
ROW_NUMBER() OVER (ORDER BY m.CUSTOMERSHIP_ID,m.ASSOCIATE_ID) RowNumber
FROM CZ_CUSTOMERSHIP ms
INNER JOIN CZ_CUSTOMER m
ON ms.CUSTOMERSHIP_KY = m.CUSTOMERSHIP_KY
AND ms.CUSTOMERSHIP_ID = m.CUSTOMERSHIP_ID
AND m.BILLING_CD >= " "
AND m.CUSTOMER_TYPE_CD IN("P","A","J")
INNER JOIN CZ_Rider r
ON m.CUSTOMER_KY = r.CUSTOMER_KY
AND m.CUSTOMERSHIP_KY = r.CUSTOMERSHIP_KY
and r.RIDER_COMP_CD="BS"
WHERE ms.STATUS IN ("A","P")
AND m.STATUS IN ("A","P")
AND NVL(ms.BAD_ADDRESS_FL,"N") = "N"
AND NVL(m.DO_NOT_RENEW_FL,"N") = "N"
AND m.BILLING_CATEGORY_CD NOT IN ("02","04","06","07","09")
AND NVL(m.email_optout_fl,"N") = "N"
AND NOT EXISTS
(SELECT "x"
FROM CZ_CUSTOMERSHIP_CODE mc
WHERE m.CUSTOMERSHIP_KY = mc.CUSTOMERSHIP_KY
AND mc.CODE IN ("FLM", "PNTM", "YSDM")
)
AND EXISTS
(
select
"Y"
from CZ_ter t, CZ_brc b, CZ_clb c
where b.brc_ky = t.brc_ky
and b.clb_cd = "240"
and b.clb_cd = c.clb_cd
and b.reg_cd in("001","002")
AND substr(t.zip,1,5) = substr(ms.ZIP,1,5)
and substr(t.zip,1,5) not in("09175","96386","96349","01343","01521"))')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Greg,
Escaping single quotes worked perfectly! Thank you. :)
Kellylee
Escaping single quotes worked perfectly! Thank you. :)
Kellylee
Greg