Using a table lookup instead of CASE for a View

Question

Using a table lookup instead of CASE for a View

0wen

Old Hand

Points: 349
More actions
February 16, 2017 at 3:46 am

#318857

Hi,
I have a table that stores vehicle details
dbo.VehicleDetails
DocNo    CarNo        RegPlate    LeaseYesNo    CarType     CarColour    CarMake        Model
148638    CAR.0001    MK4413KD    1        7     5         3        GTE
I need to create a view for over systems to use, but I need the view to show the keyword rather than the number. As an exampe two of the keyword tables are below
dbo.KeywordsColour
KeywordNo    Keyword
1    Black
2    Silver
3    Blue
4    White
5    Grey
6    Red
dbo.KeywordsType
KeywordNo    Keyword
1    MPV
2    Convertable
3    SUV
4    Estate
5    Coupe
I can use CASE however, each time a new value is added in the front end system I would need to add it to the query.
SELECT
     [DocNo],
     [CarNo],
  [Reg_Plate],
     CASE WHEN [LeaseYesNo] = 1 THEN 'No' ELSE 'Yes' END AS LeaseYesNo2,
     CASE WHEN [CarType] = '1' THEN 'MPV' WHEN [CARTYPE] = '2' THEN 'Convertable' ELSE 'Unknown' END AS [CAR_TYPE],
     [CarColour],
      [CarMake],
      [Model]
FROM dbo.VehicleDetails
Can anyone help me with how I could change this so that it looks up the details from a table rather than a CASE statement in the query?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You need to join to KeywordsColour on CarColour = KeywordNo, and to all the other lookup tables in a similar way. Have a look at this if you're new to joins.

John

0wen Old Hand Points: 349 More actions · Answer 2

Hi John,

Thank you for taking the time to get back to me I had got as far as the below

select CarNo, Reg_Plate, LeaseYesNo, Car_Type, Car_Colour, CarMake, Keyword AS CarColour
from dbo.VehicleDetails
join dbo.KeywordsColour
on dbo.KeywordsColour.KeywordNo =dbo.VehicleDetails.Car_Colour

That gives me an output of
CarNo    Reg_Plate    LeaseYesNo    Car_Type    Car_Colour    CarMake    CarColour
CAR.0001    MK65WZC    1    7    5    3    Grey
CAR.0002    HK45WRD    1    2    7    1    Green

However each of the keyword tables is the same design
[KeywordNo] [int] NOT NULL, [Keyword] [nvarchar](250) NOT NULL, [Deleted] [tinyint] NOT NULL, [Id] [uniqueidentifier] NOT NULL,

So when I add the second join I then get an error of ambigious Keyword, normally I would use AS and change the name but I am unsure where to do this in this join?

0wen Old Hand Points: 349 More actions · Answer 3

Thw query I have is below

select CarNo, Reg_Plate, LeaseYesNo, Car_Type, Car_Colour, CarMake, Keyword AS CarColour from dbo.VehicleDetails join dbo.KeywordsColour on dbo.KeywordsColour.KeywordNo =dbo.VehicleDetails.Car_Colour join dbo.KeywordsCarType on KeywordsCarType.KeywordNo =dbo.VehicleDetails.Car_Type

Msg 209, Level 16, State 1, Line 3 Ambiguous column name 'keyword'.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Use aliases to keep your code readable. The ambiguous column may (also) have come in your SELECT list, so make sure you alias all the columns in there as well.

SELECT v.CarNo , v.Reg_Plate , v.LeaseYesNo , v.Car_Type , v.Car_Colour , v.CarMake , c.Keyword AS CarColour , t.Keyword AS CarType FROM dbo.VehicleDetails d JOIN dbo.KeywordsColour c ON v.Car_Colour = c.Keyword JOIN dbo.KeywordsCarType t ON v.Car_type = c.Keyword

0wen Old Hand Points: 349 More actions · Answer 5

Hi John,

Thanks for taking the time to help me its all working now.