"Exists" error message - 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

General SQL Server Forums

New to SQL Server Programming

"Exists" error message

Author

Topic

Alan1018
Starting Member

13 Posts

Posted - 2014-07-10 : 10:26:26

I am trying to run a query against the time & resourceorganization tables that returns selected entries from the time table, the date of the first day of the work week, and the current department. The query returns an "Exists" error. When I add exists or where exists I get ..an error near From. The main query and the sub query each run by themselves I just can;t seem to combine them. Here is what I have:

SELECT Time.ResourceID, DATEADD(dd, -(DATEPART(dw, Time.TimeEntryDate)-1), Time.TimeEntryDate) [WeekStart], Time.TimeEntryDate,
Time.ActivityCode, Time.ProjectCode, Time.TaskName, Time.StandardHours,
Time.StatusCode,

(Select Resource.ResourceID, ResourceOrganization.OrganizationID
From ResourceOrganization Inner Join Resource On Resource.ResourceID = ResourceOrganization.ResourceID
Where ResourceOrganization.EffectiveDate = (Select max(EffectiveDate)
From ResourceOrganization
Where Resource.resourceID = ResourceOrganization.ResourceID))

From Time Inner Join ResourceOrganization On Time.ResourceID = ResourceOrganization.ResourceID

Where Time.TimeEntryDate > 5-30-2014

Thanks,
Alan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-10 : 10:34:38

quote:
Originally posted by Alan1018

I am trying to run a query against the time & resourceorganization tables that returns selected entries from the time table, the date of the first day of the work week, and the current department. The query returns an "Exists" error. When I add exists or where exists I get ..an error near From. The main query and the sub query each run by themselves I just can;t seem to combine them. Here is what I have:

SELECT Time.ResourceID, DATEADD(dd, -(DATEPART(dw, Time.TimeEntryDate)-1), Time.TimeEntryDate) [WeekStart], Time.TimeEntryDate,
Time.ActivityCode, Time.ProjectCode, Time.TaskName, Time.StandardHours,
Time.StatusCode,

(Select Resource.ResourceID, ResourceOrganization.OrganizationID
From ResourceOrganization Inner Join Resource On Resource.ResourceID = ResourceOrganization.ResourceID
Where ResourceOrganization.EffectiveDate = (Select max(EffectiveDate)
From ResourceOrganization
Where Resource.resourceID = ResourceOrganization.ResourceID))

From Time Inner Join ResourceOrganization On Time.ResourceID = ResourceOrganization.ResourceID

Where Time.TimeEntryDate > 5-30-2014

Thanks,
Alan

What is the exact text of the error message?

If you can post some sample data in the tables and show what you are trying to get that would make it easier to figure out what might work best for you.

As it stands, the only thing you are filtering on is the Time.TimeEntryDate. But even that would not work as you are expecting because the date needs to be provided as a string in single quotes - e.g.

Where Time.TimeEntryDate > '5-30-2014'

or ideally in the YYYYMMDD foramt.

Where Time.TimeEntryDate > '20140530'

Alan1018
Starting Member

13 Posts

Posted - 2014-07-10 : 10:58:20

The only reason for the filter is to limit the data returned while building the query.

I am trying to get the resourceID, time entry date, first day of the week (the date part section), Project, Task, Activity Code, status code, Hours, and the current department the resource is assigned to..

When I push this into SSRS I want the user selected parameters to be week, then either a single resource or all resources in one department.

The problem is with the resource organization table. There is no column for current department or end date, just all departments the resource was ever assigned to with the date the assignment first became effective. I have to do a max of effective date query to get the current department, unless there is another way to return the current dept.

Thanks,
Alan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-10 : 11:32:29

You query doesn't have an EXISTS clause in the predicate or any place I can see. So, maybe you should post that.

Alan1018
Starting Member

13 Posts

Posted - 2014-07-10 : 11:51:20

Sorry, IT had the test servers down for updates the last 1/2 hour

First Query:
SELECT Time.ResourceID, DATEADD(dd, -(DATEPART(dw, Time.TimeEntryDate)-1), Time.TimeEntryDate) [WeekStart], Time.TimeEntryDate,
Time.ActivityCode, Time.ProjectCode, Time.TaskName, Time.StandardHours,
Time.StatusCode,
(Select Resource.ResourceID, ResourceOrganization.OrganizationID
From ResourceOrganization Inner Join Resource On Resource.ResourceID = ResourceOrganization.ResourceID
Where ResourceOrganization.EffectiveDate = (Select max(EffectiveDate)
From ResourceOrganization
Where Resource.resourceID = ResourceOrganization.ResourceID))

From Time Inner Join ResourceOrganization On Time.ResourceID = ResourceOrganization.ResourceID

Where Time.TimeEntryDate > '20140530'

Error: Msg 116, Level 16, State 1, Line 10
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

---Add Exists:--------------------------------

SELECT Time.ResourceID, DATEADD(dd, -(DATEPART(dw, Time.TimeEntryDate)-1), Time.TimeEntryDate) [WeekStart], Time.TimeEntryDate,
Time.ActivityCode, Time.ProjectCode, Time.TaskName, Time.StandardHours,
Time.StatusCode,
Where Exists (Select Resource.ResourceID, ResourceOrganization.OrganizationID
From ResourceOrganization Inner Join Resource On Resource.ResourceID = ResourceOrganization.ResourceID
Where ResourceOrganization.EffectiveDate = (Select max(EffectiveDate)
From ResourceOrganization
Where Resource.resourceID = ResourceOrganization.ResourceID))

From Time Inner Join ResourceOrganization On Time.ResourceID = ResourceOrganization.ResourceID

Where Time.TimeEntryDate > '20140530'

Error: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Where'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'From'.

Thanks,
Alan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-10 : 12:12:19

Your query is way out of wack and the EXISTS clause doesn't make any sense as it will always be true. So I suspect that you need to tell us what you are trying to do and provide sample data and expected output. Here are some links to help you post that:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For grins I took a shot and re-writing it to by syntactically correct. But, I doubt it gets the results you want or if it does the query could be re-written much more efficiently.

SELECT 
	Time.ResourceID, 
	DATEADD(dd, -(DATEPART(dw, Time.TimeEntryDate)-1), Time.TimeEntryDate) [WeekStart], 
	Time.TimeEntryDate,
	Time.ActivityCode, 
	Time.ProjectCode, 
	Time.TaskName, 
	Time.StandardHours, 
	Time.StatusCode
FROM 
	Time 
INNER JOIN 
	ResourceOrganization 
	ON Time.ResourceID = ResourceOrganization.ResourceID
WHERE 
	Time.TimeEntryDate > '20140530'
	AND EXISTS 
		(
			SELECT 
				*
			FROM 
				ResourceOrganization 
			INNER JOIN 
				Resource 
				ON Resource.ResourceID = ResourceOrganization.ResourceID
			WHERE 
				ResourceOrganization.EffectiveDate = 
					(
						SELECT MAX(EffectiveDate) 
						FROM ResourceOrganization 
						WHERE Resource.resourceID = ResourceOrganization.ResourceID
					)
		)

Alan1018
Starting Member

13 Posts

Posted - 2014-07-10 : 13:16:08

I get an error message with the last posted query :Msg 102, Level 15, State 1, Line 31
Incorrect syntax near 'ResourceID'.
I also tried putting the Dept. in the where statement with negative results.

Here are the columns from the time table:
TimeID, ProjectCode. TaskUID, ActivityCode, ResourceID, TimeEntryDate StatusCode, StandardHours, TaskName, ApprovedDate, CreateID, CreateDate, LastUpdateID, LastUpdated

Here are the columns from the ResourceOrganization table;

ResourceID, RevisionNum, OrganizationID, EffectiveDate, CreateDate, CreateUserID

This is what I want to return:
ResourceID, Week(calculated from TimeEntryDate),TimeEntryDate, ActivityCode, ProjectCode, TaskName, StandardHours, StatusCode, OrganizationID(Current)

This gives me the Time entries:
SELECT Time.ResourceID, DATEADD(dd, -(DATEPART(dw, Time.TimeEntryDate)-1), Time.TimeEntryDate) [WeekStart], Time.TimeEntryDate,
Time.ActivityCode, Time.ProjectCode, Time.TaskName, Time.StandardHours,
Time.StatusCode

From Time

Where Time.TimeEntryDate > '20140530'

This gives me the current organization:
Select Resource.ResourceID, ResourceOrganization.OrganizationID
From ResourceOrganization Inner Join Resource On Resource.ResourceID = ResourceOrganization.ResourceID
Where ResourceOrganization.EffectiveDate = (Select max(EffectiveDate)
From ResourceOrganization
Where Resource.resourceID = ResourceOrganization.ResourceID)

I need to put the two together so I get the current OrganizationID as a column in the return from the Time table
ResourceID WeekStart TimeEntryDate ActivityCode ProjectCode TaskName StandardHours StatusCode OrganizationID
JJONES 2014-06-01 2014-06-02 NULL 1000-38414 PUG 5.00 N Dept25
JJONES 2014-06-08 2014-06-09 0 NULL 1384-002 Data Entry 2.75 N Dept25

Thanks,
Alan

Alan1018
Starting Member

13 Posts

Posted - 2014-07-10 : 13:46:18

OK, I cheated, created a view table with ResourceID and Current Organization so I can just link to a table(view) that only has the current organization. I'm sure this could be accomplished in the query but this was a simple solution to the problem.

Thanks,
Alan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources