October 4, 2010 at 7:16 pm
I'm having some issues with the highlighted portion of this script. Would anybody be able to help?
DECLARE
@OrganizationID INT,
@vin NVARCHAR(17),
@LanguageID INT,
@PageNumber INT = 1 ,
@PageSize INT = 25 ,
@SortExpression NVARCHAR(64) = ''
SET @OrganizationID = 1
SET @vin = ''
SET @LanguageID = 1
SET @PageNumber = 1
SET @PageSize = 25
SET @SortExpression = ''
BEGIN
SET NOCOUNT ON
DECLARE @LowerBound INT,
@UpperBound INT,
@TotalRows INT;
DECLARE @TmpVINOpportunitySearch TABLE
(
[RowNumber] INT ,
[Description] INT,
[Date] SMALLDATETIME,
[Odometer] NVARCHAR(64)
)
SET @LowerBound = ( @PageNumber - 1 ) * @PageSize ;
SET @UpperBound = @LowerBound + @PageSize ;
WITH VehicleDescriptionSelect AS (SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY s.[SoldDate])
-- Get Sales Information for Purchased Description (VehicleDescriptionHistoryID = 1)
AS [RowNumber],
[Date] = s.[SoldDate],
(SELECT CASE WHEN [vdhl].[LocalizedName] IS NULL THEN [vdh].[Name]
ELSE [vdhl].[LocalizedName]
END
FROM[dbo].[VehicleDescriptionHistory] vdh
LEFT JOIN [dbo].[VehicleDescriptionHistoryLocalized] vdhl
ON [vdh].[VehicleDescriptionHistoryID] = [vdhl].[VehicleDescriptionHistoryID]
WHERE vdh.[VehicleDescriptionHistoryID] = 1
AND @LanguageID = vdhl.[LanguageID]
)AS
[Description],
v.[Odometer]
FROM [dbo].[SaleTransaction] s
INNER JOIN [dbo].[Vehicle] v
ON s.[VehicleID] = v.[VehicleID]
INNER JOIN [dbo].[OrganizationVehicle] ov
ON v.VehicleID = ov.VehicleID
WHERE @vin = v.[VehicleIdentificationNumber]
AND @OrganizationID = ov.[OrganizationID]
UNION
-- Get Appraisal Information for Traded (VehicleDescriptionHistory = 2)/Appraised (VehicleDescriptionHistory = 3)
SELECT ROW_NUMBER() OVER (ORDER BY Date) AS
[RowNumber],
(SELECT CASE WHEN ta.[TradeInDate] IS NULL THEN ta.[CreateDate]
ELSE ta.[TradeInDate]
END) AS
[Date]
FROM [dbo].[TradeAppraisal] ta,
(SELECT CASE WHEN ta.[TradeInDate] IS NOT NULL THEN
(SELECT CASE
WHEN [vdhl].[LocalizedName] IS NULL THEN [vdh].[Name]
ELSE [vdhl].[LocalizedName]
END
FROM[dbo].[VehicleDescriptionHistory] AS vdh
LEFT JOIN [dbo].[VehicleDescriptionHistoryLocalized] AS vdhl
ON [vdh].[VehicleDescriptionHistoryID] = [vdhl].[VehicleDescriptionHistoryID]
WHEREvdh.[VehicleDescriptionHistoryID] = 2
AND @LanguageID = vdhl.[LanguageID]
)
ELSE
(
SELECTCASE
WHEN [vdhl].[LocalizedName] IS NULL THEN [vdh].[Name]
ELSE [vdhl].[LocalizedName]
END
FROM[dbo].[VehicleDescriptionHistory] vdh
LEFT JOIN [dbo].[VehicleDescriptionHistoryLocalized] vdhl
ON [vdh].[VehicleDescriptionHistoryID] = [vdhl].[VehicleDescriptionHistoryID]
WHEREvdh.[VehicleDescriptionHistoryID] = 3
AND @LanguageID = vdhl.[LanguageID]
)
END AS
[Description],
ta.[Odometer]
FROM[dbo].[TradeAppraisal] AS ta
INNER JOIN [dbo].[Vehicle] AS v
ON [ta].[VehicleID] = [v].[VehicleID]
INNER JOIN [dbo].[OrganizationVehicle] AS ov
ON [v].[VehicleID] = [ov].[VehicleID]
WHEREv.[VehicleIdentificationNumber] = @vin
ANDov.[OrganizationID] = @OrganizationID
October 4, 2010 at 7:22 pm
Here is the full Script... Tele sense is throwing an error at the INSERT statement where I am trying to populate the temp table.
DECLARE
@OrganizationID INT,
@vin NVARCHAR(17),
@LanguageID INT,
@PageNumber INT = 1 ,
@PageSize INT = 25 ,
@SortExpression NVARCHAR(64) = ''
SET @OrganizationID = 1
SET @vin = ''
SET @LanguageID = 1
SET @PageNumber = 1
SET @PageSize = 25
SET @SortExpression = ''
BEGIN
SET NOCOUNT ON
DECLARE @LowerBound INT,
@UpperBound INT,
@TotalRows INT;
DECLARE @TmpVINOpportunitySearch TABLE
(
[RowNumber] INT ,
[Description] INT,
[Date] SMALLDATETIME,
[Odometer] NVARCHAR(64)
)
SET @LowerBound = ( @PageNumber - 1 ) * @PageSize ;
SET @UpperBound = @LowerBound + @PageSize ;
WITH VehicleDescriptionSelect AS (SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY s.[SoldDate])
-- Get Sales Information for Purchased Description (VehicleDescriptionHistoryID = 1)
AS [RowNumber],
[Date] = s.[SoldDate],
(SELECT CASE WHEN [vdhl].[LocalizedName] IS NULL THEN [vdh].[Name]
ELSE [vdhl].[LocalizedName]
END
FROM[dbo].[VehicleDescriptionHistory] vdh
LEFT JOIN [dbo].[VehicleDescriptionHistoryLocalized] vdhl
ON [vdh].[VehicleDescriptionHistoryID] = [vdhl].[VehicleDescriptionHistoryID]
WHERE vdh.[VehicleDescriptionHistoryID] = 1
AND @LanguageID = vdhl.[LanguageID]
)AS
[Description],
v.[Odometer]
FROM [dbo].[SaleTransaction] s
INNER JOIN [dbo].[Vehicle] v
ON s.[VehicleID] = v.[VehicleID]
INNER JOIN [dbo].[OrganizationVehicle] ov
ON v.VehicleID = ov.VehicleID
WHERE @vin = v.[VehicleIdentificationNumber]
AND @OrganizationID = ov.[OrganizationID]
UNION
-- Get Appraisal Information for Traded (VehicleDescriptionHistory = 2)/Appraised (VehicleDescriptionHistory = 3)
SELECT ROW_NUMBER() OVER (ORDER BY Date) AS
[RowNumber],
(SELECT CASE WHEN ta.[TradeInDate] IS NULL THEN ta.[CreateDate]
ELSE ta.[TradeInDate]
END) AS
[Date]
FROM [dbo].[TradeAppraisal] ta,
(SELECT CASE WHEN ta.[TradeInDate] IS NOT NULL THEN
(SELECT CASE
WHEN [vdhl].[LocalizedName] IS NULL THEN [vdh].[Name]
ELSE [vdhl].[LocalizedName]
END
FROM[dbo].[VehicleDescriptionHistory] AS vdh
LEFT JOIN [dbo].[VehicleDescriptionHistoryLocalized] AS vdhl
ON [vdh].[VehicleDescriptionHistoryID] = [vdhl].[VehicleDescriptionHistoryID]
WHEREvdh.[VehicleDescriptionHistoryID] = 2
AND @LanguageID = vdhl.[LanguageID]
)
ELSE
(
SELECTCASE
WHEN [vdhl].[LocalizedName] IS NULL THEN [vdh].[Name]
ELSE [vdhl].[LocalizedName]
END
FROM[dbo].[VehicleDescriptionHistory] vdh
LEFT JOIN [dbo].[VehicleDescriptionHistoryLocalized] vdhl
ON [vdh].[VehicleDescriptionHistoryID] = [vdhl].[VehicleDescriptionHistoryID]
WHEREvdh.[VehicleDescriptionHistoryID] = 3
AND @LanguageID = vdhl.[LanguageID]
)
END AS
[Description],
ta.[Odometer]
FROM[dbo].[TradeAppraisal] AS ta
INNER JOIN [dbo].[Vehicle] AS v
ON [ta].[VehicleID] = [v].[VehicleID]
INNER JOIN [dbo].[OrganizationVehicle] AS ov
ON [v].[VehicleID] = [ov].[VehicleID]
WHEREv.[VehicleIdentificationNumber] = @vin
ANDov.[OrganizationID] = @OrganizationID
-- Get Service information for Serviced Description (VehicleDescriptionHistoryID = 4)
UNION
SELECT
ROW_NUMBER() OVER (ORDER BY Date) AS
[RowNumber],
[Date] = st.[ServicedDate],
(SELECT CASE WHEN [vdhl].[LocalizedName] IS NULL
THEN [vdh].[Name]
ELSE [vdhl].[LocalizedName]
END
FROM[dbo].[VehicleDescriptionHistory] AS vdh
LEFT JOIN [dbo].[VehicleDescriptionHistoryLocalized] AS vdhl
ON [vdh].[VehicleDescriptionHistoryID] = [vdhl].[VehicleDescriptionHistoryID]
WHEREvdh.[VehicleDescriptionHistoryID] = 4
AND @LanguageID = vdhl.[LanguageID]
)AS
[Description],
[Odometer] = st.[Odometer]
FROM [dbo].[ServiceTransaction] st
INNER JOIN [dbo].[ServiceLead] sl
ON sl.[ServiceLeadID]= sl.[ServiceLeadID]
INNER JOIN [dbo].[CustomerVehicle] cv
ON sl.[CustomerVehicleID] = cv.[CustomerVehicleID]
INNER JOIN [dbo].[Vehicle] v
ON cv.[VehicleID] = v.[VehicleID]
INNER JOIN [dbo].[OrganizationVehicle] o
ON [v].[VehicleID] = [o].[VehicleID]
WHERE @vin = v.[VehicleIdentificationNumber]
AND @OrganizationID = o.[OrganizationID]
INSERT INTO @TmpVINOpportunitySearch
(
[RowNumber] ,
[Date] ,
[Description] ,
[Odometer]
)
SELECT [RowNumber] ,
[Date] ,
[Description] ,
[Odometer]
FROM VehicleDescriptionSelect
SET @TotalRows = @@ROWCOUNT
SELECT [RowNumber] ,
[Date] ,
[Description] ,
[Odometer] ,
@TotalRows AS TotalRecords
FROM @TmpVINOpportunitySearch AS tmp
WHERE tmp.[RowNumber] > @LowerBound
AND tmp.[RowNumber] <= @UpperBound
SET Nocount OFF
END ;
October 4, 2010 at 8:11 pm
I know this isn't going to be what you want to hear, but the code is just wrong. It appears that you are trying to nest a CASE inside a FROM clause. I can't figure out what you are attempting to achieve.
To help you, we will need a few things from you. First, read the first article I reference below in my signature block regarding asking for help. Following the instructions in that article, please provide the DDL (CREATE TABLE statement(s)) for the table(s) involved in your query, sample data for the table(s) (as a series of INSERT INTO statement(s)), and most importantly the expected results based on the sample data you provide.
With all this, you will definitely get the help you are seeking, plus you will get tested code in return.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply