Incorrect syntax near 'ProjectOverview'.

Question

0.00/5 (No votes)

See more:

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

SQL

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:

SQL

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

but it says:

Incorrect syntax near 'ProjectOverview'.<br />
String cannot be converted to date

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

Posted 31-Aug-11 0:12am

serigraphie

Updated 31-Aug-11 0:19am

Prerak Patel

v2

Add a Solution

Comments

Herman<T>.Instance 31-Aug-11 6:17am

please show table structure

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2011-08-31T00:21:00

Solution 1

I'm almost positive that you can't use MAX on a string. Try it this way:

SQL

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

Posted 31-Aug-11 0:21am

#realJSOP

v2

serigraphie · Answer 2 · 2011-08-31T00:30:00

hello John, thanks for the response, and for you reference, here is the whole code:

SQL

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]

and an error appeared when I performed the code you have given me
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.