Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-20
4
Medium Priority
?
1,074 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

916 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