Incorrect syntax near 'ProjectOverview'.

[aplee]

Hello,
Please help me with my problem, this is the simple select statement I have for target bid date:

ProjectOverview.[Target Event Date - Date] AS [Target Bid Date]

now, I want to ask how can I select the latest target event date and if value is null, put "no date" caption

i tried doing this:

isnull MAX(ProjectOverview.[Target Event Date - Date],'INACTIVE') AS [Target Bid Date]

but it says:
Incorrect syntax near 'ProjectOverview'.
String cannot be converted to date

Thank you very much and I am hoping someone can help me.

[jbisono]

You are missing a parentesis

ISNULL(MAX(ProjectOverview.[Target Event Date - Date]), 'INACTIVE') AS [Target Bid Date]

[aplee]

You are missing a parentesis

ISNULL(MAX(ProjectOverview.[Target Event Date - Date]), 'INACTIVE') AS [Target Bid Date]

unfortunately an error occured:
Msg 8120, Level 16, State 1, Line 1
Column 'EventSummary.Event Information - Event Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[jbisono]

Can you post the full statement?

[aplee]

Can you post the full statement?

Hi jbisono, here you go

SELECT      IsNull(EventSummary.[Event Information - Event Status],'ACTIVE') AS [Event Status],
					  ProjectOverview.[Target Event Date - Fiscal Year] AS [Fiscal Year], 
                      ProjectOverview.[Target Event Date - Fiscal Quarter] AS [Fiscal Quarter],
                      FY$.[Fiscal Month],
                      ProjectOverview.[Project - Project Id] AS [Project ID], 
                      ProjectOverview.[Project - Project Name] AS [Project Name],
                      ProjectOverview.Description, 
                      ISNULL(MAX(ProjectOverview.[Target Event DATE - DATE]), 'INACTIVE') AS [Target Bid DATE]
                      EventSummary.[Bidding Start Date - Date] AS [Sourcing Bid Date], 
                      EventSummary.[Event Close Date - Date] AS [Close Date], 
                      ProjectOverview.[Owner Name] AS Owner, 
                      isnull(Team$.[Commodity Team Name],'UNCLASSIFIED') AS [Commodity Team], 
                      Team$.[Commodity Family], 
                      Team$.[Commodity Code],
                      Team$.[Commodity Description] as [Commodity Name],
                      Team$.[Managed Type], 
                      SavingsForm.[Spend Type], 
                      ProjectOverview.[Project Type], 
                      EventSummary.[Event Information - Template Name] as [Template Type], 
                      SavingsForm.[Region - Region Id (L1)] as Region, 
                      BG$.[Business Group], 
                      SavingsForm.[Organization - Department (L1)] as [Division Name], 
                      ProjectOverview.[Execution Strategy], 
                      ProjectOverview.[Process - Process] as Process, 
                      SavingsForm.[sum(Baseline Spend)] as [Baseline Spend], SavingsForm.[Spend in Native Currency (Currency)], 
                      SavingsForm.[Spend in Native Currency (Amount)]
FROM         Team$ INNER JOIN
                      SavingsForm ON Team$.[Commodity Code] = SavingsForm.[Commodity - Commodity ID] INNER JOIN
                      BG$ ON SavingsForm.[Organization - Department (L1)] = BG$.[Division Name] RIGHT OUTER JOIN
                      ProjectOverview LEFT OUTER JOIN
                      FY$ ON ProjectOverview.[Target Event Date - Fiscal Month] = FY$.[Fiscal Number] LEFT OUTER JOIN
                      EventSummary ON ProjectOverview.[Project - Project Id] = EventSummary.[Event Information - Project Id] ON 
                      SavingsForm.[Project - Project Id] = ProjectOverview.[Project - Project Id]

also what if I only want to get the max date of this code? :

ProjectOverview.[Target Event Date - Date] AS [Target Bid Date],

I used this:

max(ProjectOverview.[Target Event Date - Date]) AS [Target Bid Date],

but an error occured:
Msg 8120, Level 16, State 1, Line 1
Column 'EventSummary.Event Information - Event Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thank you for your response!

[jbisono]

Ok, let me see if with this example i can help you. When you use any aggregate function, in this case I see you are using "MAX", and you are selecting more fields that does not have an aggregate function, you have to group by those fields example.

SELECT MAX(TargetEventDate) AS TargetBidDate, ProjectID
FROM YOURTABLES
GROUP BY ProjectID

In the above example the "TargetEventDate" is in an aggregate function "MAX" but the projectID does not. so you need to Group By those extras fields that does not have an aggregate function...

Got it?

[adam_k]

If your table contains different dates with different status for example and you group by status - or any other field for that matter - then you won't get the "latest version" of your project. You will be getting the maximum date per unique record.

If this is not what you are looking for, I suggest you keep the isnull and remove the max(). Then change your query to include the following join

inner join (select ProjectOverview.[Project - Project Id], max(ProjectOverview.[Target Event DATE - DATE]) as 'max_date' from ProjectOverview
group by [Project - Project Id]) 'max_date' on ProjectOverview.[Project - Project Id]= max_date.[Project - Project Id] and isnull(ProjectOverview.[Target Event DATE - DATE],'INACTIVE') = isnull(max_date.max_date,'INACTIVE')

[aplee]

If your table contains different dates with different status for example and you group by status - or any other field for that matter - then you won't get the "latest version" of your project. You will be getting the maximum date per unique record.

If this is not what you are looking for, I suggest you keep the isnull and remove the max(). Then change your query to include the following join

inner join (select ProjectOverview.[Project - Project Id], max(ProjectOverview.[Target Event DATE - DATE]) as 'max_date' from ProjectOverview
group by [Project - Project Id]) 'max_date' on ProjectOverview.[Project - Project Id]= max_date.[Project - Project Id] and isnull(ProjectOverview.[Target Event DATE - DATE],'INACTIVE') = isnull(max_date.max_date,'INACTIVE')

Hi adam_k,
thanks for your response, what if I remove the isnull function and retain the max date? I tried this code:

MAX(ProjectOverview.[Target Event DATE - DATE]) AS [Target Bid DATE]

but this error appeared:

Column 'EventSummary.Event Information - Event Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What's important is to get the maximum date in ProjectOverview table.

Thank you

[adam_k]

Selecting Max(NULL) will return NULL, so I don't think that will do you any good.

In order to avoid the error, you have to either remove the MAX() or group by all fields but the one with the MAX().
If you remove MAX() you can still get the maximum date in ProjectOverview with the small query I posted for you to add to yours.
It should look like:

SELECT      IsNull(EventSummary.[Event Information - Event Status],'ACTIVE') AS [Event Status],
ProjectOverview.[Target Event Date - Fiscal Year] AS [Fiscal Year], 
ProjectOverview.[Target Event Date - Fiscal Quarter] AS [Fiscal Quarter],
FY$.[Fiscal Month],
ProjectOverview.[Project - Project Id] AS [Project ID], 
ProjectOverview.[Project - Project Name] AS [Project Name],
ProjectOverview.Description, 
isnull(ProjectOverview.[Target Event DATE - DATE], 'INACTIVE') AS [Target Bid DATE],
EventSummary.[Bidding Start Date - Date] AS [Sourcing Bid Date], 
EventSummary.[Event Close Date - Date] AS [Close Date], 
ProjectOverview.[Owner Name] AS Owner, 
isnull(Team$.[Commodity Team Name],'UNCLASSIFIED') AS [Commodity Team], 
Team$.[Commodity Family], 
Team$.[Commodity Code],
Team$.[Commodity Description] as [Commodity Name],
Team$.[Managed Type], 
SavingsForm.[Spend Type], 
ProjectOverview.[Project Type], 
EventSummary.[Event Information - Template Name] as [Template Type], 
SavingsForm.[Region - Region Id (L1)] as Region, 
BG$.[Business Group], 
SavingsForm.[Organization - Department (L1)] as [Division Name], 
ProjectOverview.[Execution Strategy], 
ProjectOverview.[Process - Process] as Process, 
SavingsForm.[sum(Baseline Spend)] as [Baseline Spend], SavingsForm.[Spend in Native Currency (Currency)], 
SavingsForm.[Spend in Native Currency (Amount)]
FROM Team$ INNER JOIN
SavingsForm ON Team$.[Commodity Code] = SavingsForm.[Commodity - Commodity ID]
INNER JOIN BG$ ON SavingsForm.[Organization - Department (L1)] = BG$.[Division Name] 
RIGHT OUTER JOIN ProjectOverview ON SavingsForm.[Project - Project Id] = ProjectOverview.[Project - Project Id]
LEFT OUTER JOIN FY$ ON ProjectOverview.[Target Event Date - Fiscal Month] = FY$.[Fiscal Number] LEFT OUTER JOIN EventSummary ON ProjectOverview.[Project - Project Id] = EventSummary.[Event Information - Project Id]

inner join 
(select ProjectOverview.[Project - Project Id], max(ProjectOverview.[Target Event DATE - DATE]) as 'max_date'
 from ProjectOverview
group by [Project - Project Id]) 'max_date' 
on ProjectOverview.[Project - Project Id]= max_date.[Project - Project Id] and isnull(ProjectOverview.[Target Event DATE - DATE],'INACTIVE') = isnull(max_date.max_date,'INACTIVE')