HELP WITH A VIEW - calculated column

I need a view that contains a select statement that reads through all rows in a table, and based on the value in one of the columns, returns an additional column containing either "Manager" or "employee" depending on the values of that column. I'm not sure whenter to use a loop statement , a local variable, etc - -- but the end result must be a datagrid holding all all rows in the table plus the additional "Manager column" Can someone help me?

SELECT CASE col1 WHEN 'M' THEN 'Manager' ELSE 'Employee' END as EmpType
FROM tablename

You can use a CASE statement. Books online has a very good reference for using CASE.|||Thanks - works great!

help with a scalare function

i wrote a scalare function-

select name,id

from tableName

where function1(id) / function2(id)>100

but sometimes function2 returns zero so im getting a divide by zero exception.

how can i solve this problem?

thanks in advanced.

? It depends on what do you want for output if function2 returns 0... Here's one way to handle it (return nothing): select name,id from tableName where case function2(id) when 0 then 0 else function1(id) / function2(id) end > 100 ...Keep in mind that using functions in this way is going to force table scans, thereby creating some serious performance issues. You might want to reevaluate the problem you're solving here. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <ppl1@.discussions.microsoft..com> wrote in message news:ea55a515-a0d6-480b-b89a-92cd54cb6d6b@.discussions.microsoft.com... i wrote a scalare function- select name,id from tableName where function1(id) / function2(id)>100 but sometimes function2 returns zero so im getting a divide by zero exception. how can i solve this problem? thanks in advanced.|||ok thanks for the help|||

You can modify your WHERE clause to:

where function1(id) / nullif(function2(id), 0)>100

help with a query

Hi,
I have this select query:
select name from categories
it returns a lot of rows, but I want it to be in a single row separated by ,
so the result would be
"Games, Music, Gifts"
instead of
Games
Music
Gifts
how can I do that?
thanks,
BrunoIt looks like you're looking to do some pivottable stuff. This isn't
trivial to do using TSQL. You'd be better off doing this either on the
client or using MDX expressions.
-Alan|||Look at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
http://milambda.blogspot.com/

help with a querie

i need to finish to define this function

create function okJetsHelpFunc
(@.idevent INT)
Returns Table
AS
return select j.*
from jet as j
where j.eventid = @.idevent and abs(dbo.eta(j.id))>4.5 and dbo.pt(j.id)>20.0

GO

right now is working perfectly, but i need to put a condition that return all the jets information if they are 3 jets that fullfil the where statement, and return a null table if not
right now i have this, but it doesnt work

create function okJetsHelpFunc
(@.idevent INT)
Returns Table
AS
if (select count(j.id)
from jet as j
where j.eventid = @.idevent and abs(dbo.eta(j.id))>4.5 and dbo.pt(j.id)>20.0)>=3
return select j.*
from jet as j
where j.eventid = @.idevent and abs(dbo.eta(j.id))>4.5 and dbo.pt(j.id)>20.0

Return null

GO
|||

See if this works:

create function okJetsHelpFunc
(@.idevent INT)
Returns Table
as
return
( select j.*
from jet as j
where j.eventid = @.idevent
and abs(dbo.eta(j.id))>4.5
and dbo.pt(j.id) > 20.0
and ( select count(j.id)
from jet as j
where j.eventid = @.idevent
and abs(dbo.eta(j.id)) > 4.5
and dbo.pt(j.id) > 20.0
) >= 3
)

GO