Pls help me with - Syntax error Last Line 52

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

General SQL Server Forums

New to SQL Server Programming

Pls help me with - Syntax error Last Line 52

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-04-09 : 10:45:43

SELECT
usr_login "User ALM EMCID",
employeeID "User PV EMPID",
usr_udf_nt_login "ALM USR LOGIN",
usr_first_name "User FirstName",
usr_last_name "User LastName",
usr_email "User Email",
usr_emp_type "Employee Type",
usr_status "ALM Status",
PeopleView.Active_Term "PV Status",
IsActive "AD Status",
benabled "ACE Status",
usr_start_date "User Start Date",
usr_end_date "User End Date",
usr_udf_manager_emcid "Manager EMCID",
manager_first_name "Manager FirstName",
manager_last_name "Manager LastName",
manager_email "Manager Email",
manager_status "Manager Status",
manager_end_date "Manager End Date"
from
(select
ALM.usr_login,
PV.employeeID,
ALM.usr_udf_nt_login,
ALM.usr_first_name,
ALM.usr_last_name,
ALM.usr_email,
ALM.usr_emp_type,
ALM.usr_status,
PV.Active_Term,
AD.IsActive,
ACE.benabled,
ALM.usr_start_date,
ALM.usr_end_date,
ALM.usr_udf_manager_emcid,
ALM.manager_first_name,
ALM.manager_last_name,
ALM.manager_email,
ALM.manager_status,
ALM.manager_end_date

from dbo.ALM_USERS ALM,
dbo.PeopleView PV,
dbo.corp_emp_ids AD,
dbo.ALM_USERS ACE

where cast(ALM.USR_login as int)= PV.EmployeeID -- PV
and ALM.USR_login = CAST(AD.employeeid AS varchar)---AD
and ALM.USR_login = ACE.chdata ----ACE
AND ALM.USR_login NOT IN ('BTGWC1', 'DMADMIN', 'IFILL', 'TESTUSER', 'WTDEV06', 'XELOPERATOR', 'XELSELFREG', 'XELSYSADM')
AND ALM.USR_STATUS = 'Disabled')

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-04-09 : 10:47:11

I am using SQL Server 2005. Just FYI. Thanks

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 10:48:14

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 10:48:21

I think you have to name the derived table.
Add a "AS f" to the last line.

E 12°55'05.25"
N 56°04'39.16"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-04-09 : 10:49:21

You need to alias your derived table
AND ALM.USR_STATUS = 'Disabled') as MyTable

Jim

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-04-09 : 11:06:25

Error :
The multi-part identifier "PeopleView.Active_Term" could not be bound.

Firstline in the where clause how can i fix this. This is because i have table alias name as pv instead of Peopleview.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 11:26:48

[code]SELECT
usr_login "User ALM EMCID",
employeeID "User PV EMPID",
usr_udf_nt_login "ALM USR LOGIN",
usr_first_name "User FirstName",
usr_last_name "User LastName",
usr_email AS User Email ,
usr_emp_type AS Employee Type,
usr_status AS ALM Status,
PeopleView.Active_Term "PV Status",
IsActive "AD Status",
benabled "ACE Status",
usr_start_date "User Start Date",
usr_end_date "User End Date",
usr_udf_manager_emcid "Manager EMCID",
manager_first_name "Manager FirstName",
manager_last_name "Manager LastName",
manager_email "Manager Email",
manager_status "Manager Status",
manager_end_date "Manager End Date"
from
(select
ALM.usr_login,
PV.employeeID,
ALM.usr_udf_nt_login,
ALM.usr_first_name,
ALM.usr_last_name,
ALM.usr_email,
ALM.usr_emp_type,
ALM.usr_status,
PV.Active_Term,
AD.IsActive,
ACE.benabled,
ALM.usr_start_date,
ALM.usr_end_date,
ALM.usr_udf_manager_emcid,
ALM.manager_first_name,
ALM.manager_last_name,
ALM.manager_email,
ALM.manager_status,
ALM.manager_end_date

from dbo.ALM_USERS ALM,
dbo.PeopleView PV,
dbo.corp_emp_ids AD,
dbo.ALM_USERS ACE

where cast(ALM.USR_login as int)= PV.EmployeeID -- PV
and ALM.USR_login = CAST(AD.employeeid AS varchar)---AD
and ALM.USR_login = ACE.chdata ----ACE
AND ALM.USR_login NOT IN ('BTGWC1', 'DMADMIN', 'IFILL', 'TESTUSER', 'WTDEV06', 'XELOPERATOR', 'XELSELFREG', 'XELSYSADM')
AND ALM.USR_STATUS = 'Disabled')A[/code]
Remove table name PeopleView from column name Active_term in SELECT list. ALso dont put "" for aliases. Just use columnname AS alias in select list

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-04-10 : 16:39:09

Thanks.

Since i am new to this field, I have one more clarification related to the same query.
I have four tables right

dbo.ALM_USERS ALM,
dbo.PeopleView PV,
dbo.corp_emp_ids AD,
dbo.ALM_USERS ACE

I have to pull out status data from all 4 tables.
I want the diabled from ALM and active in PV.

Also, all the disabled & active from AD and ACE (these are 3rd and 4th table)

Based on the id's linked, will it give me what i want or would i have to use an OR instead of an AND. If so, how would i modify this query?

According to my co-worker, if
alm = 1 and disabled
pv = 1 active
ad status is y and
ace status is y it will return
if the same senario with
ad status is y
ace status is n it will not return.
I am not sure if this is true.

Please atleast comment on this. Thanks

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-04-11 : 07:05:42

Please can somebody clarify my doubt? Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources