select query failing - 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)

select query failing

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-14 : 02:14:50

Hi,
Iam new to this as i have written one select query it got failing.can you help me out in solving this.



SELECT top 10 TRANSMIT_ERROR_ID, SERVICE_PROVIDER_ID, SERVICE_PROVIDER_TYPE, REQUEST_DATA, RESPONSE_DATA, ETED.USER_LOGIN, USER_NAME, ERROR_DETAILS,  
ltrim(rtrim(convert(varchar,TRANSACTION_DATE,105))) + ' ' + rtrim(ltrim(replace(replace(substring(convert(varchar,TRANSACTION_DATE,0),13,7),'AM',' AM'),'PM',' PM'))) AS TRANSACTION_DATE 
FROM EMRTransmitErrorDetails ETED  INNER JOIN USERS USR ON ETED.USER_LOGIN = USR.USER_LOGIN 
WHERE ETED.USER_LOGIN IN ( SELECT DISTINCT USER_LOGIN FROM EMRUSERROLELOCATION WHERE GROUP_ID = 1501 )

AND ETED.SERVICE_PROVIDER_TYPE IN (SELECT DISTINCT PROVIDER_TYPE FROM EMRServiceProvider WHERE SERVICE_PROVIDER_ID =

(SELECT * FROM EMRServiceProvider where provider_type='APPOINTMENT'))

exception is

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-14 : 02:49:03

quote:
....SELECT DISTINCT USER_LOGIN FROM EMRUSERROLELOCATION WHERE GROUP_ID = 1501 )
AND ETED.SERVICE_PROVIDER_TYPE IN (SELECT DISTINCT PROVIDER_TYPE FROM EMRServiceProvider WHERE SERVICE_PROVIDER_ID =
(SELECT * FROM EMRServiceProvider where provider_type='APPOINTMENT'))

Change to

(SELECT DISTINCT PROVIDER_TYPE FROM EMRServiceProvider WHERE provider_type='APPOINTMENT'

Not sure correct or not...

Hope can help...but advise to wait pros with confirmation...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 13:47:45

why use subquery within subquery? cant you use join instead?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources