Hi everybody,
I have this:
[CODE]
"SELECT TOP(1) ID, BegindatumCert, EinddatumCert, Merk, ISNULL((SELECT Omschrijving FROM Techdata.dbo.Merk WHERE ID " & _
"= derivedtabel.merk), '- Onbekend -') AS sMerk, Kenteken, CertificaatNr, KlantNaam,derivedtabel.[status], ISNULL((SELECT Omschrijving " & _
"FROM [Status] WHERE (ID = derivedtabel.[status])), '- Onbekend -') AS sStatus, ISNULL((SELECT Omschrijving FROM " & _
"TechData.dbo.Enum AS Enum WHERE (Enum.Id = (SELECT TypeAuto FROM Techdata.dbo.[Auto] WHERE ID = " & _
"derivedtabel.[auto])) AND (Enum.EnumType = 4) AND (Enum.IsVerwijderd = 0)), '- Onbekend -') AS sTypeAuto, " & _
"(SELECT Code FROM CardType WHERE ID = (SELECT CardType FROM CertificaatCardType WHERE Certificaat = derivedtabel.ID)) " & _
"AS CardType, Reparateur, rowtotal FROM (SELECT rowtotal = COUNT(*) OVER(), Row_number() OVER(ORDER BY @ORDER) AS rownum, Certificaat.id, " & _
"begindatumcert, einddatumcert, merk, kenteken, [auto], certificaatnr, klantnaam, [status], Reparateur FROM certificaat INNER JOIN " & _
"Management.dbo.Bedrijf AS Reparateur ON Certificaat.Reparateur = Reparateur.ID @WHERE ) derivedtabel WHERE " & _
"rownum BETWEEN @STARTROW AND (@STARTROW + @PAGESIZE) - 1 ORDER BY Gemaakt DESC"
[/CODE]
But I get this error:
Invalid column name 'Gemaakt'. But this column exist
This is the original QUERY:
[code]
use mobiliteitsgarantie
DECLARE @startrow int, @PAGESIZE int
SET @startrow = 0
SET @PAGESIZE = 100
Declare @Kenteken varchar(50), @Order Varchar(50)
SET @Kenteken = '29RPB6' -- Kenteken maken we even als variabele. We willen alle certificaten op dit kenteken
SET @Order = 'ASC' --ASC of DESC
SELECT top (1) ID,
BegindatumCert,
EinddatumCert,
Merk,
ISNULL((SELECT Omschrijving
FROM Techdata.dbo.Merk
WHERE ID = derivedtabel.merk), '- Onbekend -') AS sMerk,
Kenteken,
CertificaatNr,
KlantNaam,
derivedtabel.[status],
ISNULL((SELECT Omschrijving
FROM [Status]
WHERE (ID = derivedtabel.[status])), '- Onbekend -') AS sStatus,
ISNULL((SELECT Omschrijving
FROM TechData.dbo.Enum AS Enum
WHERE (Enum.Id =
(SELECT TypeAuto
FROM Techdata.dbo.[Auto]
WHERE ID = derivedtabel.[auto])) AND
(Enum.EnumType = 4) AND
(Enum.IsVerwijderd = 0)), '- Onbekend -') AS sTypeAuto,
(SELECT Code
FROM CardType
WHERE ID =
(SELECT CardType
FROM CertificaatCardType
WHERE Certificaat = derivedtabel.ID)) AS CardType,
Reparateur,
rowtotal
FROM
(SELECT rowtotal = COUNT(*) OVER(), Row_number() OVER(ORDER BY @ORDER) AS rownum, Certificaat.id, begindatumcert, einddatumcert, merk, kenteken, [auto],
certificaatnr, klantnaam, [status], Reparateur, Certificaat.Gemaakt
FROM certificaat INNER JOIN
Management.dbo.Bedrijf AS Reparateur ON
Certificaat.Reparateur = Reparateur.ID
WHERE Kenteken = @Kenteken -- We selecteren uit de totale tabel alleen die certificaten met het gewenste kenteken.
--Anders gaan we de hele tabel uitvragen. Dit gebeurt normaal in code.
) derivedtabel
WHERE rownum BETWEEN @STARTROW AND
(@STARTROW + @PAGESIZE) - 1
ORDER BY Gemaakt DESC
[code]
THX