Query - 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)

Query

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2011-06-21 : 14:33:55

Hi Group:

I have a little dilema. I have a query that pulls a list of active users.
I used this query to allow the user to pick a person that will work in an specific project.
If @SQLType = 'LDAP'
BEGIN
SELECT displayname as valuex
FROM dbo.PMO_LDAP_data
where displayname not like '_IT%'
order by displayname

For Example.
Date Name Project
1/10/2010 -----John Doe------Implementation Part of SAP

The problem is if the record gets selected again and John Doe is not longer with the company, the application will error out.

I need to add an if case to the query that if the employee is not in the list. Then the record will said "employee not active".

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-22 : 02:20:55

That's not very clear.
Your SELECT is retreiving a column "displayname".
Your Example has a column "Name".
I can see nothing that would tell if there is an inactive employee.

Please show table structure, example data and wanted result EXACTLY.

No, you're never too old to Yak'n'Roll if you're too young to die.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2011-06-22 : 16:18:41

Hi Webfred

The front end application is Project Detail Form

For Example.
Record#------------Date-----------Display Name ----------Project Name
0000001------------1/10/2010 -----John Doe---------------Implementation Part of SAP

In order to populate Display Name field on the form I am using the following Query coming from Active Directory(LDAP):

If @SQLType = 'LDAP'
BEGIN
SELECT displayname as valuex
FROM dbo.PMO_LDAP_data
where displayname not like '_IT%'
order by displayname

-----------------------------
The problem is, that it gives me a list of ACTIVE EMPLOYEES. So imagine, that 6 months ago when Record# 000001 was created Jonh Doe was an active Employee.
Today, Someone wants to update Record# 000001 and because John Doe is not longer with the company, when selected this particular record, it will cause, the application to error out.

If the employee is in the company one you click in a record. Each field in the record will be in edit mode, and the drop down list with the list of Active Employee will be focus on the current selection. But if is not in the database, it will error out.
Hope is more clear now. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-22 : 16:27:23

You can use WHERE NOT EXISTS to check if current employee did or did not exists previously.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources