Help with Query SQL Server 2008

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)

Help with Query SQL Server 2008

Author

Topic

LO0917
Starting Member

2 Posts

Posted - 2011-11-28 : 15:35:24

I have this long SQL Code that is going to be pulling around 84 columns of Data. I put in two lines and linked a view to bring in some other necessary day and the code just runs and runs not bringing any data back yet it does not error out either. I used only the view table in a separate query to see if there was data in the fields and there is. I took the fields out of my query and the link to the view table and I get back data as well. I've tried linkin on PATIENT_ID,PATIENT_ENCOUNTER_NUMBER,PATIENT_MRN left join inner join and still no luck. Would someone please look at my code and let me know what you think could be the issue? I bolded the new fields I added and the link to the view table the fields are in.

SELECT DISTINCT
DBO.VWR_CASE_INFO.PATIENT_ENCOUNTER_NUMBER,
DBO.VWR_CASE_INFO.PATIENT_MRN,
DBO.VWR_CASE_INFO.CASE_CATEGORY AS CATEGORY,
DBO.VWR_CASE_INFO.IS_FINAL_CASE,
DBO.VWR_CASE_INFO.ACCESSION_NO,
CONVERT(VARCHAR(MAX),DBO.VWR_CASE_HISTORY.FINDING_TEXT)AS ABNORMAL_Y_OR_N,
DBO.VWR_CASE_INFO.ASSIGNED_TO_FIRST_NAME,
DBO.VWR_CASE_INFO.ASSIGNED_TO_LAST_NAME,
DBO.VWR_CASE_INFO.ASSIGNED_TO_MID_NAME,
DBO.VWR_CASE_INFO.ASSIGNED_TO_TITLE,
CONVERT(VARCHAR(MAX),DBO.VWR_CASE_HISTORY.FINDING_TEXT)AS ADDENDUM,
DBO.VWR_CASE_HISTORY.FINDING AS BLOCK_DIAGNOSIS,
DBO.VWR_CASE_HISTORY.SPECIMEN_DESC AS BLOCK_GROSS_DESCRIPTION,
DBO.VWR_CASE_HISTORY.SPECIMEN_ID AS SPECIMEN_BLOCK_CODE,
DBO.VWR_CASE_PROCESS_HISTORY.CASE_STEP_DESCRIPTION,
DBO.VWR_CASE_PROCESS_HISTORY.CASE_STEP_COMPLETION_DATE,
DBO.VWR_CASE_DIAGNOSTIC_RESULTS.CASE_DIAGNOSIS_CLASSIFICATION,
DBO.VWR_CASE_DIAGNOSTIC_RESULTS.CASE_TYPE,
DBO.VWR_CASE_DIAGNOSTIC_RESULTS.PRIMARY_SPEC_DESCRIPTION,
DBO.VWR_CASE_REQUISITION_DATA.requisition_data_field_name AS REQ_DATA,
CONVERT(VARCHAR(MAX),DBO.VWR_CASE_REQUISITION_DATA.requisition_data_field_value)AS REQ_DATA_VALUE,'N/A' AS DATE_OF_LAST_PAP,
'N/A' AS CPT_CODES,
CONVERT(VARCHAR(MAX),DBO.VWR_CASE_DIAGNOSTIC_RESULTS.RESULT_TEXT)AS CytologicDiagnosis,
DBO.VWR_CASE_DIAGNOSTIC_RESULTS.CASE_PRIMARY_DIAGNOSIS,
'SEE_RESULT_TEXT' AS CYTOLOGIC_FINDINGS,
'SEE_CASE_PRIMARY_DIAGNOSIS' AS DIAGNOSIS,
DBO.VWR_CASE_DIAGNOSTIC_RESULTS.PRIMARY_SPEC_DESCRIPTION,
' ' AS HPVDETECTED,
'DIGENE' AS HPV_TEST_MANUFACTURER,
' ' AS HORMONAL_EVALUATION,
' ' AS ICD_9,
' ' AS INFLAMMATION,
'SED MEDICAL LABRATORIES' AS LABCODE,
' ' AS LOCAL_ORDER_CODE,
' ' AS LOCAL_STANDARD_CODE,
'N/A' AS LOINC_CODES,
CONVERT(VARCHAR(MAX),DBO.VWR_CASE_HISTORY.FINDING_TEXT)AS MICROSCOPIC_DESC_1,
DBO.VWR_CASE_HISTORY.FINDING AS MICROSCOPIC_DESC_2,
'N/A' AS NOTE_TEXT_DICTATION,
'N/A' AS NPIN,
DBO.VWR_CASE_PROCESS_HISTORY.PRIMARY_REFMD_FIRST_NAME,
DBO.VWR_CASE_PROCESS_HISTORY.PRIMARY_REFMD_LAST_NAME,
DBO.VWR_CASE_PROCESS_HISTORY.PRIMARY_REFMD_MID_NAME,
DBO.VWR_CASE_PROCESS_HISTORY.PRIMARY_REFMD_GEN_TITLE,
DBO.VWR_CASE_PROCESS_HISTORY.PRIMARY_REFMD_LOCATION_NAME,
DBO.VWR_CASE_PROCESS_HISTORY.PATIENT_ID,
'N/A' AS PRIMARY_REFMD_PHONE,
DBO.VWR_CASE_INFO.PRIMARY_REFMD_LOCATION_NAME AS LOCATION,
' ' AS ORDER_NAME,
' ' AS ORGANISMS,
' ' AS OTHERLOCALRESULTDXCODES,
DBO.VWR_CASE_INFO.ASSIGNED_TO_FIRST_NAME,
DBO.VWR_CASE_INFO.ASSIGNED_TO_LAST_NAME,
DBO.VWR_CASE_INFO.ASSIGNED_TO_MID_NAME,
DBO.VWR_CASE_INFO.ASSIGNED_TO_TITLE,
DBO.VWR_CASE_INFO.PATIENT_FIRST_NAME,
DBO.VWR_CASE_INFO.PATIENT_LAST_NAME,
DBO.VWR_CASE_INFO.PATIENT_MID_NAME,
DBO.VWG_PATIENT_2.EMPLR_NAME AS PATIENT_OCCUPATION,
DBO.VWG_PATIENT_2.HOME_PHONE,
DBO.VWR_CASE_INFO.PATIENT_AGE,
DBO.VWG_PATIENT_2.RACE AS ETHINICITY,
DBO.VWG_PATIENT_2.SEX,
DBO.VWG_PATIENT_2.SSN,
DBO.VWR_CASE_INFO.patient_home_addr_street1,
DBO.VWR_CASE_INFO.patient_home_addr_street2,
DBO.VWR_CASE_INFO.patient_home_addr_state,
DBO.VWR_CASE_INFO.patient_home_addr_state,
DBO.VWR_CASE_INFO.patient_home_addr_country,
DBO.VWR_CASE_INFO.PRIMARY_SPEC_COLLECTION_DATE AS SPEC_COL_DATE,
DBO.VWR_CASE_INFO.PRIMARY_SPEC_DESCRIPTION AS PRIM_DESCRIPTION,
DBO.VWR_CASE_INFO.PRIMARY_SPEC_RECEIVED_DATE AS REC_DATE,
' ' AS REQUISITION_NUMBER,
' ' AS REASON_FOR_AMEND,
' ' AS RESULT_NAME,
' ' AS SNOMED_CODES,
' ' AS SPECIMENADEQUACY,
' ' AS SPECIMENMEDIATYPE,
DBO.VWR_CASE_HISTORY.SPECIMEN_DESC AS SPECIMEN_SOURCE,
' ' AS SPECIMENS_SUBMITTED,
' ' AS TUMOR_STAGING_FORM,
' ' AS UMLCODES,
' ' AS UPIN,
' ' AS VAGINAL_SOURCE

FROM DBO.VWR_CASE_INFO
INNER JOIN DBO.VWG_PATIENT_2
ON DBO.VWG_PATIENT_2.ID=DBO.VWR_CASE_INFO.PATIENT_ID

LEFT JOIN DBO.VWR_CASE_HISTORY
ON DBO.VWR_CASE_HISTORY.ACCESSION_NO=DBO.VWR_CASE_INFO.ACCESSION_NO

INNER JOIN DBO.VWR_CASE_PROCESS_HISTORY
ON DBO.VWR_CASE_HISTORY.ACCESSION_NO=DBO.VWR_CASE_PROCESS_HISTORY.ACCESSION_NO

LEFT JOIN DBO.VWR_CASE_DIAGNOSTIC_RESULTS
ON DBO.VWR_CASE_DIAGNOSTIC_RESULTS.CASE_ID=DBO.VWR_CASE_INFO.CASE_ID
LEFT JOIN DBO.VWR_CASE_REQUISITION_DATA
ON DBO.VWR_CASE_REQUISITION_DATA.PATIENT_ID=DBO.VWR_CASE_PROCESS_HISTORY.PATIENT_ID

WHERE DBO.VWR_CASE_PROCESS_HISTORY.CASE_STEP_COMPLETION_DATE BETWEEN '01-JAN-11' AND '31-JAN-11'

AND DBO.VWR_CASE_INFO.patient_encounter_number IS NOT NULL

AND DBO.VWR_CASE_INFO.CASE_CATEGORY IN ('G','N','S')

AND DBO.VWR_CASE_PROCESS_HISTORY.CASE_STEP_DESCRIPTION = 'FINAL'

AND DBO.VWR_CASE_REQUISITION_DATA.patient_encounter_number IS NOT NULL

THANKS!
LO0917

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 03:20:42

are you sure view has some data that matches values in DBO.VWR_CASE_PROCESS_HISTORY.PATIENT_ID

field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LO0917
Starting Member

2 Posts

Posted - 2011-11-29 : 10:17:09

[quote]Originally posted by visakh16

are you sure view has some data that matches values in DBO.VWR_CASE_PROCESS_HISTORY.PATIENT_ID

field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It should have. I tried joining just those two tables in a separate query I tried joining using 3 different fields and each time it came back saying with this:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DBO.VWR_CASE_REQUISITION_DATA.ACCESSION_NO" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DBO.VWR_CASE_REQUISITION_DATA.PATIENT_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DBO.VWR_CASE_REQUISITION_DATA.CASE_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DBO.VWR_CASE_REQUISITION_DATA.REQUISITION_DATA_FIELD_NAME" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DBO.VWR_CASE_REQUISITION_DATA.REQUISITION_DATA_FIELD_VALUE" could not be bound.

Im going to try another table that is in my SQL to see if I can get it to work off of that

THANKS!
LO0917

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 11:24:33

this error is because you're using a different alias for the view

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources