June 11, 2007 at 11:56 am
Can any one pls check my query at the CASE function.
alter
Procedure [dbo].[getlist]
as
begin
select distinct
Case O.empid
when (O.empid in (select e.empidfrom emp e inner join Indicator I on e.empid=I.empid where I.classid=4)) then 'P'
when (O.empid in(select o.empidfrom empo inner join Indicator I on o.empid=I.empid where I.classid=3) )then 'R'
when (O.empid(Select empid from emp where jobid=9) )then 'U'
end as EmployeeId,
O
.empname,
U
.classname
from
emp O
left outer join Lookup T on O.TypeId = T.TypeId
left outer join Indicator I on I.empid= O.empid
left outer join User U on I.UserId= U.UserId
where OtherInd = 'N'
order by empName,o.empid
end
June 11, 2007 at 1:14 pm
First don't use User, it is a reserved word.
DECLARE @User VARCHAR(30)
SELECT @User = User
SELECT @User
________________________
dbo
There are a few syntax errors.
select distinct
Case -----take out the O.empid
when (O.empid in (select e.empidfrom FROM emp e inner join Indicator I on e.empid=I.empid where I.classid=4)) then 'P'
when (O.empid in(select o.empidfrom FROM emp o inner join Indicator I on o.empid=I.empid where I.classid=3) )then 'R'
when (O.empid IN (Select empid from emp where jobid=9) )then 'U'
end as EmployeeId, ----- EmployeeID is the name of the field
O.empname,
U.classname
from emp O
left outer join Lookup T on O.TypeId = T.TypeId
left outer join Indicator I on I.empid= O.empid
left outer join User U on I.UserId= U.UserId
where OtherInd = 'N'
order by empName,o.empid
June 11, 2007 at 1:55 pm
For the same store proc how can I display 'Yes' for column O.empid , if the following query has any rows
SELECT
ih.oppidfrom Indicatorhour ih inner join author a on a.oppid=ih.oppid where empid=O.empid
else 'No'
June 11, 2007 at 2:29 pm
elect distinct
Case WHEN I.classid=4 then 'P'
when I.classid=3 then 'R'
when O.jobid=9 then 'U'
end as EmployeeId,
O.empname,
U.classname
from emp O
left outer join Lookup T on O.TypeId = T.TypeId
left outer join Indicator I on I.empid= O.empid
left outer join [User] U on I.UserId= U.UserId
where OtherInd = 'N'
order by empName,o.empid
If OtherInd belongs to Lookup or Indicator or then you may use INNER JOIN.
It is INNER JOIN anyway.
And if you don't qualify object names with owner names (dbo,, I guess) you may use WITH RECOMPILE.
You SP will be recompiled on every calll anyway.
_____________
Code for TallyGenerator
June 11, 2007 at 4:03 pm
IF EXISTS (SELECT ih.oppid from Indicatorhour ih
inner join author a on
a.oppid=ih.oppid
where empid=O.empid)
SELECT 'YES'
ELSE
SELECT 'NO'
June 11, 2007 at 4:05 pm
Another way
SELECT CASE WHEN ih.oppidfrom IS NOT NULL
THEN 'YES'
ELSE 'NO' END
FROM Indicatorhour ih
LEFT OUTER JOIN author a ON a.oppid = ih.oppid where empid=O.empid
June 12, 2007 at 7:09 am
WHat I need is...
alter Procedure [dbo].[getlist]
as
begin
select distinct
O.empid,
Case O.empid when (SELECT ih.oppidfrom Indicatorhour ih inner join author a on a.oppid=ih.oppid where empid=O.empid) IS NOT NULL then 'YES' else 'NO'
as Status,
O
.empname,
U
.classname
from
emp O
left outer join Lookup T on O.TypeId = T.TypeId
left outer join Indicator I on I.empid= O.empid
left outer join User U on I.UserId= U.UserId
where OtherInd = 'N'
order by empName,o.empid
end
Here O.empid returns more than 1 value.
I know the query is wrong but i have put here wht my requirement is, hoping u to correct the query.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply