problem with query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

problem with query

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-06 : 07:16:06

hi my query is like this.i am facing problem with it.


SELECT DISTINCT SPECIMEN_TYPE_NAME,SPECIMEN_TYPE_ID,ELTL.TEST_ID,ELTL.TEST_NAME,ESPD.EMR_TRANSACTION_ID, NULL AS 

LAB_PROFILE_ID,NULL AS LAB_PROFILE_NAME FROM EMRPreOrder EPO 

INNER JOIN EMRPreOrderDetails EPOD ON EPOD.PRE_ORDER_ID=EPO.PRE_ORDER_ID AND EPO.PRE_ORDER_CATEGORY IN (92,93) 

LEFT OUTER JOIN EMRLabTestLkup ELTL ON ELTL.TEST_ID=EPOD.TEST_ID 

LEFT OUTER JOIN EMRLabSpecimenTypeLkup ELST ON ELST.SPECIMEN_TYPE_ID=ELTL.TEST_SPECIMEN  

LEFT OUTER JOIN EMRAppointmentDetailsHistory EADH ON EPOD.APPOINTMENT_ID=EADH.APPOINTMENT_ID AND 

EADH.APPOINTMENT_STATUS_ID=2 

LEFT OUTER JOIN EMRSchdlPreOrderDetails ESPD ON ESPD.PRE_ORDER_DETAILS_ID = EPOD.PRE_ORDER_DETAILS_ID 

INNER JOIN EMRSupplementaryTransactions EST ON EST.ID_VALUE=ESPD.EMR_TRANSACTION_ID AND EST.TASK_ID IN (92,93) 

INNER JOIN EMRTransactions ET ON CAST(ET.ID_VALUE AS VARCHAR)=EST.PATIENT_ID AND ET.ID_VALUE=805126 AND ET.TASK_ID IN 

(93) WHERE ELST.SPECIMEN_TYPE_NAME IS NOT NULL AND EPOD.APPOINTMENT_ID IS NOT NULL 

UNION ALL

SELECT DISTINCT SPECIMEN_TYPE_NAME,SPECIMEN_TYPE_ID,ELTL.TEST_ID,ELTL.TEST_NAME,ESPD.EMR_TRANSACTION_ID, 

ELTP.LAB_PROFILE_ID,ELTP.LAB_PROFILE_NAME FROM EMRPreOrder EPO 

INNER JOIN EMRPreOrderDetails EPOD ON  EPOD.PRE_ORDER_ID=EPO.PRE_ORDER_ID AND EPO.PRE_ORDER_CATEGORY IN (92,93) 

LEFT OUTER JOIN EMRTestProfiles ETP ON ETP.LAB_PROFILE_ID=EPOD.LAB_PROFILE_ID AND ETP.STATUS=1 

LEFT OUTER JOIN EMRLabTestLkup ELTL ON ETP.TEST_ID=ELTL.TEST_ID 

LEFT OUTER JOIN EMRLabSpecimenTypeLkup ELST ON ELST.SPECIMEN_TYPE_ID=ELTL.TEST_SPECIMEN 

LEFT OUTER JOIN EMRAppointmentDetailsHistory EADH ON EPOD.APPOINTMENT_ID=EADH.APPOINTMENT_ID AND 

EADH.APPOINTMENT_STATUS_ID=2  

INNER JOIN EMRLabTestProfileLkup ELTP ON ELTP.LAB_PROFILE_ID=EPOD.LAB_PROFILE_ID 

LEFT OUTER JOIN EMRSchdlPreOrderDetails ESPD ON ESPD.PRE_ORDER_DETAILS_ID = EPOD.PRE_ORDER_DETAILS_ID 

INNER JOIN EMRSupplementaryTransactions EST ON EST.ID_VALUE=ESPD.EMR_TRANSACTION_ID AND EST.TASK_ID IN (92,93) 

INNER JOIN EMRTransactions ET ON CAST(ET.ID_VALUE AS VARCHAR)=EST.PATIENT_ID AND ET.ID_VALUE=805126 AND ET.TASK_ID IN 

(93) WHERE SPECIMEN_TYPE_NAME IS NOT NULL AND EPOD.APPOINTMENT_ID IS NOT NULL 

ORDER BY SPECIMEN_TYPE_ID,ELTP.LAB_PROFILE_ID,ELTL.TEST_ID ASC

exception i am getting is

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ELTP.LAB_PROFILE_ID" could not be bound.
Msg 104, Level 16, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

in the first select query table not contains column LAB_PROFILE_ID

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 07:29:09

In ORDER BY you have only to specify column names which appear in select list.
In select list (and so in your record set) you have to order by without using table alias.

ORDER BY SPECIMEN_TYPE_ID,LAB_PROFILE_ID,TEST_ID ASC

Fred

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 07:32:26

Not generally, but in your case because using UNION.

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-06 : 07:33:24

When iam not giving table aliases some times in few cases i am getting message like
----colkum ambigously defined something.how to overcome this scenario

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 07:42:39

If you have a SELECT (without union) joining tables and a column exists in more than one table you have to use alias to address exactly one of these columns.

In your case above you are using UNION so SQL will build a record set with data from more than one SELECT. In this case the sort happens on this record set (where in bottom line there is no alias) and its columns without any connection to their origin table.

That's the difference.

Fred

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

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-06 : 07:44:52

hi

Because this column is not contained in the SELECT list and the query contains one of the above mentioned operators, the error is raised.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-06 : 07:45:27

THANKS WEBFRED FOR YOUR SUGGESTIONS.CAN YOU GIVE ME THE QUERY WHICH WORKS FINE FOR THE ABOVE.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-06 : 07:49:33

quote:
Originally posted by webfred

In ORDER BY you have only to specify column names which appear in select list.
In select list (and so in your record set) you have to order by without using table alias.

ORDER BY SPECIMEN_TYPE_ID,LAB_PROFILE_ID,TEST_ID ASC

Fred

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

I thought I've already done...

Are there more errors when you try to use it with corrected ORDER BY?

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