August 10, 2015 at 7:39 am
Hi,
I need to enable trace flag
OPTION(QUERYTRACEON 9481)
in one of my views I am having trouble finding where to put it in my existing statement:
USE [pec_prod]
GO
/****** Object: View [dbo].[PEC_Claim_Export_All] Script Date: 8/10/2015 9:18:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[PEC_Claim_Export_All]
AS
--DBCC TRACEON( 9481 )
SELECT dbo.employergroup.employergroup_id, dbo.employergroup.employergroup_ud, c.claim_ud, cp.claim_procedure_id, elig.eligibility_ud, c.member_first_name,
c.member_last_name, gp.group_contract_ud, gp.group_contract_nm, c.provider_first_name, c.provider_last_name, v.vendor_ud, cp.npi, cp.from_service_date,
cp.procedurecode_ud, cp.units, cp.rvus, cp.modifier_1, cp.modifier_2, cp.modifier_3, cp.modifier_4,
MAX(CASE WHEN cd.[sequence] = 1 THEN cd.diagnosiscode_ud END) AS diag_1, MAX(CASE WHEN cd.[sequence] = 2 THEN cd.diagnosiscode_ud END) AS diag_2,
MAX(CASE WHEN cd.[sequence] = 3 THEN cd.diagnosiscode_ud END) AS diag_3, MAX(CASE WHEN cd.[sequence] = 4 THEN cd.diagnosiscode_ud END) AS diag_4,
cp.charges, cp.write_off, cp.copay_amount, cp.net_amount, c.date_created, cp.claim_procedure_status_id, dbo.claim_procedure_status.claim_procedure_status_ud,
cp.amount_paid, cp.check_number, cp.check_date, cp.expected_payment_amount, c.referral_id, dbo.referral.referral_ud, dbo.referral_category.referral_category_ud,
cp.approve_for_accounting_date, dbo.claim_procedure_status.status_type, c.received_date, c.os_user_name, dbo.contract.contract_ud, dbo.contract.contract_nm,
cp.payment_penalty, c.provider_id, dbo.referral_category.referral_category_id, c.invoice_number, dbo.member.dob, c.fed_tax_id, dbo.provider.provider_ud,
elig.eligibility_id, c.member_id, c.benefitplan_id, cp.benefit_contract_id, cp.place_of_service_id, cp.type_of_service_id, cp.tpa_check_number, cp.tpa_check_date,
cp.adjudication_date, c.claim_status_id, cp.contract_id, c.claim_id, cp.ffs_equivalent_amount, v.vendor_id, cp.coinsurance_amount, cp.cob, cp.contract_amount,
dbo.place_of_service.place_of_service_ud, c.network_id, dbo.referral.referral_urgency_id, dbo.claim_procedure_2.pcp_id,
dbo.claim_procedure_2.network_id AS network_id_claim, dbo.network.network_ud AS network_ud_claim, acc_cyl.accounting_cycle_ud, acc_cyl.accounting_cycle_id,
acc_cyl.accounting_cycle_nm, dbo.adj_run.adj_run_ud, dbo.place_of_service.place_of_service_nm, c.first_drop_date, c.last_drop_date,
dbo.specialty.specialty_ud AS taxonomy_code_rendering_provider, cp.deductible, cp.ineligible_amount, cp.clean_date, v.tax_id, v.vendor_nm,
v.address_1 AS vendor_address_1, v.address_2 AS vendor_address_2, v.city AS vendor_city, v.state AS vendor_state, v.zip AS vendor_zip,
c.practice_office_id
FROM dbo.referral_category RIGHT OUTER JOIN
dbo.referral RIGHT OUTER JOIN
dbo.provider_specialty LEFT OUTER JOIN
dbo.specialty ON dbo.provider_specialty.specialty_id = dbo.specialty.specialty_id RIGHT OUTER JOIN
dbo.provider ON dbo.provider_specialty.provider_id = dbo.provider.provider_id RIGHT OUTER JOIN
dbo.accounting_cycle AS acc_cyl RIGHT OUTER JOIN
dbo.adj_run ON acc_cyl.accounting_cycle_id = dbo.adj_run.accounting_cycle_id RIGHT OUTER JOIN
dbo.group_contract AS gp INNER JOIN
dbo.eligibility AS elig ON gp.group_contract_id = elig.group_contract_id LEFT OUTER JOIN
dbo.employergroup ON elig.employergroup_id = dbo.employergroup.employergroup_id RIGHT OUTER JOIN
dbo.claim AS c INNER JOIN
dbo.claim_procedure AS cp ON c.claim_id = cp.claim_id INNER JOIN
dbo.claim_diagnosis AS cd ON c.claim_id = cd.claim_id LEFT OUTER JOIN
dbo.claim_procedure_2 LEFT OUTER JOIN
dbo.network ON dbo.claim_procedure_2.network_id = dbo.network.network_id ON cp.claim_procedure_id = dbo.claim_procedure_2.claim_procedure_id ON
elig.eligibility_id = c.eligibility_id ON dbo.adj_run.adj_run_id = cp.adj_run_id ON dbo.provider.provider_id = c.provider_id LEFT OUTER JOIN
dbo.place_of_service ON cp.place_of_service_id = dbo.place_of_service.place_of_service_id ON dbo.referral.referral_id = c.referral_id LEFT OUTER JOIN
dbo.vendor AS v ON cp.vendor_id = v.vendor_id LEFT OUTER JOIN
dbo.contract ON cp.contract_id = dbo.contract.contract_id LEFT OUTER JOIN
dbo.claim_procedure_status ON cp.claim_procedure_status_id = dbo.claim_procedure_status.claim_procedure_status_id ON
dbo.referral_category.referral_category_id = dbo.referral.referral_category_id LEFT OUTER JOIN
dbo.member ON elig.member_id = dbo.member.member_id
--OPTION(QUERYTRACEON 9481)
WHERE (dbo.provider_specialty.primary_specialty = 1)
--Testing Cardinality--
OPTION(QUERYTRACEON 9481)
GROUP BY cp.claim_procedure_id, cp.procedurecode_ud, v.vendor_ud, elig.eligibility_ud, c.claim_ud, c.member_first_name, c.member_last_name, gp.group_contract_ud,
gp.group_contract_nm, c.provider_first_name, c.provider_last_name, cp.npi, cp.units, cp.modifier_1, cp.modifier_2, cp.modifier_3, cp.modifier_4, cp.charges,
cp.write_off, cp.copay_amount, cp.net_amount, dbo.employergroup.employergroup_ud, dbo.employergroup.employergroup_id, c.date_created, cp.amount_paid,
cp.check_number, cp.check_date, cp.claim_procedure_status_id, cp.expected_payment_amount, dbo.claim_procedure_status.claim_procedure_status_ud,
c.referral_id, dbo.referral.referral_ud, dbo.referral_category.referral_category_ud, cp.approve_for_accounting_date, dbo.claim_procedure_status.status_type,
c.received_date, c.os_user_name, dbo.contract.contract_ud, dbo.contract.contract_nm, cp.payment_penalty, cp.from_service_date, c.provider_id,
dbo.referral_category.referral_category_id, c.invoice_number, dbo.member.dob, c.fed_tax_id, dbo.provider.provider_ud, elig.eligibility_id, c.member_id,
c.benefitplan_id, cp.place_of_service_id, cp.type_of_service_id, cp.tpa_check_number, cp.tpa_check_date, cp.adjudication_date, c.claim_status_id, cp.contract_id,
c.claim_id, cp.ffs_equivalent_amount, v.vendor_id, cp.coinsurance_amount, cp.cob, cp.contract_amount, dbo.place_of_service.place_of_service_ud, c.network_id,
dbo.referral.referral_urgency_id, dbo.claim_procedure_2.pcp_id, dbo.claim_procedure_2.network_id, acc_cyl.accounting_cycle_ud, acc_cyl.accounting_cycle_id,
acc_cyl.accounting_cycle_nm, dbo.adj_run.adj_run_ud, dbo.place_of_service.place_of_service_nm, cp.rvus, c.first_drop_date, c.last_drop_date,
dbo.specialty.specialty_ud, cp.benefit_contract_id, cp.deductible, cp.ineligible_amount, cp.clean_date, v.tax_id, v.vendor_nm, v.address_1, v.address_2, v.city,
v.state, v.zip, c.practice_office_id, dbo.network.network_ud
GO
Msg 156, Level 15, State 1, Procedure PEC_Claim_Export_All, Line 56
Incorrect syntax near the keyword 'OPTION'.
August 10, 2015 at 8:34 am
The OPTION has to be the last clause in the query so must be after the GROUP BY.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2015 at 9:32 am
I added it to the end and I get
Incorrect syntax near the keyword 'OPTION'.
WHERE (dbo.provider_specialty.primary_specialty = 1)
GROUP BY cp.claim_procedure_id, cp.procedurecode_ud, v.vendor_ud, elig.eligibility_ud, c.claim_ud, c.member_first_name, c.member_last_name, gp.group_contract_ud,
gp.group_contract_nm, c.provider_first_name, c.provider_last_name, cp.npi, cp.units, cp.modifier_1, cp.modifier_2, cp.modifier_3, cp.modifier_4, cp.charges,
cp.write_off, cp.copay_amount, cp.net_amount, dbo.employergroup.employergroup_ud, dbo.employergroup.employergroup_id, c.date_created, cp.amount_paid,
cp.check_number, cp.check_date, cp.claim_procedure_status_id, cp.expected_payment_amount, dbo.claim_procedure_status.claim_procedure_status_ud,
c.referral_id, dbo.referral.referral_ud, dbo.referral_category.referral_category_ud, cp.approve_for_accounting_date, dbo.claim_procedure_status.status_type,
c.received_date, c.os_user_name, dbo.contract.contract_ud, dbo.contract.contract_nm, cp.payment_penalty, cp.from_service_date, c.provider_id,
dbo.referral_category.referral_category_id, c.invoice_number, dbo.member.dob, c.fed_tax_id, dbo.provider.provider_ud, elig.eligibility_id, c.member_id,
c.benefitplan_id, cp.place_of_service_id, cp.type_of_service_id, cp.tpa_check_number, cp.tpa_check_date, cp.adjudication_date, c.claim_status_id, cp.contract_id,
c.claim_id, cp.ffs_equivalent_amount, v.vendor_id, cp.coinsurance_amount, cp.cob, cp.contract_amount, dbo.place_of_service.place_of_service_ud, c.network_id,
dbo.referral.referral_urgency_id, dbo.claim_procedure_2.pcp_id, dbo.claim_procedure_2.network_id, acc_cyl.accounting_cycle_ud, acc_cyl.accounting_cycle_id,
acc_cyl.accounting_cycle_nm, dbo.adj_run.adj_run_ud, dbo.place_of_service.place_of_service_nm, cp.rvus, c.first_drop_date, c.last_drop_date,
dbo.specialty.specialty_ud, cp.benefit_contract_id, cp.deductible, cp.ineligible_amount, cp.clean_date, v.tax_id, v.vendor_nm, v.address_1, v.address_2, v.city,
v.state, v.zip, c.practice_office_id, dbo.network.network_ud
OPTION(QUERYTRACEON 9481)
GO
August 10, 2015 at 9:40 am
Looks like a query in a view can't have an option clause on it. Probably treated like Order By, restricted to the outermost query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply