Solved

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

Posted on 2011-09-20
4
1,022 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Recursion schedule 13 48
SQL Select Query help 1 50
Connect to SQL 2008 r2 server over the Internet 4 84
T-SQL: problem comparing datetime 4 82
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 article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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