Click here to Skip to main content
15,897,273 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
select

tbl_login.userid,
tbl_login.name as Employee,

(Case  when Exists
(Select * from tbl_attendance where tbl_attendance.userid=tbl_login.userid and tbl_attendance.firstlogin is not null  and tbl_attendance.date='2011/03/25') then 'Present' else 'Absent' end) as Status,

(select top 1 coalesce(tbl_timesheet.description,null) as Filename,coalesce(LTRIM(Right(Convert(varchar(19),tbl_timesheet.starttime,100),7)),null) as StartTime from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date='2011/03/25' and stoptime is null)

from tbl_login where tbl_login.privilege<>'ADMIN'





This is my query,its showing an error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


Cna anyone help me
Posted

select

tbl_login.userid,
tbl_login.name as Employee,

(Case  when Exists
(Select * from tbl_attendance where tbl_attendance.userid=tbl_login.userid is not null and tbl_attendance.firstlogin is not null  and tbl_attendance.date='2011/03/25') then 'Present' else 'Absent' end) as Status,
(select top 1 coalesce(tbl_timesheet.description,null) as Filename,coalesce(LTRIM(Right(Convert(varchar(19),tbl_timesheet.starttime,100),7)),null) as StartTime from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date='2011/03/25' and stoptime is null)

from tbl_login where tbl_login.privilege<>'ADMIN'



Replace your code with this and it should work.

Try and let me know.
 
Share this answer
 
Comments
Appukuttan Nair 25-Mar-11 2:46am    
thanks for reply but sorry its not working..
Read this:
MSDN: Subquery Rules[^]

For full detail of the error:
Explained with example[^]
 
Share this answer
 
Try
SQL
select  tbl_login.userid,
        tbl_login.name as Employee,
        (
        Case
            when Exists (
                        Select  *
                        from    tbl_attendance
                        where   tbl_attendance.userid=tbl_login.userid
                                and
                                tbl_attendance.firstlogin is not null
                                and
                                tbl_attendance.date='2011/03/25'
                        )
            then 'Present'
            else 'Absent'
        end
        ) as Status,
        (
        select  top 1
                coalesce(tbl_timesheet.description,null)
        from    tbl_timesheet
        where   tbl_timesheet.userid=tbl_login.userid
                and
                date='2011/03/25'
                and
                stoptime is null
        ) as Filename,
        (
        select  top 1
                coalesce(LTRIM(Right(Convert(varchar(19),tbl_timesheet.starttime,100),7)),null)
        from    tbl_timesheet
        where   tbl_timesheet.userid=tbl_login.userid
                and
                date='2011/03/25'
                and
                stoptime is null
        ) as StartTime
from    tbl_login
where   tbl_login.privilege<>'ADMIN'


Note the (select top 1 coalesce(tbl_timesheet.description,null) ... is broken into two.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900