Solved

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

Posted on 2011-09-20
4
990 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
  • 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now