steege_houtx
asked on
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.
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.
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
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
ASKER
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.
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.
ASKER
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.
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.
ASKER
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?
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?
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
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
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
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
ASKER
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."
I just tried that with the original code, and I still come up with "The multi-part identifier "Jobs.Jobcode" could not be bound."
ASKER
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.
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.
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!!
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!!
ASKER
Received a number of suggestions, but this was the one that took care of the problem. Good job!
You're welcome. Glad I could help.
James
James
select *
from jobs
where JobDate =
(select max(a.InvoiceDate)
from Invoices a
where jobs.jobcode = a.JobCode
and a.status = 4)