• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 734
  • Last Modified:

Incorrect syntax near '='

Preface: I am new to SQL and Crystal Reports both, so this may have an easy answer I can't find.

I have been trying to run the following code in MSN SQL Server 2008 and as a SQL expression in Crystal Reports XI 2:

(
SELECT MAX(InvoiceDate)
FROM Invoices
WHERE "JobCode" IN (SELECT "JobCode" FROM Invoices)
AND "Status" IN (SELECT "Status" FROM Invoices) = 4
)

I get the error message "Incorrect syntax near '=' ".
I tried substituting IS NOT NULL for the = 4, then got the error message Incorrect syntax near 'IS' .  With this code, I am trying to rewrite the following code from a Crystal Reports report, which comes up with the error message "Reserved error (|); there is no message for this error."

(select max(a.InvoiceDate)
from Invoices a
where jobs.jobcode = a.JobCode
and a.status = 4)

Any and all help greatly appreciated.


0
steege_houtx
Asked:
steege_houtx
  • 8
  • 3
  • 2
  • +2
1 Solution
 
dwkorCommented:
Frankly neither of those code snippets are correct. It looks like the last selectshould be the part of subquery - something like

select *
from jobs
where JobDate =
(select max(a.InvoiceDate)
from Invoices a
where jobs.jobcode = a.JobCode
and a.status = 4)
0
 
PreeceCommented:
Looking at your first query, I would rewrite it this way:

select i.jobcode, max(i.invoicedate)
from invoices i
where i.status in (4)

But the second query mentions a jobs table.  So, I'm assuming you need to join the two tables.  What exactly are you after in both tables?

Preece
0
 
steege_houtxAuthor Commented:
I'm trying to get the most recent invoice date for a particular jobcode from the invoices table.  I didn't write the query I posted second - the problem is that I'm trying to get it to work as part of a Crystal Report.  The invoices table has the jobcode listed in it; my initial assumption is that because of this, I don't need the jobs table in the query, I should be able to use the jobcode from the invoices table.

I tried running the code as you suggest, and it returns the following:
Column 'invoices.JobCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
steege_houtxAuthor Commented:
dwkor,

When I run your suggestion, I end up with the following:
The multi-part identifier "jobs.jobcode" could not be bound.

That (multi-part identifier issue) was the problem I had when I tried to use Jobs.Jobcode before I tried switching the query to the more convoluted one I initially asked about.
As I say, I am a newbie, using manuals & tutorials & anything & everything I can to do this.  Again, all help appreciated.
0
 
steege_houtxAuthor Commented:
dwkor,

I changed your suggestion slightly to

(
select *
from Invoices
where InvoiceDate =
(select max(a.InvoiceDate)
from Invoices a
where invoices.jobcode = a.JobCode
and a.status = 4)
)

and this ran well in MSN SQL Server and returned what I wanted, BUT when I used it as an expression in Crystal, it gave me this:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Any ideas?
0
 
mlmccCommented:
Where are you entering the code?

Is it in a Crystal command or inSQL?

Do you only need the maxdate for the report or do you need other information from the tables?

Do you want this maxdate for ALL jobcodes or only a specific jobcode?

mlmcc
0
 
James0628Commented:
 mlmcc,

 He said in the first post that it was a SQL Expression.


 steege_houtx,

 SQL Expressions are not really my area (almost all of my reports use stored procedures, and you can't use SQL Expressions with them), but your original code basically looks OK:

(select max(a.InvoiceDate)
from Invoices a
where jobs.jobcode = a.JobCode
and a.status = 4)

 I'm assuming that JOBS is a table in the report.

 However, on my system, SQL Expressions seem to be case sensitive.  So, for example, if the actual table and column names are all uppercase, that would need to be:

(select max(a.INVOICEDATE)
from INVOICES a
where JOBS.JOBCODE = a.JOBCODE
and a.STATUS = 4)


 James
0
 
steege_houtxAuthor Commented:
James0628,

I just tried that with the original code, and I still come up with "The multi-part identifier "Jobs.Jobcode" could not be bound."
0
 
steege_houtxAuthor Commented:
mlmcc,

The code is for a SQL query/expression I am trying to use in Crystal Reports.  With this particular SQL expression, I am trying to get the maxdate (or most recent date) of the invoices for any jobcodes which have been invoiced.  It is being used in a report to find the last invoice issued.
0
 
mlmccCommented:
A SQL Expression can only return 1 value which in your case will a the maxdate for a single product.  It cannot return the maxdate for all products.  It cannot return a recordset.

It may have to be like

(select max(a.INVOICEDATE)
from INVOICES a
where a.JOBCODE = SELECT THE JOB CODE FIELD FROM THE FIELD LIST
and a.STATUS = 4)

mlmcc

0
 
steege_houtxAuthor Commented:
mlmcc:

I am researching your last comment now, as I don't know the answer there.  I know the guy who wrote it is supposed to be a Crystal programmer, but then again, the original code didn't work properly, & that's what started all this. I'll get back to you.  Thanks for your efforts.
0
 
James0628Commented:
I think mlmcc is a bit off in what he said (or seems to be saying) about your expression.  Yes, SQL Expressions only return one value, but that's all that yours seems to be trying to do, so I don't see a problem in that regard.  It's trying to produce the max date for a JobCode.  Put that SQL Expression in a detail section, and it will be run for every record, producing the max date for the JobCode in that record (technically, it gets incorporated into the report's main query as a sub-query).


 For your error on "Jobs.Jobcode", I assume that you're using the Jobs table in your report.  I would delete Jobs.Jobcode from the SQL Expression and then find the Jobcode field in the Field Explorer and double-click it to add it to the expression.  That way you know that it's spelled exactly the way that CR expects it to be (same case, etc.).

 I've also found that if you use a table alias in the expression and use the alias in the WHERE, to compare a column in the expression to one in your report, the report field has to come first.  Using your original code, and assuming that jobs.jobcode was the correct name:

 This would work:

jobs.jobcode = a.JobCode

 But this would give you that "multi-part identifier ... could not be found" error:

a.JobCode = jobs.jobcode

 I don't know why, but that's the way it works here.

 Either order seems to work without the alias:

jobs.jobcode = JobCode

   or

JobCode = jobs.jobcode


 If you're still having problems, post your current expression.

 James
0
 
steege_houtxAuthor Commented:
James0628:

I've just been able to get back to this and try your suggestion.  Would've sworn I had already tried that, but apparently not.  That seems to have worked.  THANK YOU!!
0
 
steege_houtxAuthor Commented:
Received a number of suggestions, but this was the one that took care of the problem.  Good job!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now