A new world ORDER

Often I have seen sites where you need to select a country for some reason or other.  And of course you have to wade through a big list to find yours.    A list like this one from iso.org.  Some will put the U.S.  or the home country at the top but is there a way make this easy to maintain?  Generally what they do is add a sort column into the table.   This is just cruft that becomes another source of maintenance .

So what to do?   Add a table that lets the sort be defined and different sorts for different needs.    First the tables:

CREATE TABLE COUNTRY_CODES (
                COUNTRY_CODE CHAR(2) NOT NULL,
                COUNTRY_CODE# NUMBER(22) NOT NULL,
                COUNTRY_NAME VARCHAR2(64),
                CONSTRAINT COUNTRY_CODES_PK PRIMARY KEY (COUNTRY_CODE)
);

CREATE TABLE COUNTRY_SORT (
                USAGE VARCHAR2(16) NOT NULL,
                COUNTRY_CODE CHAR(2) NOT NULL,
                POS NUMBER(22) NOT NULL,
                CONSTRAINT COUNTRY_SORT_PK PRIMARY KEY (USAGE, COUNTRY_CODE, POS)
);

ALTER TABLE COUNTRY_SORT ADD CONSTRAINT COUNTRY_SORT_COUNTRY_CODE_FK1
FOREIGN KEY (COUNTRY_CODE)
REFERENCES COUNTRY_CODES (COUNTRY_CODE);

I then populated country_codes from the CSV in the link above and use a sequence.   Then I inserted the following into the country_sort_table:

REM INSERTING into COUNTRY_SORT
SET DEFINE OFF;
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('AT',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('BE',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('BG',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('CY',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('CZ',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('DE',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('DK',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('EE',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('ES',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('FI',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('FR',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('GB',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('GR',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('HU',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('IE',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('IT',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('LT',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('LU',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('LV',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('MT',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('NL',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('PL',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('PT',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('RO',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('SE',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('SI',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('SK',27,'EU');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('US',1,'North America');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('CA',2,'North America');
Insert into COUNTRY_SORT (COUNTRY_CODE,POS,USAGE) values ('MX',3,'North America');

Now I have two sets of sorting for the country_codes table and have not had to touch the base table.  I can select and change the order in what I select not by changing the tables or the order by

SQL> var loc varchar2(32) 
SQL> exec :loc:='North America' 
SQL> select c.*, ( select pos from country_sort s 
                    where usage = :loc 
                      and s.country_code = c.country_code ) pos 
       from country_codes c 
      order by pos, country_code# ;

COUNTRY_CODE# COUNTRY_NAME                        CO       POS
-------- --------------------------------------- -- ------------
      236 UNITED STATES                           US         1
       40 CANADA                                  CA         2
      144 MEXICO                                  MX         3
        1 AFGHANISTAN                             AF
        2 ?LAND ISLANDS                           AX
        3 ALBANIA                                 AL
        4 ALGERIA                                 DZ
...........
 249  ZIMBABWE                                    ZW 

SQL> exec :loc:='EU' 
SQL> select c.*, ( select pos from country_sort s 
 where usage = :loc 
 and s.country_code = c.country_code ) pos 
 from country_codes c
    order by pos, country_code# ;

COUNTRY_CODE# COUNTRY_NAME                          CO       POS
------------- ------------------------------------- -- ------------
       15 AUSTRIA                                   AT        27
       22 BELGIUM                                   BE        27
       35 BULGARIA                                  BG        27
       59 CYPRUS                                    CY        27
       60 CZECH REPUBLIC                            CZ        27
       61 DENMARK                                   DK        27
       70 ESTONIA                                   EE        27
       75 FINLAND                                   FI        27
       76 FRANCE                                    FR        27
       83 GERMANY                                   DE        27
       86 GREECE                                    GR        27
      101 HUNGARY                                   HU        27
      107 IRELAND                                   IE        27
      110 ITALY                                     IT        27
      123 LATVIA                                    LV        27
      129 LITHUANIA                                 LT        27
      130 LUXEMBOURG                                LU        27
      138 MALTA                                     MT        27
      157 NETHERLANDS                               NL        27
      177 POLAND                                    PL        27
      178 PORTUGAL                                  PT        27
      182 ROMANIA                                   RO        27
      202 SLOVAKIA                                  SK        27
      203 SLOVENIA                                  SI        27
      209 SPAIN                                     ES        27
      215 SWEDEN                                    SE        27
      235 UNITED KINGDOM                            GB        27
        1 AFGHANISTAN                               AF
...........
     249  ZIMBABWE                                  ZW

So that is cool,  change a variable and change the order.  But looking at the output of the second select, I don’t want UK so far down the list:

insert into country_code ( select conutry_code, 27, 'EU GB First' 
                             from country_code );
update country_sort set pos = 1 where country_code='GB' 
                                  and usage = 'EU GB First' ;

/* now we can get */
exec :loc:='EU GB First'

SQL> select c.*, ( select pos from country_sort s 
                    where usage = :loc 
                      and s.country_code = c.country_code ) pos 
      from country_codes c
    order by pos, country_code# ;

COUNTRY_CODE# COUNTRY_NAME                          CO       POS
------------- ------------------------------------- -- ------------
      235 UNITED KINGDOM                            GB         1
       15 AUSTRIA                                   AT        27
       22 BELGIUM                                   BE        27
....

Now using what we learned about pipelined functions, in the previous article:

CREATE TYPE country_t VARCHAR2(64); 
CREATE OR REPLACE  function ordered_country_list( pOrder VARCHAR2 )
   RETURN country_t  deterministic pipelined AS
  lRet VARCHAR2(64) ;
BEGIN
     FOR  rec IN  (  SELECT  c.*, ( SELECT pos 
                                      FROM country_sort s
                                     WHERE usage =  pOrder
                                       AND  s.country_code = c.country_code ) pos
                       FROM country_codes c
                      ORDER BY pos, country_code# )
      LOOP
         PIPE ROW (rec.country_name);
      END LOOP;
      RETURN ;
END;
/

Calling the ordered_country_list function and passing in the parameter will list the results in the  requested order:

select column_value as country_name 
  from table(  ordered_country_list( 'North America' ) ) ;

This is a real world example.  I have also used this technique for things like prescriptions where brand name are listed above the generic, or  just below.   Control is in the sort table and any number of ways of sorting can be added and used.

 

 

Bookmark the permalink.

Leave a Reply