Our Case statements can get very long and complicated and I would like to maintain just a single "Case" and than depending on the type of set I'm processing,
use this generic "Case" statement to properly decode the value. My SQL code is listed below.
Select
'PCAR' as [vtyp]
,Case
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When [vtyp]='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as 'Engine_Decode'
From veh_owner
Where substring(veh_vin,10,1) = '3' --Selects on cars
Union All
Select
'PTRK' as [vtyp]
,Case
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When [vtyp]='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When [vtyp]='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as 'Engine_Decode'
From veh_owner
Where substring(veh_vin,10,1) = '4' --Selects only Trucks
I would like to deploy the SQL code described above but get the following errors:
Msg 207, Level 16, State 1, Line 18 Invalid column name 'vtyp'
Any ideas, comments or help with this issue will be greatly appreciated.
sfmd:
SQL Server does not have this kind of alias. I am not sure that I understand what you are looking for, is this heading in the right direction?:
select veh_vin,
substring ('1 2 PCARPTRK', 4 * convert(integer, substring (veh_vin,10,1)) - 3, 4) as [vtyp],
case when substring (veh_vin,10,1) = '3' and substring (veh_vin,8,1) = 'A' then 'Cyl = V-6 Fuel = Gasoline'
when substring (veh_vin,10,1) = '4' and substring (veh_vin,8,1) = 'A' then 'Cyl = V-8 Fuel = Diesel'
when substring (veh_vin,10,1) = '3' and substring (veh_vin,8,1) = 'B' then 'Cyl = V-6 Fuel = CNG'
else '?'
end as Engine_Decode
from veh_owner
where substring (veh_vin,10,1) between '3' and '4'
-- - Sample Output: -
|||-- veh_vin vtyp Engine_Decode
-- -- -
-- 1233567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1243567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1253567B1373 PCAR Cyl = V-6 Fuel = CNG
-- 1263567A1373 PCAR Cyl = V-6 Fuel = Gasoline
-- 1334567A1473 PTRK Cyl = V-8 Fuel = Diesel
-- 1344467A1473 PTRK Cyl = V-8 Fuel = Diesel
-- 2344467B1473 PTRK ?Dave
One thing to consider is persisting these two characters of the VIN. I realize that a VIN is what it is and you cannot change these "smart coded" columns; nonetheless, these portions of the VIN have a specific meaning and it might be a good idea to give these meanings an existence of themselves rather than only existing as a "sub-column".
|||
Dave
>>One thing to consider is persisting these two characters of the VIN. I realize that a VIN is what it is and you cannot change these "smart coded" columns; nonetheless, these portions of the VIN have a specific meaning and it might be a good idea to give these meanings an existence of themselves rather than only existing as a "sub-column".<<
Absolutely. Anytime you find yourself querying on a substring, you probably ought to reconsider what you are doing :)
|||
Perhaps I was not clear enough with my example
My point with this thread is to find a way to use a generic or multi-purpose "Case" Statement to process similar "sets" of rows. The selection of each processing set, in this case either all cars or all trucks, but never a mixed group of vehicles can be handled a thousand different ways by configuring a "Where" clause.
However the vehicle group is selected, I would like to use a generic "Case" for decoding certain values. I will always know the composition of the set (vtyp=value) again either cars or trucks and need to convey/pass this informational profile to the generic "Case" statement.
Notice that a car "A" in VIN position 10 decodes differently than a truck "A" in VIN position 10, that's the crux of the problem. Sure it's easy to use and maintain 2 different "Case" Statements(1 for car and 1 for trucks), but that's not what I initially would prefer to do. It's sure to get out of hand very quickly.
I hope this helps clarify my issue
|||In answer to your question "no" you cannot do what you want without making it dynamic SQL.|||
How about having UDF on this..
Create Function dbo.VehData(@.Vtype as varchar(10), @.VinId Varchar(10))
returns Table As
Return (Select
@.Vtype as [vtyp]
,Case
When @.Vtype='PCAR' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-6 Fuel = Gasoline'
When @.Vtype='PTRK' and substring(veh_vin,8,1)= 'A' Then 'Cyl = V-8 Fuel = Diesel'
When @.Vtype='PCAR' and substring(veh_vin,8,1)= 'B' Then 'Cyl = V-6 Fuel = CNG'
Else '?' End as Engine_Decode
From veh_owner
Where substring(veh_vin,10,1) = @.VinId )
Go
Select * From VehData('PCAR',3)
Union All
Select * From VehData('PTRK',4)
Union ALL
..etc
