?
Solved

Is there any way to run the query more than 8000 character via openquery?

Posted on 2011-09-21
6
Medium Priority
?
932 Views
Last Modified: 2012-05-12
Hi,

I need to build a large query that goes way over 8000 characters using OpenQuery syntax.  Is there another option without building a view?

Sample code I am trying to use that is causing "is too long" error:


SELECT * FROM OPENQUERY(CUSTOMER_PROD,
       'SELECT
        ms.CUSTOMERship_id,
        m.Associate_Id,
        TO_CHAR(''620''||''240''||m.CUSTOMERSHIP_ID||m.ASSOCIATE_ID) mbrshp_id_15,
        to_char(m.CUSTOMER_ky) CUSTOMER_ky,
        to_char(m.CUSTOMER_ID) customer_id,
        ms.CUSTOMERship_ky,
        m.salutation,
        m.first_name,
        m.middle_name,
        m.last_name,
        m.name_suffix,  
        nvl(ms.bad_address_fl,''N'') bad_address_fl,                                                                                                                           
        ms.address_line1 address1,
        ms.address_line2 address2,
        ms.attention_line,
        ms.city,
        ms.state,
        ms.country,
        substr(ms.zip,1,5) zip,
        ms.delivery_route zip4,
        to_char(m.join_aaa_dt,''YYYYMMDD'') join_aaa_dt,
        to_char(m.join_club_dt,''YYYYMMDD'') join_club_dt,
        to_char(m.CUSTOMER_expiration_dt,''YYYYMMDD'') CUSTOMER_expiration_dt,
        m.status CUSTOMER_status,
        to_char(m.status_dt,''YYYYMMDD'') CUSTOMER_status_dt,
        ms.phone,
        m.Previous_Club_Cd,
        m.previous_CUSTOMERship_id, 
        to_char(m.birth_dt,''YYYYMMDD'') birth_dt,
        decode(nvl(ms.oot_fl,''N''),''T'',''Y'',''N'') eom_xfer_oot_fl,
        m.CUSTOMER_type_cd,
        ms.coverage_level_cd,
        (select distinct max(to_char(rd.REINSTATE_DT,''YYYYMMDD''))
          from mz_CUSTOMER_lapse rd
          where rd.CUSTOMER_ky = m.CUSTOMER_ky
          and rd.RIDER_KY is NULL
          and rd.REINSTATE_DT is not null) max_reinstate_dt,
        m.billing_cd,
        m.billing_category_cd,
        NVL(m.DO_NOT_RENEW_FL,''N'') do_not_renew_fl,
        m.solicitation_cd,
        m.source_of_sale,
        m.commission_cd,
        decode(nvl(m.bad_email_fl,''N''),''Y'',null,m.email) email,
        m.renew_method_cd,
        ar.CC_TYPE_CD auto_renew_cc_type_cd,
        (select distinct cd.code_desc from CX_CODES cd
         where cd.code =  ar.cc_type_cd 
         and cd.code_type = ''CRTCRD'') auto_renew_cc_desc,
        to_char(ar.cc_reject_dt,''YYYYMMDD'') auto_renew_cc_reject_dt,
        m.lead_sequence_nr,
        b.BRANCH_CD,
        b.region_cd,
        m.ORIGINAL_AGENT_ID,
        r.DUES_COST_AT dues CUSTOMER_dues,
        nvl(ms.roll_on_fl,''N'') plus_roll_on_fl,
        nvl(ms.safety_fund_applied_fl,''N'') safety_fund_applied_fl,
        NVL(m.send_bill_to,''P'') send_bill_to,
        nvl(ms.split_fl,''N'') split_CUSTOMERship_fl,
        to_char(ms.transfer_dt,''YYYYMMDD'') CUSTOMERship_transfer_dt,
        ms.transfer_reason_cd,
        to_char(m.future_cancel_dt,''YYYYMMDD'') CUSTOMER_future_cancel_dt,
        to_char(ms.ADM_D2000_UPDATE_DT,''YYYYMMDD'') D2000_update_dt,
        decode(ms.coverage_level_cd,''PL'',
       (select distinct to_char(r1.effective_dt,''YYYYMMDD'')
                  from mz_rider r1
                  where r1.CUSTOMER_ky = m.CUSTOMER_ky
                  and r1.rider_comp_cd = ''PL''),null) plus_effective_date,
        decode(ms.coverage_level_cd,''PM'',
        (select distinct to_char(r3.effective_dt,''YYYYMMDD'')
                  from mz_rider r3
                  where r3.CUSTOMER_ky = m.CUSTOMER_ky
                  and r3.rider_comp_cd = ''PM''),null) premier_effective_date,

        decode((select DISTINCT ''Y'' from mz_CUSTOMERship_code mc1 
        where mc1.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and mc1.code = ''DSP''),''Y'',''Y'',''N'') do_not_send_pub_fl,
        decode((select DISTINCT ''Y'' from mz_CUSTOMERship_code mc1 
        where mc1.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and mc1.code = ''DNE''),''Y'',''Y'',''N'') do_not_email_fl,
        decode((select DISTINCT ''Y'' from mz_CUSTOMERship_code mc1 
        where mc1.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and mc1.code IN (''DNS'', ''DNTM'', ''DNDM'')),''Y'',''Y'', NVL(m.email_optout_fl,''N'')) do_not_solicit_fl,
         decode((select DISTINCT ''Y'' from mz_CUSTOMERship_code mc1 
        where mc1.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and mc1.code = ''FL''),''Y'',''Y'',''N'') fleet_fl,
        decode((select distinct ''Y'' from  mz_bill_summary b
        inner join mz_bill_detail b2 on b2.bill_summary_ky = b.bill_summary_ky
        where b.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and   b2.CUSTOMER_ky = m.CUSTOMER_ky
        and b.bill_type = ''R''
        and b.notice_nr = 1
        and b.mail_dt is not null
        and b.expiration_dt = (select max(b3.expiration_dt) from mz_bill_summary b3
                       where b3.bill_summary_ky = b.bill_summary_ky
                       and b3.expiration_dt >= (sysdate - 90))),''Y'',''Y'',''N'') renewal_bill_1_fl,
        (select distinct max(b.mail_dt) from  mz_bill_summary b
        inner join mz_bill_detail b2 on b2.bill_summary_ky = b.bill_summary_ky
        where b.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and   b2.CUSTOMER_ky = m.CUSTOMER_ky
        and b.bill_type = ''R''
        and b.notice_nr = 1
        and b.mail_dt is not null 
        and b.expiration_dt = (select max(b3.expiration_dt) from mz_bill_summary b3
                       where b3.bill_summary_ky = b.bill_summary_ky
                       and b3.expiration_dt >= (sysdate - 90))) renewal_bill_1_mail_dt,
        decode((select distinct ''Y'' from  mz_bill_summary b
        inner join mz_bill_detail b2 on b2.bill_summary_ky = b.bill_summary_ky
        where b.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and   b2.CUSTOMER_ky = m.CUSTOMER_ky
        and b.bill_type = ''R''
        and b.notice_nr = 2
        and b.mail_dt is not null
        and b.expiration_dt = (select max(b3.expiration_dt) from mz_bill_summary b3
                       where b3.bill_summary_ky = b.bill_summary_ky
                       and b3.expiration_dt >= (sysdate - 90))),''Y'',''Y'',''N'') renewal_bill_2_fl,
                       
       (select distinct max(b.mail_dt) from  mz_bill_summary b
        inner join mz_bill_detail b2 on b2.bill_summary_ky = b.bill_summary_ky
        where b.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and   b2.CUSTOMER_ky = m.CUSTOMER_ky
        and b.bill_type = ''R''
        and b.notice_nr = 2
        and b.mail_dt is not null 
        and b.expiration_dt = (select max(b3.expiration_dt) from mz_bill_summary b3
                       where b3.bill_summary_ky = b.bill_summary_ky
                       and b3.expiration_dt >= (sysdate - 90))) renewal_bill_2_mail_dt,
        decode((select distinct ''Y'' from  mz_bill_summary b
        inner join mz_bill_detail b2 on b2.bill_summary_ky = b.bill_summary_ky
        where b.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and   b2.CUSTOMER_ky = m.CUSTOMER_ky
        and b.bill_type = ''R''
        and b.notice_nr = 3
        and b.mail_dt is not null
        and b.expiration_dt = (select max(b3.expiration_dt) from mz_bill_summary b3
                       where b3.bill_summary_ky = b.bill_summary_ky
                       and b3.expiration_dt >= (sysdate - 90))),''Y'',''Y'',''N'') renewal_bill_3_fl,
        (select distinct max(b.mail_dt) from  mz_bill_summary b
        inner join mz_bill_detail b2 on b2.bill_summary_ky = b.bill_summary_ky
        where b.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and   b2.CUSTOMER_ky = m.CUSTOMER_ky
        and b.bill_type = ''R''
        and b.notice_nr = 3
        and b.mail_dt is not null 
        and b.expiration_dt = (select max(b3.expiration_dt) from mz_bill_summary b3
                       where b3.bill_summary_ky = b.bill_summary_ky
                       and b3.expiration_dt >= (sysdate - 90))) renewal_bill_3_mail_dt,
        decode((select distinct ''Y'' from  mz_bill_summary b
        inner join mz_bill_detail b2 on b2.bill_summary_ky = b.bill_summary_ky
        where b.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and   b2.CUSTOMER_ky = m.CUSTOMER_ky
        and b.bill_type = ''R''
        and b.notice_nr = 4
        and b.mail_dt is not null
        and b.expiration_dt = (select max(b3.expiration_dt) from mz_bill_summary b3
                       where b3.bill_summary_ky = b.bill_summary_ky
                       and b3.expiration_dt >= (sysdate - 90))),''Y'',''Y'',''N'') renewal_bill_4_fl,
        (select distinct max(b.mail_dt) from  mz_bill_summary b
        inner join mz_bill_detail b2 on b2.bill_summary_ky = b.bill_summary_ky
        where b.CUSTOMERship_ky = ms.CUSTOMERship_ky
        and   b2.CUSTOMER_ky = m.CUSTOMER_ky
        and b.bill_type = ''R''
        and b.notice_nr = 4
        and b.mail_dt is not null 
        and b.expiration_dt = (select max(b3.expiration_dt) from mz_bill_summary b3
                       where b3.bill_summary_ky = b.bill_summary_ky
                       and b3.expiration_dt >= (sysdate - 90))) renewal_bill_4_mail_dt,
        TO_CHAR(ma.PCDI_ACTIVATION_DATE,''YYYYMMDD'')  PCDI_activation_dt,
        TO_CHAR(ma.PCDI_DEACTIVATION_DATE,''YYYYMMDD'') PCDI_deactivation_dt
     FROM mz_CUSTOMERship ms
     join mz_branch  b on b.branch_ky = ms.branch_ky
     join mz_CUSTOMER m  on m.CUSTOMERship_ky = ms.CUSTOMERship_ky
     join mz_rider r on m.CUSTOMER_ky = r.CUSTOMER_ky
                       and r.rider_comp_cd = ''BS''
     LEFT OUTER JOIN mz_autorenewal_card AR on ar.autorenewal_card_ky = r.autorenewal_card_ky
     LEFT OUTER JOIN MZ_CUSTOMER_PCDI MA on MA.CUSTOMER_KY = M.CUSTOMER_KY
  WHERE
          ms.status IN (''A'',''P'')
    and   ms.billing_cd >= '' ''
    and   m.status IN (''A'',''P'')
    and   m.BILLING_CATEGORY_CD NOT IN (''02'',''04'',''06'',''07'',''09'')')

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
  • 3
  • 3
6 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36576080
If you have any control over the remote server, you could create a stored procedure or view on the remote server and then just use it through the open query.  That's probably your best bet.  I don't know of any other way around the 8000 character limit.  

Greg

0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36576315
I just had another thought.  You may be able to avoid using open query altogether and use four part naming instead like this.  LINKEDSERVERNAME..SCHEMANAME.TABLENAME.  You would have to substitute the four part name for every table in the remote query.

Greg

0
 

Author Comment

by:Kellylee22
ID: 36576528
I thought of LINKEDSERVERNAME..SCHEMANAME.TABLENAME.  By doing that I can't use Oracle's functions like to_char, Decode....

Kellylee
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 21

Expert Comment

by:JestersGrind
ID: 36576573
That's a good point.  Putting the code in Oracle in some fashion might be the only option then.

Greg
0
 

Accepted Solution

by:
Kellylee22 earned 0 total points
ID: 36584060
I ended up creating functions in Oracle to remove all the subqueries.  That made the query small enough to use in an OpenQuery statement.

Kellylee
0
 

Author Closing Comment

by:Kellylee22
ID: 37166470
I resolved my own question.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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 we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

764 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