Kellylee22
asked on
Is there any way to run the query more than 8000 character via openquery?
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:
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'')')
I just had another thought. You may be able to avoid using open query altogether and use four part naming instead like this. LINKEDSERVERNAME..SCHEMANA ME.TABLENA ME. You would have to substitute the four part name for every table in the remote query.
Greg
Greg
ASKER
I thought of LINKEDSERVERNAME..SCHEMANA ME.TABLENA ME. By doing that I can't use Oracle's functions like to_char, Decode....
Kellylee
Kellylee
That's a good point. Putting the code in Oracle in some fashion might be the only option then.
Greg
Greg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I resolved my own question.
Greg