MSG 116 - 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)

MSG 116

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-03 : 07:59:02

hi iam facing exception like

Msg 116, Level 16, State 1, Procedure ezEMRx_Swift, Line 35
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Procedure ezEMRx_Swift, Line 35
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

my sample block is


Declare task_Cursor CURSOR  FOR 
			SELECT EMR_TRANSACTION_ID,PATIENT_ID,ID_VALUE,CONSULTANT_ID,APPOINTMENT_ID,SRV_ITEM_ID,CREATED_DATE,USER_LOGIN,
			EMR_SERVICE_TYPE_ID,LAB_GROUP_NAME,FASTING ,TEST_ID,NULL AS LAB_PROFILE_ID,PRE_ORDER_ID FROM (SELECT EMR_TRANSACTION_ID,
			PATIENT_ID,ID_VALUE FROM EMRSupplementaryTransactions WHERE TASK_ID=2 AND ID_VALUE IN (SELECT APPOINTMENT_ID FROM 
			EMRAPPOINTMENTDETAILS WHERE VISIT_TYPE_ID =10 AND APPOINTMENT_STATUS_ID=1 AND APPOINTMENT_ID IN 
			(select EMR_TRANSACTION_ID,ID_VALUE,APPOINTMENT_ID,
                   CONSULTANT_ID,VISIT_TYPE_ID,CREATED_DATE,PATIENT_ID from (select EMR_TRANSACTION_ID,ID_VALUE,PATIENT_ID
                   from EMRSupplementaryTransactions where ID_VALUE IN 
                 (select APPOINTMENT_ID from EMRAppointmentDetails where APPOINTMENT_STATUS_ID=1 
                  and appointment_id IN (SELECT WS.APPOINTMENT_ID FROM EMRTPBilledItems EB
INNER JOIN
EMRAPPMTWSTRANSACTIONS WS ON EB.APPOINTMENT_ID=WS.APPOINTMENT_ID WHERE FLAG_ID='008')) )Trans, 
                  (select  APPOINTMENT_STATUS_ID,APPOINTMENT_ID,CONSULTANT_ID,VISIT_TYPE_ID,CREATED_DATE from 
                   EMRAppointmentDetails WHERE  VISIT_TYPE_ID!=10) APPMT Where Trans.ID_VALUE=APPMT.APPOINTMENT_ID 
                   AND APPOINTMENT_STATUS_ID=1 AND VISIT_TYPE_ID!=10))) TRANS, (SELECT VISIT_TYPE_ID,EA.CONSULTANT_ID,EA.APPOINTMENT_ID ,APPOINTMENT_STATUS_ID,
			SRV_ITEM_ID,EA.CREATED_DATE,EA.USER_LOGIN,EMR_SERVICE_TYPE_ID,LAB_GROUP_NAME,FASTING,LTL.TEST_ID,PRE_ORDER_ID FROM EMRAPPOINTMENTDETAILS EA 
			INNER JOIN EMRSERVICEITEMS ON SRV_ITEM_ID=EMR_SERVICE_ID INNER JOIN EMRLABTESTLKUP LTL ON 
			LTL.EMR_SERVICE_ID = SRV_ITEM_ID INNER JOIN EMRPreOrderDetails EP ON EA.APPOINTMENT_ID=EP.APPOINTMENT_ID
			WHERE IS_LAB_APMT=0 AND APPOINTMENT_STATUS_ID=1 AND VISIT_TYPE_ID=10 
			AND LTL.TEST_ID IS NOT NULL) EATB WHERE TRANS.ID_VALUE = EATB.APPOINTMENT_ID AND APPOINTMENT_STATUS_ID=1 
			UNION 
			SELECT EMR_TRANSACTION_ID,PATIENT_ID,ID_VALUE,CONSULTANT_ID,APPOINTMENT_ID,SRV_ITEM_ID, CREATED_DATE,
			USER_LOGIN,EMR_SERVICE_TYPE_ID,NULL AS LAB_GROUP_NAME,NULL AS FASTING,NULL AS TEST_ID ,LAB_PROFILE_ID,NULL AS PRE_ORDER_ID  FROM 
			(SELECT EMR_TRANSACTION_ID,PATIENT_ID,ID_VALUE FROM EMRSupplementaryTransactions WHERE TASK_ID=2 AND ID_VALUE 
			IN (SELECT APPOINTMENT_ID FROM EMRAPPOINTMENTDETAILS WHERE VISIT_TYPE_ID =10 AND APPOINTMENT_STATUS_ID=1 
			AND APPOINTMENT_ID IN (select EMR_TRANSACTION_ID,ID_VALUE,APPOINTMENT_ID,
                   CONSULTANT_ID,VISIT_TYPE_ID,CREATED_DATE,PATIENT_ID from (select EMR_TRANSACTION_ID,ID_VALUE,PATIENT_ID
                   from EMRSupplementaryTransactions where ID_VALUE IN 
                 (select APPOINTMENT_ID from EMRAppointmentDetails where APPOINTMENT_STATUS_ID=1 
                  and appointment_id IN (SELECT WS.APPOINTMENT_ID FROM EMRTPBilledItems EB
INNER JOIN
EMRAPPMTWSTRANSACTIONS WS ON EB.APPOINTMENT_ID=WS.APPOINTMENT_ID WHERE FLAG_ID='008')) )Trans, 
                  (select  APPOINTMENT_STATUS_ID,APPOINTMENT_ID,CONSULTANT_ID,VISIT_TYPE_ID,CREATED_DATE from 
                   EMRAppointmentDetails WHERE  VISIT_TYPE_ID!=10) APPMT Where Trans.ID_VALUE=APPMT.APPOINTMENT_ID 
                   AND APPOINTMENT_STATUS_ID=1 AND VISIT_TYPE_ID!=10))) TRANS, (SELECT VISIT_TYPE_ID,CONSULTANT_ID,APPOINTMENT_ID 
			,APPOINTMENT_STATUS_ID,SRV_ITEM_ID, EA.CREATED_DATE,EA.USER_LOGIN,EMR_SERVICE_TYPE_ID,LTL.LAB_PROFILE_ID 
			FROM EMRAPPOINTMENTDETAILS EA INNER JOIN EMRSERVICEITEMS ON SRV_ITEM_ID=EMR_SERVICE_ID INNER JOIN 
			EMRLABTESTPROFILELKUP LTL ON LTL.EMR_SERVICE_ID = SRV_ITEM_ID WHERE IS_LAB_APMT=0 AND APPOINTMENT_STATUS_ID=1
			 AND VISIT_TYPE_ID=10 ) EATB WHERE TRANS.ID_VALUE = EATB.APPOINTMENT_ID AND APPOINTMENT_STATUS_ID=1

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-03 : 08:27:35

Please check the restrictions of subquery....

The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).

If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.

The ntext, text, and image data types cannot be used in the select list of subqueries.

Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.

The DISTINCT keyword cannot be used with subqueries that include GROUP BY.

The COMPUTE and INTO clauses cannot be specified.

ORDER BY can only be specified when TOP is also specified.

A view created by using a subquery cannot be updated.

The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are the same as those for a standard select list, because a subquery introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.

RD..

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-03 : 08:28:14

hi iam having a query

SELECT TRANSFER_TYPE from EMRTransferedPatients WHERE TRANSFER_TYPE='Temporary' AND PATIENT_ID=
UNION SELECT TH.TRANSFER_TYPE from EMRTransferedPatientsHistory TH,
(SELECT PATIENT_ID,MAX(TRANSFER_DATE) as TRANSFER_DATE FROM EMRTransferedPatientsHistory GROUP BY PATIENT_ID) TEMPTBL
WHERE TEMPTBL.PATIENT_ID=TH.PATIENT_ID AND TEMPTBL.TRANSFER_DATE=TH.TRANSFER_DATE
AND TRANSFER_TYPE='Temporary' AND TH.PATIENT_ID =

i want to use an if condition based on for column like query is successfull then 1 or o.

how can i manage that.

can you help me out.

as i need to pass that in this

INSERT INTO EMRSatelliteUpdates (SAT_UPDATE_ID,TABLE_ID, ID_VALUE, SAT_QUERY,
IS_PREP_STMT_QUERY,PATIENT_ID, USER_LOGIN, GROUP_ID,
SATELLITE_ID, IS_TEMP_PATIENT, MODIFIED_DATE ) VALUES (@GSAT_UPDATE_ID,246,
@APPOINTMENT_ID,@SQL,0,@PATIENT_ID,@USER_LOGIN,1501,1501,1,@CREATED_DATE);

at is_temp_patient with the above query

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-03 : 08:29:12

Error of the Severity Level 15 are generated by the user and are corrigible by the user. You need to remove all but one expression from the SELECT list.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources