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
Solved

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

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

808 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