Query problem  | Expert | | Join Date: Jul 2008 Location: Utrecht, The Netherlands
Posts: 283
| |
Hi,
I'm using the following query to (duh!) query my database. Now, I changed from Access 2003 to SQL Server 2005. It returns two errors and I can't find out why it does that.
The errors are: Msg 195, Level 15, State 10, Line 1
'FIRST' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'tblAanvraag'.
Here's my query: - SELECT DISTINCT [tblAanvraag.AanvraagID] AS Aanvraagnummer, [tblAanvraag.PA-Nummer] AS [PA-Nummer], ([tblMedewerker.MedewerkerAchternaam]+', '+[tblMedewerker.MedewerkerVoornaam]+' '+[tblMedewerker.MedewerkerTussenvoegsel]) AS Aanvrager, [tblAanvraag.Aanvraagdatum] AS Aanvraagdatum, FIRST(tblMoleculair.MoleculairKorteNaam) AS Vraagstelling, [tblStatus.Status] AS Status FROM ((tblAanvraag INNER JOIN tblMedewerker ON tblAanvraag.AanvragerID = tblMedewerker.MedewerkerID) INNER JOIN tblStatus ON tblAanvraag.StatusCode = tblStatus.StatusCode) INNER JOIN (tblAangevraagdMoleculair INNER JOIN tblMoleculair ON tblAangevraagdMoleculair.MoleculairID = tblMoleculair.MoleculairID) ON tblAanvraag.AanvraagID = tblAangevraagdMoleculair.AanvraagID GROUP BY [tblAanvraag.AanvraagID], [tblAanvraag.PA-Nummer], ([tblMedewerker.MedewerkerAchternaam]+', '+[tblMedewerker.MedewerkerVoornaam]+' '+[tblMedewerker.MedewerkerTussenvoegsel]), [tblAanvraag.Aanvraagdatum], [tblStatus.Status], tblAanvraag.MoleculairePathologie, tblAanvraag.StatusCode HAVING (((tblAanvraag.MoleculairePathologie)=True) AND ((tblAanvraag.StatusCode)<4))
Can anyone help me with this one? It would be greatly appeciated!
Cheers,
Steven
| | Lives Here | | Join Date: Sep 2006
Posts: 12,070
| | | re: Query problem
The part - INNER JOIN (tblAangevraagdMoleculair
-
INNER JOIN tblMoleculair ON tblAangevraagdMoleculair.MoleculairID = tblMoleculair.MoleculairID)
-
ON tblAanvraag.AanvraagID = tblAangevraagdMoleculair.AanvraagID
Looks suspicious to me.
Did you mean - FROM tblAanvraag INNER JOIN tblMedewerker ON tblAanvraag.AanvragerID = tblMedewerker.MedewerkerID
-
INNER JOIN tblStatus ON tblAanvraag.StatusCode = tblStatus.StatusCode
-
INNER JOIN tblAangevraagdMoleculair ON tblAanvraag.AanvraagID = tblAangevraagdMoleculair.AanvraagID
-
INNER JOIN tblMoleculair ON tblAangevraagdMoleculair.MoleculairID = tblMoleculair.MoleculairID
instead?
| | Expert | | Join Date: Jul 2008 Location: Utrecht, The Netherlands
Posts: 283
| | | re: Query problem
No, I think that part works fine...
| | Lives Here | | Join Date: Sep 2006
Posts: 12,070
| | | re: Query problem
Quote:
Originally Posted by MrMancunian No, I think that part works fine... Silly me. That First function is not supported in SQL 2000. Is that what you are using? You'd need to use TOP.
| | Expert | | Join Date: Jul 2008 Location: Utrecht, The Netherlands
Posts: 283
| | | re: Query problem
Quote:
Originally Posted by r035198x Silly me. That First function is not supported in SQL 2000. Is that what you are using? You'd need to use TOP. Ok, how do I use TOP in this query? It tried some things but it keeps telling me it's incorrect syntax.
| | Lives Here | | Join Date: Sep 2006
Posts: 12,070
| | | re: Query problem
Quote:
Originally Posted by MrMancunian Ok, how do I use TOP in this query? It tried some things but it keeps telling me it's incorrect syntax. If you are solving the top rows per group problem then I suggest you look at this article.
| | Expert | | Join Date: Jul 2008 Location: Utrecht, The Netherlands
Posts: 283
| | | re: Query problem
Quote:
Originally Posted by r035198x If you are solving the top rows per group problem then I suggest you look at this article. Ok, I changed my query to a working one. Except for one thing. The TOP-part doesn't work the way I want to. Let me explain what it should do. The system I built is a request application for Pathologists. They can request molecular techniques on a tissue sample. More than one technique per sample can be requested. When they request multiple techniques, there should be more than one row in the tblMoleculair selected. As these are all in the same family, it doesn't matter which of the selected tecnhiques get picked. The only problem is that, regardless the chosen technique, it just returns the first name in the table, period. How can I change this, using the following query: - SELECT DISTINCT
-
tblAanvraag.AanvraagID AS Aanvraagnummer,
-
[PA-Nummer],
-
(tblMedewerker.MedewerkerAchternaam+', '+tblMedewerker.MedewerkerVoornaam+' '+tblMedewerker.MedewerkerTussenvoegsel) AS Aanvrager,
-
tblAanvraag.Aanvraagdatum AS Aanvraagdatum,
-
(SELECT TOP (1) MoleculairKorteNaam FROM tblMoleculair) AS Vraagstelling,
-
tblStatus.Status AS Status
-
FROM tblAanvraag
-
INNER JOIN tblMedewerker ON tblAanvraag.AanvragerID = tblMedewerker.MedewerkerID
-
INNER JOIN tblStatus ON tblAanvraag.StatusCode = tblStatus.StatusCode
-
INNER JOIN tblAangevraagdMoleculair ON tblAanvraag.AanvraagID = tblAangevraagdMoleculair.AanvraagID
-
INNER JOIN tblMoleculair ON tblAangevraagdMoleculair.MoleculairID = tblMoleculair.MoleculairID
-
GROUP BY tblAanvraag.AanvraagID, tblAanvraag.[PA-Nummer],
-
(tblMedewerker.MedewerkerAchternaam+', '+tblMedewerker.MedewerkerVoornaam+' '+tblMedewerker.MedewerkerTussenvoegsel),
-
tblAanvraag.Aanvraagdatum, tblStatus.Status, tblAanvraag.MoleculairePathologie, tblAanvraag.StatusCode
-
HAVING tblAanvraag.MoleculairePathologie=1
-
AND tblAanvraag.StatusCode<4
| | Expert | | Join Date: Jul 2008 Location: Utrecht, The Netherlands
Posts: 283
| | | re: Query problem
Dont bother, I already found the solution, using another method. - SELECT DISTINCT
-
tblAanvraag.AanvraagID AS Aanvraagnummer,
-
[PA-Nummer],
-
(tblMedewerker.MedewerkerAchternaam+', '+tblMedewerker.MedewerkerVoornaam+' '+tblMedewerker.MedewerkerTussenvoegsel) AS Aanvrager,
-
tblAanvraag.Aanvraagdatum AS Aanvraagdatum,
-
MAX(tblMoleculair.MoleculairKorteNaam) AS Vraagstelling,
-
tblStatus.Status AS Status
-
FROM tblAanvraag
-
INNER JOIN tblMedewerker ON tblAanvraag.AanvragerID = tblMedewerker.MedewerkerID
-
INNER JOIN tblStatus ON tblAanvraag.StatusCode = tblStatus.StatusCode
-
INNER JOIN tblAangevraagdMoleculair ON tblAanvraag.AanvraagID = tblAangevraagdMoleculair.AanvraagID
-
INNER JOIN tblMoleculair ON tblAangevraagdMoleculair.MoleculairID = tblMoleculair.MoleculairID
-
WHERE tblAanvraag.AanvragerID=85
-
GROUP BY tblAanvraag.AanvraagID, tblAanvraag.[PA-Nummer],
-
(tblMedewerker.MedewerkerAchternaam+', '+tblMedewerker.MedewerkerVoornaam+' '+tblMedewerker.MedewerkerTussenvoegsel),
-
tblAanvraag.Aanvraagdatum, tblStatus.Status, tblAanvraag.MoleculairePathologie, tblAanvraag.StatusCode
-
HAVING tblAanvraag.MoleculairePathologie=1
-
ORDER BY tblAanvraag.AanvraagID DESC
Thnx for all the help! :)
|  |
Similar Microsoft SQL Server bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,467 network members.
|
