case statement problem

Hi all

I am having a small problem with the case statement,
I have two table, a status table and users table ( i have scripted them below)

create table users
(id int
, user_name char (10) )

insert into users (id, user_name)
values ( 1, 'bob')
insert into users (id, user_name)
values ( 2, 'sue')
insert into users (id, user_name)
values ( 3, 'richard')
insert into users (id, user_name)
values ( 4, 'john')
insert into users (id, user_name)
values ( 5, 'wendy')

create table status
(name char (10)
, status int, sales_manager int, account_manager int)

insert into status (name, status, sales_manager)
values ('test1', 1, 1 )
insert into status (name, status, sales_manager)
values ('test2', 1, 2 )
insert into status (name, status, account_manager)
values ('test3', 2, 3 )
insert into status (name, status, account_manager)
values ('test4', 2, 4 )
insert into status (name, status)
values ('test5', 2 )

What i need to do when i run the below statement it gives me a list of the names
and the managers, if there is a null value returned i want it to display
'No manager assigned' or something like that

select s.name
, 'manager' = case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

thanks

Like this, use COALESCE or ISNULL

select status,s.name
, 'manager' = case
when status = 1 then coalesce(u1.user_name,'No manager assigned')
when status = 2 then coalesce(u2.user_name,'No manager assigned')
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can do below:

select s.name
, coalesce(case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end, 'No manager assigned') as manager
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Also, please don't use the 'column_alias' = expr syntax. This has been deprecated in SQL Server 2005 and will be removed in a future version of SQL Server. See link below for more details:

http://msdn2.microsoft.com/en-us/ms143729(SQL.90).aspx

|||Thanks guys for the answers, sorted!

Case insensitivity problem

I have an application that needs to check users' log on credentials from a
web front end.
The web front end passes the user name and password to a stored procedure
and, if the stored procedure finds someone with those credentials then it
returns the user's ID.
Trouble is that SQLServer has been installed to be case insensitive, so
"password" = "PASSWORD"
Is there anything that I can do in the stored procedure that can make the
select statement case sensitive for this particular query?
Thanks
Griff> Is there anything that I can do in the stored procedure that can make the
> select statement case sensitive for this particular query?
Of course.
http://www.aspfaq.com/2152|||http://vyaskn.tripod.com/case_sensi..._sql_server.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Griff" <Howling@.The.Moon> wrote in message
news:OS5efhMqFHA.1556@.TK2MSFTNGP12.phx.gbl...
> I have an application that needs to check users' log on credentials from a
> web front end.
> The web front end passes the user name and password to a stored procedure
> and, if the stored procedure finds someone with those credentials then it
> returns the user's ID.
> Trouble is that SQLServer has been installed to be case insensitive, so
> "password" = "PASSWORD"
> Is there anything that I can do in the stored procedure that can make the
> select statement case sensitive for this particular query?
> Thanks
> Griff
>|||Try to convert them to a binary and then compare...
Marcel van Eijkel
( www.vaneijkel.com )sql

CASE in WHERE clause?

I would like to select customers from my database depending on some criteria that the users choose - such as area code, company size etc. Since i have about 15 criteria and the users want to be able to choose many criteria for one selection i can't make one procedure for each criteria - i would end up with about a hundred procedures. I want to let the users choose what criteria to search on and have ONE single select statement that return the customers. Something like:

select name, address, city etc
from customer
where if users want to see customers of a certain type then type = @.myInParameterForType
and if users want to see customers from a certain area then area = @.myInParameterForArea

is this possible? sorry if i made you all confused...my english is not perfect! Thanks in advance!Yes, you may use CASE in where|||one easy (but not very performant) way is to build your "where" clause in your application (ie:web form ) and send it to your sp:
------------------------
CREATE PROCEDURE mySP

(
@.WhereClause varchar(4000)
)

AS
Declare @.SQL varchar(6000)

begin

set @.SQL = 'SELECT field1, field2 ... from myTable where ' + @.WhereClause + ' ORDER BY someID'

end

EXEC (@.SQL)

RETURN

GO
------------------------

when you have many optional parameter it is very conveniant

CASE function

Hi,
I have a date column where the application users puposely enter a date way
in the future as part of their business rule. For instance, entering the yea
r
2033 if the given value for this date column is unknown.
I need to programmatically retrieve this date and represent those
out-of-whack dates to show as the current date + 10 days.
I created the following SELECT stmt. using the CASE function:
SELECT ....
CASE post_datetime
WHEN post_datetime > getdate()+365 THEN getdate()+10
...
However, SQL QA returns an error (Incorrect syntax near '>') when I try to
execute this stmt. What am I doing wrong here. Please help. Thanks.
Regards,
- Rob.Rob wrote:
> Hi,
> I have a date column where the application users puposely enter a date way
> in the future as part of their business rule. For instance, entering the y
ear
> 2033 if the given value for this date column is unknown.
> I need to programmatically retrieve this date and represent those
> out-of-whack dates to show as the current date + 10 days.
> I created the following SELECT stmt. using the CASE function:
> SELECT ....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10
> ...
> However, SQL QA returns an error (Incorrect syntax near '>') when I try to
> execute this stmt. What am I doing wrong here. Please help. Thanks.
> Regards,
> - Rob.
Try this instead:
CASE WHEN post_datetime > GETDATE() + 365 THEN GETDATE() + 10 ELSE
post_datetime END|||> SELECT ....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10
There are two general forms of the CASE expression (it is not a function).
You can either say
CASE [expression] WHEN [value] THEN [value] END
or
CASE WHEN [expression][operator][value] THEN [value] END
You combined the two in a way I don't recall ever seeing (and as you have
found out, the syntax is invalid). You need the latter, because you are
testing a more complex expression than simple equality.
Try:
SELECT
CASE WHEN post_datetime > getdate()+365 THEN getdate()+10 ENDsql

Cascading address returns in query

I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserIDint
AddressIDint
Address
AddressIDint
Address1varchar(100)
Address2varchar(100)
Address3varchar(100)
Address4varchar(100)
Citynvarchar(100)
PostalCodevarchar(10)
Countrysmallint
AddressTypeint
CreateDatesmalldatetime
CreateUserIDint
Updatedatesmalldatetime
UpdateUserIDint
ProvinceTypeint
Inactivebit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
Stacy
On Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle

Cascading address returns in query

I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserID int
AddressID int
Address
AddressID int
Address1 varchar(100)
Address2 varchar(100)
Address3 varchar(100)
Address4 varchar(100)
City nvarchar(100)
PostalCode varchar(10)
Country smallint
AddressType int
CreateDate smalldatetime
CreateUserID int
Updatedate smalldatetime
UpdateUserID int
ProvinceType int
Inactive bit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
StacyOn Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle

Cascading address returns in query

I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserID int
AddressID int
Address
AddressID int
Address1 varchar(100)
Address2 varchar(100)
Address3 varchar(100)
Address4 varchar(100)
City nvarchar(100)
PostalCode varchar(10)
Country smallint
AddressType int
CreateDate smalldatetime
CreateUserID int
Updatedate smalldatetime
UpdateUserID int
ProvinceType int
Inactive bit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
StacyOn Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle

Capture user that calls sp_start_job

I have 10 users assigned to SQLAgentOperatorRole. I want to track which of the users calls sp_start_job. I am using Windows authentication. How can I capture the identity of the user that calls sp_start_job?

I want to do something like "INSERT INTO tbJobHistory(JobName, RunDate, User)"

No, unless you run profiler or encapsulate the procedure callo in your own procedure, there is no way to this.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Capture NT User ID

Access front-end, SQL Server 2005 backend.

I have users connected to SQL Server via a Microsoft Access user-
interface.
Connection is via NT login.

I want to log users' activities to the database with their userid.

How can I capture their NT User ID (via VBA in Access)?

Thanks,
BubblesYou could create a view in SQL Server, I guess, like:

CREATE VIEW dbo.NTUsername
AS
SELECT [username] = SUSER_SNAME();

Now VBA should be able to query from that view in SQL Server just like it
would any other table or view...

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"bubbles" <bubbles.one@.hotmail.comwrote in message
news:1175737278.694527.78540@.q75g2000hsh.googlegro ups.com...

Quote:

Originally Posted by

Access front-end, SQL Server 2005 backend.
>
I have users connected to SQL Server via a Microsoft Access user-
interface.
Connection is via NT login.
>
I want to log users' activities to the database with their userid.
>
How can I capture their NT User ID (via VBA in Access)?
>
Thanks,
Bubbles
>