Solved

Complex OpenQuery in Sql Server 2008 R2 Connection to 11g Oracle Database

Posted on 2011-09-20
4
1,034 Views
Last Modified: 2012-08-14
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,<insert select statement here>')
 
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')
);

Open in new window

0
Comment
Question by:Kellylee22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36569341
Your open query looks correct, assuming the linked server is named MEMBER_PROD.  For the last part, did you put the query inside single quotes and escape all the single quotes inside your query by changing them to double single quotes?

Greg

0
 

Author Comment

by:Kellylee22
ID: 36569505
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"))')

Open in new window

0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
ID: 36569528
It looks like you used double quotes "  not two single quotes ''  Sorry for the confusion.  They look almost identical.  You escape a single quote with two single quotes.

Greg

0
 

Author Comment

by:Kellylee22
ID: 36569940
Hi Greg,

Escaping single quotes worked perfectly!  Thank you. :)

Kellylee
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question