| Author |
 Topic  |
|
|
Spica66
Starting Member
23 Posts |
 Posted - 07/26/2012 : 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
Sweden
29156 Posts |
Posted - 07/26/2012 : 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
Sweden
29156 Posts |
Posted - 07/26/2012 : 08:03:33
|
But on the other hand, your query cannot run anyway since there are more syntax errors.
Try this oneSELECT 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 - 07/26/2012 : 08:05:27
|
Thank you VERY much!!!  |
|
|
|
Spica66
Starting Member
23 Posts |
Posted - 07/26/2012 : 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 - 07/26/2012 : 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. |
Edited by - Spica66 on 07/26/2012 09:27:17 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 07/26/2012 : 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
Sweden
29156 Posts |
Posted - 07/26/2012 : 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" |
|
|
| |
Topic |
|
Incorrect Syntax!?!
