Why a SSRS error in a validated Query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Why a SSRS error in a validated Query?

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2010-03-25 : 12:52:57

If I have a working query which returns exactly what I want (SSMS) in a dataset, then, setting it to be a dataset for SSRS, confirming that it also runs fine in the query designer, why do I get errors when clicking OK in the data source query box?

The query apparently contains more than one unnamed or duplicate field name. Please specify unique column aliases.

There are no cases of this

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 13:04:15

Can you show the query?

No, you're never too old to Yak'n'Roll if you're too young to die.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2010-03-25 : 13:09:10

select * from
(SELECT
av_tbl.star_episode_key,
asci_tbl.admin_category_code,
asc_tbl.ubrn,
asci_tbl.referrers_local_id,
asci_tbl.star_AN_number,
asci_tbl.star_umbrella_AN_number,
asc_tbl.referral_type_code,
asci_tbl.referring_gp_code,
asci_tbl.referring_gp_practice_code,
asci_tbl.referral_desc,
asci_tbl.referral_procedure_code,
asci_tbl.referral_received_dt,
asci_tbl.referring_organisation_code,
av_tbl.speciality_code,
ap_tbl.star_internal_no,
ap_tbl.nhs_number PAS_NHS_Number,
--ap_tbl.surname + ' ' + ap_tbl.forename AS 'Name',
apa_tbl.address_postcode,
av_tbl.source_system_id,
av_tbl.facility_code,
av_tbl.treatment_centre_code,
--av_tbl.star_account_number,
av_tbl.contract_code,
av_tbl.star_short_pat_type_code,
av_tbl.vip_indicator,
av_tbl.admit_date_time,
av_tbl.disch_date_time,
dd_tbl.discharge_disposition_description

FROM
[SHCGBCL1DB05\SQL05].PAS_data2.dbo.active_spell_of_care_tbl AS asc_tbl
INNER JOIN [SHCGBCL1DB05\SQL05].PAS_data2.dbo.active_visit_tbl AS av_tbl
ON asc_tbl.star_AN_number = av_tbl.star_AN_number
AND asc_tbl.source_system_id = av_tbl.source_system_id

INNER JOIN [SHCGBCL1DB05\SQL05].PAS_data2.dbo.active_patient_tbl AS ap_tbl
ON ap_tbl.source_system_id = asc_tbl.source_system_id
AND asc_tbl.star_internal_no = ap_tbl.star_internal_no

inner join [SHCGBCL1DB06\SQL06].billing_data_warehouse.dbo.episode_header_tbl eh
on eh.star_internal_no = asc_tbl.star_internal_no
and eh.episode_key = av_tbl.star_episode_key

inner join [SHCGBCL1DB06\SQL06].billing_data_warehouse.dbo.bill_summary_tbl bs
on bs.episode_key = av_tbl.star_episode_key
and eh.star_internal_no = ap_tbl.star_internal_no
and bs.system_id = av_tbl.source_system_id

LEFT OUTER JOIN
[SHCGBCL1DB05\SQL05].PAS_data2.dbo.active_patient_address_tbl AS apa_tbl
ON ap_tbl.star_internal_no = apa_tbl.star_internal_no
AND ap_tbl.source_system_id = apa_tbl.source_system_id

LEFT OUTER JOIN [SHCGBCL1DB05\SQL05].PAS_data2.dbo.discharge_disposition_tbl AS dd_tbl
ON dd_tbl.discharge_disposition_code = av_tbl.discharge_disposition

LEFT OUTER JOIN [SHCGBCL1DB05\SQL05].PAS_data2.dbo.active_spell_of_care_info_tbl AS asci_tbl
ON asci_tbl.source_system_id = asc_tbl.source_system_id
AND asc_tbl.star_AN_number = asci_tbl.star_AN_number

/*where (av_tbl.star_short_pat_type_code in ('NC','ND','NI','NO','NT','NX','NY')*/) PAS

inner join

(SELECT
eh.EPISODE_KEY,
pl.plan_code,
pl.plan_name,
eh.CONSULTANT_CODE,
eh.REFERRING_GP_CODE,
bs.HOSPITAL_FACILITY_CODE,
bs.system_id,
pd.FORENAME + ' ' + pd.SURNAME AS 'Name',
pd.NHS_NUMBER BDW_NHS_Number,
pd.star_internal_no,
bs.PATIENT_TYPE,
eh.ADMISSION_DATE,
eh.DISCHARGE_DATE,
bl.SERVICE_DATE,
eh.PRIMARY_OPCS_CODE,
bl.OPCS_CODE,
eh.hrg_code,
eh.charge_hrg_code,
eh.report_hrg_code,
bl.GROSS_CHARGE_AMOUNT,
bl.DISCOUNT,
bl.NET_OR_REIMBURSMENT_CHARGE_AMOUNT,
net_y,
bl.BILL_LINE_ACTIVE_FLAG,
bl.BILLING_SEQUENCE_NO,
bs.BILL_ACTIVE_FLAG,
bs.BILL_DATE,
bs.BILL_TYPE,
bl.REVENUE_CENTRE_CODE,
bl.CHARGE_DEPARTMENT,
bl.SIM_CODE,
sd.SIM_DESCRIPTION

FROM
[SHCGBCL1DB06\SQL06].billing_data_warehouse.dbo.episode_header_tbl eh

INNER JOIN [SHCGBCL1DB06\SQL06].billing_data_warehouse.dbo.plan_tbl pl
ON eh.INSURER_PLAN_ID = pl.plan_id
AND eh.system_id = pl.system_id

INNER JOIN [SHCGBCL1DB06\SQL06].billing_data_warehouse.dbo.bill_summary_tbl bs
ON eh.EPISODE_KEY = bs.EPISODE_KEY
AND eh.system_id = bs.system_id

INNER JOIN [SHCGBCL1DB06\SQL06].billing_data_warehouse.dbo.patient_demog_tbl pd
ON eh.STAR_INTERNAL_NO = pd.STAR_INTERNAL_NO
AND eh.system_id = pd.system_id

INNER JOIN [SHCGBCL1DB06\SQL06].billing_data_warehouse.dbo.bill_summary_xref_bill_line_tbl x
ON bs.BILL_SUMMARY_ID = x.bill_summary_id
INNER JOIN [SHCGBCL1DB06\SQL06].billing_data_warehouse.dbo.bill_line_tbl bl
ON x.bill_line_id = bl.bill_line_id

INNER JOIN [SHCGBCL1DB06\SQL06].billing_data_warehouse.dbo.sim_details_tbl sd
ON bl.SIM_CODE = sd.SIM_CODE
AND bl.system_id = sd.system_id
AND bl.CHARGE_DEPARTMENT = sd.SIM_CHARGE_DEPT

--WHERE (bs.PATIENT_TYPE IN ('NC', 'ND', 'NI', 'NO', 'NT', 'NX', 'NY')
where datepart(mm, bs.bill_DATE)=03 and datepart(yyyy, bs.bill_date)=2010) BDW
---------------------------------------------------------------------------------------------------------
on PAS.star_episode_key = BDW.EPISODE_KEY
and PAS.star_internal_no = BDW.star_internal_no
and PAS.source_system_id = BDW.system_id

where episode_key = 'V1006100084'
--where star_account_number = 'V1001900035' -- takes longer
--order by 1

--ORDER BY av_tbl.admit_date_time

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 13:41:29

Puh!
Your outer select goes on the derived table named PAS.
Your where clause points to episode_key but in PAS is no episode_key but there is star_episode_key.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources