Incorrect Syntax!?! - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Incorrect Syntax!?!

Author

Topic

Spica66
Starting Member

23 Posts

Posted - 2012-07-26 : 07:52:50

Can anyone tell me why this statement has "Incorrect syntax near the keyword 'WHERE'." ?

SELECT c.NFCUSTOMER, c.CLAIMID

FROM NFWARRANTYCLAIMS c,

(SELECT MAX(h.TRANSDATE) TRANSDATE

FROM NFWARRANTYCLAIMHISTORY h

WHERE NFCLAIMSTATUS=20

GROUP BY NFCLAIMID)

WHERE c.CLAIMID = h.NFCLAIMID;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-26 : 08:01:21

Your subquery needs an alias.

SELECT c.NFCUSTOMER, c.CLAIMID
FROM NFWARRANTYCLAIMS c,
(SELECT MAX(h.TRANSDATE) TRANSDATE
FROM NFWARRANTYCLAIMHISTORY h
WHERE NFCLAIMSTATUS=20
GROUP BY NFCLAIMID) AS h
WHERE c.CLAIMID = h.NFCLAIMID;

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-26 : 08:03:33

But on the other hand, your query cannot run anyway since there are more syntax errors.
Try this one

SELECT		c.NFCUSTOMER,
		c.CLAIMID,
		h.TRANSDATE
FROM		dbo.NFWARRANTYCLAIMS AS c
INNER JOIN	(
			SELECT		NFCLAIMID,
					MAX(TRANSDATE) AS TRANSDATE 
			FROM		dbo.NFWARRANTYCLAIMHISTORY
			WHERE		NFCLAIMSTATUS = 20 
			GROUP BY	NFCLAIMID
		) AS h ON h.NFCLAIMID = c.CLAIMID;

N 56°04'39.26"
E 12°55'05.63"

Spica66
Starting Member

23 Posts

Posted - 2012-07-26 : 08:05:27

Thank you VERY much!!!

Spica66
Starting Member

23 Posts

Posted - 2012-07-26 : 09:11:11

This worked perfectly:
SELECT c.NFCUSTOMER,
c.CLAIMID,
h.TRANSDATE
FROM dbo.NFWARRANTYCLAIMS AS c
INNER JOIN (
SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM dbo.NFWARRANTYCLAIMHISTORY
WHERE NFCLAIMSTATUS = 20
GROUP BY NFCLAIMID
) AS h ON h.NFCLAIMID = c.CLAIMID;

Now I need to get a few more columns from a third table WARRANTYCLAIMSTABLE t. I can use the c.CLAIMID as the key. How would I do a second join?

Spica66
Starting Member

23 Posts

Posted - 2012-07-26 : 09:26:08

I tried this:

SELECT c.NFCUSTOMER,
c.CLAIMID,
h.TRANSDATE,
t.HRVYEARID,
t.HRVMODELID,
t.HRVBRANDID

FROM dbo.NFWARRANTYCLAIMS AS c

INNER JOIN (
SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM NFWARRANTYCLAIMHISTORY
WHERE NFCLAIMSTATUS = 20
GROUP BY NFCLAIMID
) AS h ON h.NFCLAIMID = c.CLAIMID

LEFT JOIN dbo.NFWARRANTYTABLE t
ON c.CLAIMID = t.INVENTSERIALID

but the three new values (columns) came up with all null values and I know that there are values that match.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-26 : 09:32:25

Check the datatypes of c.CLAIMID and t.INVENTSERIALID.
Maybe the datatypes are different and then it is possible that they are not equal because of leading zeroes or whatever...

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-26 : 09:34:39

Don't forget that the derived table h filters out all nfclaimid NOT having a claimstatus of 20 (due to the inner join)!

SELECT		c.NFCUSTOMER,
		c.CLAIMID,
		h.TRANSDATE,
		t.HRVYEARID,
		t.HRVMODELID,
		t.HRVBRANDID
FROM		dbo.NFWARRANTYCLAIMS AS c
LEFT JOIN	(
			SELECT		NFCLAIMID,
					MAX(TRANSDATE) AS TRANSDATE 
			FROM		dbo.NFWARRANTYCLAIMHISTORY
			WHERE		NFCLAIMSTATUS = 20 
			GROUP BY	NFCLAIMID
		) AS h ON h.NFCLAIMID = c.CLAIMID
LEFT JOIN	dbo.NFWARRANTYTABLE AS t ON t.INVENTSERIALID = c.CLAIMID

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources