Need help with SELECT statement

  • BOR15K

    SSCertifiable

    Points: 5767

    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_id INT IDENTITY(1,1),
    carrier CHAR(3),
    departure CHAR(3),
    arrival CHAR(3),
    class_code CHAR(1),
    class_desc VARCHAR(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_classes

    If 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?

     

    • This topic was modified 6 months, 3 weeks ago by  BOR15K.
    • This topic was modified 6 months, 3 weeks ago by  BOR15K.
  • John Mitchell-245523

    SSC Guru

    Points: 148769

    Can we see your function, please?

    John

  • BOR15K

    SSCertifiable

    Points: 5767

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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

  • Steve Collins

    SSC Eights!

    Points: 883

    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").

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

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