Good afternoon, SO.
I'm trying to put my working SSMS query into SSRS, and I'm getting the mentioned error. I've looked over the code and I'm not seeing a multiple call, and so I came to you guys for help. I know it's long, and am more than willing to answer any questioned needed. I'm so lost.
Basically I need an overly convoluted join string (ugh, this program is going to be the bane of my existence). To gain provider data into the temp table I have to create (no physical way of making this without that)
/* These 2 below SELECTs pull the Group provider data only from Acuity which is output to #Temp_Group table */
SELECT DISTINCT provider.provider_identity
,provider_affiliation.db_record_id_parent
,provider_affiliation.db_record_id_child
,provider.external_provider_id
,provider.npi
,provider.provider_name
,provider.provider_type
,provider.tax_id
, db_record_parent.db_record_id
INTO #Temp
FROM provider AS provider
,provider AS provider2
,provider_affiliation
,db_record AS db_record_parent
,db_record AS db_record_child
WHERE (( provider.provider_identity = db_record_parent.key1 )
AND ( db_record_parent.db_record_id = provider_affiliation.db_record_id_parent )
AND ( provider2.provider_identity = db_record_child.key1 )
AND ( db_record_child.db_record_id = provider_affiliation.db_record_id_child )
AND (provider.provider_type = 'G')
AND (db_record_parent.table_code = 'PROV')
AND (provider2.provider_type <> 'G')
AND (db_record_child.table_code = 'PROV'))
AND provider.status = 'ACTV'
SELECT t.provider_identity
,t.db_record_id_parent
,t.db_record_id_child
,t.NPI
,t.provider_name
,t.provider_type
,dbo.address.address_1 AS [Billing Address]
,dbo.address.address_2 AS [Billing Suite]
,dbo.address.city AS [Billing City]
,dbo.state.state_code AS [Billing State]
,SUBSTRING(dbo.address.zip,1,5) AS [Billing ZIP]
,(CASE WHEN CHARINDEX('-', dbo.address.zip ) = 0
THEN ''
ELSE SUBSTRING(dbo.address.zip,(CHARINDEX('-', dbo.address.zip ) + 1),4)
END) AS [Billing ZIP Extension]
,dbo.telephony.telephony_string AS [Billing Phone]
,fax_tele.telephony_string AS [Billing Fax]
INTO #Temp_Group
FROM #Temp AS t
LEFT OUTER JOIN dbo.address_link
ON t.db_record_id = dbo.address_link.db_record_id
AND dbo.address_link.order_no = 1
LEFT OUTER JOIN dbo.address
ON dbo.address_link.address_identity = dbo.address.address_identity
LEFT OUTER JOIN dbo.db_record AS dbr_addr
ON dbo.address.address_identity = dbr_addr.key1
AND dbr_addr.table_code = 'ADDR'
LEFT OUTER JOIN dbo.state
ON dbo.address.state_identity = dbo.state.state_identity
LEFT OUTER JOIN dbo.telephony_link
ON t.db_record_id = dbo.telephony_link.db_record_id
AND dbo.telephony_link.order_no = 1
LEFT OUTER JOIN dbo.telephony
ON dbo.telephony_link.telephony_identity = dbo.telephony.telephony_identity
LEFT OUTER JOIN dbo.reason_reference AS rr_fax
ON rr_fax.reason_code = 'FAX'
AND rr_fax.reason_type = 'TT'
LEFT OUTER JOIN dbo.telephony_link AS fax_tell
ON t.db_record_id = fax_tell.db_record_id
AND fax_tell.telephony_type_identity = rr_fax.reason_reference_identity
AND fax_tell.order_no = (SELECT MIN(order_no) AS Expr1
FROM dbo.telephony_link AS fl
WHERE (db_record_id = t.db_record_id)
AND (telephony_type_identity = rr_fax.reason_reference_identity))
LEFT OUTER JOIN dbo.telephony AS fax_tele
ON fax_tell.telephony_identity = fax_tele.telephony_identity
/* Final Query that combines the provider and group records on the same row. This query is to provide Aetna a provider list of MP(Master Plan) plans */
SELECT DISTINCT dbo.person_name.last_name AS [Last Name], dbo.person_name.first_name AS [First Name], dbo.person_name.middle_name AS [Middle Name], CAST(dbo.person.birthdate AS DATE) AS DOB, dbo.person.sex AS Gender, dbo.person_name.name_suffix AS [Degree 1]
,NULL AS [Degree 2], NULL AS [Degree 3]
,(CASE WHEN tax.description = '' OR tax.description IS NULL
THEN tax.description
WHEN tax.description IN ('General Practice','General Dentist','Dentist')
THEN 'PCP'
ELSE 'Specialist'
END) AS Role
,tax.description AS [Specialty 1]
,(CASE WHEN udf.user_field_data_14 > ' ' AND udf.user_field_data_14 <> 'NA'
THEN 'Y'
ELSE 'N'
END) AS [Board Cert]
,NULL AS [Effective Date]
,REPLACE(CONVERT (VARCHAR(10), udf.user_field_data_15, 101), '/', '') AS [Expiration Date]
,udf.user_field_data_14 AS [Specialty 1 Board Name]
,NULL AS [Specialty 2], NULL AS [Board Cert (Y/N)], NULL AS [Effective Date], NULL AS [Expiration Date], NULL AS [Specialty 2 Board Name]
,NULL AS [Specialty 3], NULL AS [Board Cert (Y/N)], NULL AS [Effective Date], NULL AS [Expiration Date], NULL AS [Specialty 3 Board Name]
,(CASE WHEN rr_l.description <> 'English'
THEN rr_l.description
ELSE NULL
END) AS Language
,NULL AS [Medical School], NULL AS [Grad Year]
,udf.user_field_data_10 AS [Medicare #], NULL AS [Medicare Exp Date]
,REPLACE(CONVERT (VARCHAR(10), udf.user_field_data_30, 101), '/', '') AS [CDS Exp Date]
,udf.user_field_data_13 AS [CDS Number]
,udf.user_field_data_12 AS DEA, REPLACE(CONVERT (VARCHAR(10), udf.user_field_data_18, 101), '/', '') AS [DEA Exp]
,COALESCE(vpl.state,NULL) AS [License State of Issue]
,udf.user_field_data_11 AS [State Lic #], REPLACE(CONVERT (VARCHAR(10), udf.user_field_data_17, 101), '/', '') AS [State Lic Exp]
,udf.user_field_data_09 AS [Medicaid #],NULL AS [Medicaid Expiration Date],NULL AS [Medicaid State]
,NULL AS [Public Email], dbo.telephony.telephony_string AS [NPI Phone Number], dbo.provider.NPI AS [NPI Number (Type 1)]
--,COALESCE(vppa.privilege_provider1_name, NULL) AS [Hospital Affiliation Name 1]
,vppa.privilege_provider1_name AS [Hospital Affiliation Name 1]
,NULL AS [Hospital Affiliation Name 2]
,NULL AS [Hospital Affiliation Name 3]
,dbo.person_id_key.id_key AS SSN, dbo.provider.tax_id, NULL AS [TIN Owner], dbo.provider.provider_name AS [Group Name]
,t2.NPI AS [NPI Number (Type II)]
,'' AS [Does this NPI apply to all Service Locations and Billing Addresses for this Tax id?]
,'' AS [Does this NPI apply to all providers using this Tax id?]
,'' AS [Does this NPI apply to all Service Locations and Billing Addresses for this provider only?]
,'' AS [Does this NPI apply to all to all tax ids for this provider only?]
,dbo.address.address_1 AS [Service Address],dbo.address.address_2 AS [Service Suite],dbo.address.city AS [Service City], dbo.state.state_code AS [Service State], SUBSTRING(dbo.address.zip,1,5) AS [Service ZIP]
,(CASE WHEN CHARINDEX('-', dbo.address.zip ) = 0
THEN ''
ELSE SUBSTRING(dbo.address.zip,(CHARINDEX('-', dbo.address.zip ) + 1),4)
END) AS [Service ZIP Extension]
,dbo.telephony.telephony_string AS [Service Phone], fax_tele.telephony_string AS [Service Fax]
,(udf.user_field_data_01 + '|' + udf.user_field_data_02 + '|' + udf.user_field_data_03 + '|' + udf.user_field_data_04 + '|' + udf.user_field_data_05 + '|' + udf.user_field_data_06 + '|' + udf.user_field_data_07) AS [Office Hours]
,NULL AS [Practice Age Limitations]
,udf2.user_field_data_03 AS [Accepting New Patients]
,udf2.user_field_data_02 AS [Directory Print]
,t2.[Billing Address]
,t2.[Billing Suite]
,t2.[Billing City]
,t2.[Billing State]
,t2.[Billing ZIP]
,t2.[Billing ZIP Extension]
,t2.[Billing Phone]
,t2.[Billing Fax]
,REPLACE(CONVERT (VARCHAR(10), dbo.address_link.start_date, 101), '/', '') AS [Network TIN/Service Location Effective Date]
,REPLACE(CONVERT (VARCHAR(10), dbo.address_link.end_date, 101), '/', '') AS [Network TIN/Service Location Term Date]
,CAST(udf.user_field_data_26 AS DATE) AS [Original Credential Date]
,CAST(udf.user_field_data_27 AS DATE) AS [Recredential Date]
,(CASE WHEN udf.user_field_data_21 = udf.user_field_data_26
THEN ''
ELSE CAST(udf.user_field_data_21 AS DATE)
END) AS [Most Recent Recredentialing Date]
,NULL AS [PEARL-HAMP Provider?]
,company.company_code
,company.description
,dbo.address.additional_loc
,dbr_addr.db_record_id AS db_record_id_addr
,dbo.provider.external_provider_id, dbo.db_record.active_flag,dbo.provider.provider_type
,dbo.provider.provider_identity
,db_record.db_record_id
,db_record.key1
,db_record.linking_id
,db_record.record_template_library_identity
,db_record.calling_db_record_id
,db_record.parent_db_record_id
,t2.db_record_id_parent
,t2.provider_identity
FROM dbo.provider
LEFT OUTER JOIN dbo.db_record
ON dbo.provider.provider_identity = dbo.db_record.key1
AND dbo.db_record.table_code = 'PROV'
-- For Group provider data (Billing Address fields) joining to a regular Provider record
LEFT OUTER JOIN [#Temp_Group] AS t2
ON t2.db_record_id_child = dbo.db_record.db_record_id
-- Next 3 Joins needed to access company.company_code and to pull MP records (Dental Master Plan)
LEFT OUTER JOIN dbo.provider_affiliation praf
ON praf.db_record_id_child = dbo.db_record.db_record_id
LEFT OUTER JOIN dbo.db_record AS dbr_parent
ON praf.db_record_id_parent = dbr_parent.db_record_id
LEFT OUTER JOIN company
ON dbr_parent.key1 = company.company_identity
-- For 'Accepting New Patients' and 'Directory Print' Flags code (Found on the Provider Network tab in Acuity)
LEFT OUTER JOIN v_provider_network_affiliation vpna
ON vpna.key2 = dbo.provider.provider_identity
AND vpna.key1 = '280'
LEFT OUTER JOIN user_field_data AS udf2
ON udf2.table_code='PRNW'
AND udf2.key1=vpna.provider_affiliation_identity
--Service Address fields (Regular provider records level)
LEFT OUTER JOIN dbo.address_link
ON dbo.db_record.db_record_id = dbo.address_link.db_record_id
AND dbo.address_link.order_no = 1
LEFT OUTER JOIN dbo.address
ON dbo.address_link.address_identity = dbo.address.address_identity
LEFT OUTER JOIN dbo.db_record AS dbr_addr
ON dbo.address.address_identity = dbr_addr.key1
AND dbr_addr.table_code = 'ADDR'
LEFT OUTER JOIN dbo.state
ON dbo.address.state_identity = dbo.state.state_identity
LEFT OUTER JOIN dbo.reason_reference AS rr_country
ON dbo.address.country_identity = rr_country.reason_reference_identity
LEFT OUTER JOIN dbo.reason_reference AS rr_region
ON dbo.address.region_identity = rr_region.reason_reference_identity
LEFT OUTER JOIN dbo.telephony_link
ON dbo.db_record.db_record_id = dbo.telephony_link.db_record_id
AND dbo.telephony_link.order_no = 1
LEFT OUTER JOIN dbo.telephony
ON dbo.telephony_link.telephony_identity = dbo.telephony.telephony_identity
LEFT OUTER JOIN dbo.reason_reference AS rr_fax
ON rr_fax.reason_code = 'FAX'
AND rr_fax.reason_type = 'TT'
LEFT OUTER JOIN dbo.telephony_link AS fax_tell
ON dbo.db_record.db_record_id = fax_tell.db_record_id
AND fax_tell.telephony_type_identity = rr_fax.reason_reference_identity
AND fax_tell.order_no = (SELECT MIN(order_no) AS Expr1
FROM dbo.telephony_link AS fl
WHERE (db_record_id = dbo.db_record.db_record_id)
AND (telephony_type_identity = rr_fax.reason_reference_identity))
LEFT OUTER JOIN dbo.telephony AS fax_tele
ON fax_tell.telephony_identity = fax_tele.telephony_identity
LEFT OUTER JOIN dbo.reason_reference AS rr_telephony
ON dbo.telephony_link.telephony_type_identity = rr_telephony.reason_reference_identity
LEFT OUTER JOIN dbo.provider_language
ON dbo.provider_language.provider_identity = dbo.provider.provider_identity
AND dbo.provider_language.order_no = 1
LEFT OUTER JOIN dbo.users AS u_entry
ON dbo.provider.entry_by = u_entry.user_identity
LEFT OUTER JOIN dbo.users AS u_mod
ON dbo.provider.mod_by = u_mod.user_identity
LEFT OUTER JOIN dbo.reason_reference AS rr_l
ON dbo.provider_language.language_identity = rr_l.reason_reference_identity
LEFT OUTER JOIN dbo.person_name
ON dbo.provider.person_identity = dbo.person_name.person_identity
AND dbo.person_name.order_no = 1
LEFT OUTER JOIN dbo.person
ON dbo.person.person_identity = dbo.provider.person_identity
LEFT OUTER JOIN dbo.person_id_key
ON dbo.person_id_key.person_identity = dbo.person.person_identity
AND dbo.person_id_key.order_no = 1
LEFT OUTER JOIN dbo.id_key_type
ON dbo.id_key_type.id_key_type_identity = dbo.person_id_key.id_key_type_identity
LEFT OUTER JOIN dbo.user_field_data udf
ON provider.provider_identity = udf.key1
AND udf.table_code = 'PROV'
LEFT OUTER JOIN dbo.provider_taxonomy prvt
ON prvt.provider_identity = dbo.provider.provider_identity
INNER JOIN dbo.taxonomy tax
ON tax.taxonomy_identity = prvt.taxonomy_identity
-- For Hospital Affil. Name
LEFT OUTER JOIN v_provider_privilege_affiliation AS vppa
ON vppa.provider2_identity = dbo.provider.provider_identity
-- For License State of Issue
LEFT OUTER JOIN v_provider_license AS vpl
ON vpl.provider_identity = dbo.provider.provider_identity
WHERE dbo.db_record.active_flag = 'Y'
AND company.company_code in ('Prestige', '114', '115', '148', '181')
ORDER BY dbo.provider.NPI
As @Jacob said, you have multiple fields with the same name: [Effective Date], [Board Cert (Y/N)]. While SSMS can handle this if you run the query, SSRS must have uniquely named columns so it knows what to put into each field of the report. One thing I started doing is putting the alias first, so instead of
dbr_addr.db_record_id AS db_record_id_addr
I would put
db_record_id_addr = dbr_addr.db_record_id
That nicely lines up all of the column names so they're easy to see.