January 15, 2020 at 10:30 am
Hello,
I need some help with SELECT statement, please. We query 3rd party table (I cannot amend its structure nor the content) to obtain descriptions for various airline companies' classes. the partial sample below is for Air France. A quick explanation : departure and arrival are the airport THREE letters codes, if the value starts with * followed by two letters, then it is a country - in my example *TN stands for Tunisia and *MA - Morocco, if it has only two letters - then it is area, e.g. EU - European Union. I have cities view in place, so I always know for every airport its country and area.
IF OBJECT_ID('tempdb..#Airline_classes') IS NOT NULL
DROP TABLE #Airline_classes;
GO
CREATE TABLE #Airline_classes
(
row_idINT IDENTITY(1,1),
carrierCHAR(3),
departureCHAR(3),
arrivalCHAR(3),
class_codeCHAR(1),
class_descVARCHAR(20)
);
WITH AF_sample AS (
SELECT 'AF'AS carrier,
'*'AS dep,
'*'AS arr,
'F'AS classcode,
'First Class' AS class_desc
UNION ALL
SELECT 'AF'AS carrier,
'SVO'AS dep,
'EU'AS arr,
'F'AS classcode,
'Economy' AS class_desc
UNION ALL
SELECT 'AF'AS carrier,
'SVO'AS dep,
'TLV'AS arr,
'F'AS classcode,
'Economy' AS class_desc
UNION ALL
SELECT 'AF'AS carrier,
'*TN'AS dep,
'EU'AS arr,
'F'AS classcode,
'Economy' AS class_desc
UNION ALL
SELECT 'AF'AS carrier,
'EU'AS dep,
'*MA'AS arr,
'F'AS classcode,
'Economy' AS class_desc
UNION ALL
SELECT 'AF'AS carrier,
'EU'AS dep,
'EU'AS arr,
'F'AS classcode,
'Economy' AS class_desc
)
INSERT INTO #Airline_classes (carrier,departure,arrival,class_code,class_desc)
SELECT * FROM AF_Sample
SELECT *FROM #Airline_classesIf a customer flies from Paris(airport code CDG) to London (LHR) with Air France, the query
SELECT *FROM #Airline_classes
WHERE departure = 'CDG'
AND arrival = 'LHR'
will return no record, so I query by country then
SELECT *FROM #Airline_classes
WHERE departure = '*FR'
AND arrival = '*UK'
and again, it returns no value. Next I query by area
SELECT *FROM #Airline_classes
WHERE departure = 'EU'
AND arrival = 'EU'
and lucky me - I get Economy class. If I would run same process for a flight from Washington (IAD) to Los Angeles (LAX) I would end up with First Class, as neither US nor *NA (North America) are within the table, hence would be replaced with '*'.
I have a function in place, working for years, but it slightly affects the performance and the maintenance is not easy for too many lines of code there. I wonder if there is anything simpler / smarter / faster option, please?
January 15, 2020 at 11:00 am
Can we see your function, please?
John
January 15, 2020 at 11:08 am
Something like this (it has more code and some additional logic with each of the SELECT's, which is out of the scope I have raised here) - first I check for a simple From / To based on airport codes, then I start to replace with countries and so on.
------------------------------------------------------------------------
--===== Check a class for a simple FROM / TO per carrier ====
------------------------------------------------------------------------
SELECT TOP 1 @class_description =a.ClassCode+';'+
CASE a.ServLevel WHEN 'B' THEN 'J'
WHEN 'E' THEN 'Y'
WHEN 'P' THEN 'W'
ELSE a.ServLevel
END
+';'+ a.Description +';'+ COALESCE(a.Application,' ')+CHAR(32)
FROM Air_Classes a
WHERE Carrier = @i_carrier_code
AND ClassCode = @i_class
AND Dep = @i_depart_from
AND Arr = @i_arrive_to;
IF @@ROWCOUNT =1
RETURN COALESCE (@class_description,@class_economy);
------------------------------------------------------------------------
--=== IF nothing returned, replace DEPARTURE with Country Code ====
------------------------------------------------------------------------
SELECT TOP 1 @class_description =a.ClassCode+';'+
CASE a.ServLevel WHEN 'B' THEN 'J'
WHEN 'E' THEN 'Y'
WHEN 'P' THEN 'W'
ELSE a.ServLevel
END
+';'+ a.Description +';'+ COALESCE(a.Application,' ')+CHAR(32)
FROM Air_Classes a
WHERE Carrier = @i_carrier_code
AND ClassCode = @i_class
AND Dep = @k_star+LTRIM((SELECT c.Country_Code FROM City c WHERE c.IATA_City_Airport_Code = @i_depart_from))
AND Arr = @i_arrive_to;
IF @@ROWCOUNT =1
RETURN COALESCE (@class_description,@class_economy);
------------------------------------------------------------------------
--=== IF nothing returned, replace Arrival with Country Code ====
------------------------------------------------------------------------
SELECT TOP 1 @class_description =a.ClassCode+';'+
CASE a.ServLevel WHEN 'B' THEN 'J'
WHEN 'E' THEN 'Y'
WHEN 'P' THEN 'W'
ELSE a.ServLevel
END
+';'+ a.Description +';'+ COALESCE(a.Application,' ')+CHAR(32)
FROM Air_Classes a
WHERE Carrier = @i_carrier_code
AND ClassCode = @i_class
AND Dep = @i_depart_from
AND Arr = @k_star+LTRIM((SELECT c.Country_Code FROM City c WHERE c.IATA_City_Airport_Code = @i_arrive_to));
IF @@ROWCOUNT =1
RETURN COALESCE (@class_description,@class_economy);
Thank you
January 15, 2020 at 11:43 am
Ah, right - it was your IF logic that I was most interested in. If you're worried about performance, you probably ought to convert the function to an inline table-valued function.
John
January 15, 2020 at 4:31 pm
One way to simplify things could be to resolve the hierarchical adjacency between airports, countries, and areas using a table. The ServicLevel case logic could also be replaced by a table. The final procedure could inner join the tables (air_classes, area_hierarchies, and service_levels) and select top(1) based on hierarchy (1="3 letter non-* prefix", 2="3 letter * prefix with 2 letter country", 3="area").
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply