1 2 Previous Next 25 Replies Latest reply: Jul 30, 2012 1:20 AM by Venkadesh Raja RSS

    SQL Help: display country/state/city structure

    952498
      Hi all,

      I need to write a query to generate county/state/city structure.

      Exiting table Example:
      COUNTRY          STATE          CITY          NAME
      1          0          0          US
      1          1          0          NY
      1          1          1          NYC
      1          1          2          Long Island
      1          2          0          CA
      1          2          1          San Francisco
      2          0          0          Canada
      2          1          0          Ontario
      2          1          1          Toronto
      2          2          0          Qubec
      2          2          1          Montreal
                                    

                                    
      New Table structure:

      country          county name          state          state name          city          city name
      1          US          0                    0          
      1          US          1          NY          0          
      1          US          1          NY          1          NYC
      1          US          1          NY          2          Long Island
      1          US          2          CA          0          
      1          US          2          CA          1          San Francisco
      2          Canada          0                    0          
      2          Canada          1          Ontario          0          
      2          Canada          1          Ontario          1          Toronto
      2          Canada          2          Qubec          0          
      2          Canada          2          Qubec          1          Montreal


      Since this reference table has a lot of records, like 50,000, what's the most efficient way to write it? I tried self join, it's taking a long time. I am using Oracle 11g.

      Thanks,
      Jane
        • 1. Re: SQL Help: display country/state/city structure
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: SQL Help: display country/state/city structure
            Solomon Yakobson
            with sample_data as (
                                 select 1 country,0 state,0 city,'US' name from dual union all
                                 select 1,1,0,'NY' from dual union all
                                 select 1,1,1,'NYC' from dual union all
                                 select 1,1,2,'Long Island' from dual union all
                                 select 1,2,0,'CA' from dual union all
                                 select 1,2,1,'San Francisco' from dual union all
                                 select 2,0,0,'Canada' from dual union all
                                 select 2,1,0,'Ontario' from dual union all
                                 select 2,1,1,'Toronto' from dual union all
                                 select 2,2,0,'Qubec' from dual union all
                                 select 2,2,1,'Montreal' from dual
                                )
            select  country,
                    case
                      when state = 0 then name
                      else (select name from sample_data t2 where t2.country = t1.country and t2.state = 0)
                    end country_name,
                    state,
                    case
                      when state = 0 then null
                      when city = 0 then name
                      else (select name from sample_data t2 where t2.country = t1.country and t2.state = t1.state and t2.city = 0)
                    end state_name,
                    city,
                    case
                      when city = 1 then name
                    end city_name
              from  sample_data t1
            /
            
               COUNTRY COUNTRY_NAME       STATE STATE_NAME          CITY CITY_NAME
            ---------- ------------- ---------- ------------- ---------- -------------
                     1 US                     0                        0
                     1 US                     1 NY                     0
                     1 US                     1 NY                     1 NYC
                     1 US                     1 NY                     2
                     1 US                     2 CA                     0
                     1 US                     2 CA                     1 San Francisco
                     2 Canada                 0                        0
                     2 Canada                 1 Ontario                0
            
               COUNTRY COUNTRY_NAME       STATE STATE_NAME          CITY CITY_NAME
            ---------- ------------- ---------- ------------- ---------- -------------
                     2 Canada                 1 Ontario                1 Toronto
                     2 Canada                 2 Qubec                  0
                     2 Canada                 2 Qubec                  1 Montreal
            
            11 rows selected.
            
            SQL> 
            SY.
            • 3. Re: SQL Help: display country/state/city structure
              Purvesh K
              Are you looking for something like below?
              drop table test_table;
              create table test_table as
              with data(country, state, city, name) as
              (
                select 1, 0, 0, 'US' from dual union all
                select 1, 1, 0, 'NY' from dual union all
                select 1, 1, 1, 'NYC' from dual union all
                select 1, 1, 2, 'Long Island' from dual union all
                select 1, 2, 0, 'CA' from dual union all
                select 1, 2, 1, 'San Francisco' from dual union all
                select 2, 0, 0, 'Canada' from dual union all
                select 2, 1, 0, 'Ontario' from dual union all
                select 2, 1, 1, 'Toronto' from dual union all
                select 2, 2, 0, 'Qubec' from dual union all
                select 2, 2, 1, 'Montreal' from dual
              )
              select country, decode(country, 1, 'US', 2, 'Canada') country_name,
                     state, case when country = 1 and state = 1
                                  then 'NY'
                                 when country = 1 and state = 2
                                  then 'CA'
                                 when country = 2 and state = 1
                                  then 'Ontario'
                                 when country = 2 and state = 2
                                  then 'Qubec'
                            end state_name,
                     city, case when country = 1 and state = 1 and city = 1
                                  then 'NYC'
                                when country = 1 and state = 1 and city = 2
                                  then 'Long Island'
                                when country = 1 and state = 2 and city = 1
                                  then 'San Francisco'
                                when country = 2 and state = 1 and city = 1
                                  then 'Toronto'
                                when country = 2 and state = 2 and city = 1
                                  then 'Montreal'
                            end city_name
                from data;
              Please follow Sb's hint to read the sticky thread by BluShadow and provide the relevant details.
              • 4. Re: SQL Help: display country/state/city structure
                952498
                Hi Solomon and Purvesh,

                Thanks for the quick response. I appreciated it.

                The old table has many countries, and for each county, there are many states, and for each state there are many cities. I actually forget, there is another level for COUNTY;

                When there are lots of records in the table and lots of joins, performance wise, would it be any issue?

                Thanks,
                Jane
                • 5. Re: SQL Help: display country/state/city structure
                  Purvesh K
                  As you said in Original Post, table would contain around 50K records; It isn't a really huge table considering processing capabilities of Oracle.

                  However, you are surely going to have a tough task ahead to segregate the Data based on Country, State, City etc. by having to add Case constructs. ;)

                  Edited by: Purvesh K on Jul 28, 2012 1:33 AM


                  I would suggest you to Create a Master Table roughly similar to below:
                  master_table
                  (
                    country_code  number,
                    country_name varchar2(50),
                    state_code     number,
                    state_name    varchar2(50),
                    city_code       number,
                    city_name      varchar2(50)
                  )
                  You will have to spend initial time to prepare data for the table, but it will definitely be helpful to populate the data into your New Table Structure, as you will eliminate the need to add CASE/Decode constructs in the Select query and you will have to merely join the Master table with the Old Table.

                  Edited by: Purvesh K on Jul 28, 2012 1:34 AM
                  • 6. Re: SQL Help: display country/state/city structure
                    952498
                    So what other options are there beside using CASE statement?
                    • 7. Re: SQL Help: display country/state/city structure
                      Purvesh K
                      949495 wrote:
                      So what other options are there beside using CASE statement?
                      Please read my previous post, I was editing it while you posted this. :)
                      • 8. Re: SQL Help: display country/state/city structure
                        sb92075
                        949495 wrote:
                        Hi Solomon and Purvesh,

                        Thanks for the quick response. I appreciated it.

                        The old table has many countries, and for each county, there are many states, and for each state there are many cities. I actually forget, there is another level for COUNTY;
                        post actual DDL for existing table
                        • 9. Re: SQL Help: display country/state/city structure
                          Solomon Yakobson
                          949495 wrote:
                          The old table has many countries, and for each county, there are many states, and for each state there are many cities. I actually forget, there is another level for COUNTY;
                          It shouldn't be a problem, assuming country,state,county,city,name is indexed:
                          SQL> create table tbl as
                            2                       select 1 country,0 state,0 city,'US' name from dual union all
                            3                       select 1,1,0,'NY' from dual union all
                            4                       select 1,1,1,'NYC' from dual union all
                            5                       select 1,1,2,'Long Island' from dual union all
                            6                       select 1,2,0,'CA' from dual union all
                            7                       select 1,2,1,'San Francisco' from dual union all
                            8                       select 2,0,0,'Canada' from dual union all
                            9                       select 2,1,0,'Ontario' from dual union all
                           10                       select 2,1,1,'Toronto' from dual union all
                           11                       select 2,2,0,'Qubec' from dual union all
                           12                       select 2,2,1,'Montreal' from dual
                           13  /
                          
                          Table created.
                          
                          SQL> create index tbl_idx1
                            2    on tbl(country,state,city,name)
                            3  /
                          
                          Index created.
                          
                          SQL> exec dbms_stats.gather_table_stats('SCOTT','TBL');
                          
                          PL/SQL procedure successfully completed.
                          
                          SQL> explain plan for
                            2  select  country,
                            3          case
                            4            when state = 0 then name
                            5            else (select name from tbl t2 where t2.country = t1.country and t2.state = 0)
                            6          end country_name,
                            7          state,
                            8          case
                            9            when state = 0 then null
                           10            when city = 0 then name
                           11            else (select name from tbl t2 where t2.country = t1.country and t2.state = t1.state and t2.city = 0)
                           12          end state_name,
                           13          city,
                           14          case
                           15            when city = 1 then name
                           16          end city_name
                           17    from  tbl t1
                           18  /
                          
                          Explained.
                          
                          SQL> @?\rdbms\admin\utlxpls
                          
                          PLAN_TABLE_OUTPUT
                          --------------------------------------------------------------------------------------------------------------------
                          Plan hash value: 3989462035
                          
                          ------------------------------------------------------------------------------
                          | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                          ------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT  |          |    11 |   165 |     3   (0)| 00:00:01 |
                          |*  1 |  INDEX RANGE SCAN | TBL_IDX1 |     1 |    13 |     1   (0)| 00:00:01 |
                          |*  2 |  INDEX RANGE SCAN | TBL_IDX1 |     1 |    15 |     1   (0)| 00:00:01 |
                          
                          PLAN_TABLE_OUTPUT
                          --------------------------------------------------------------------------------------------------------------------
                          |   3 |  TABLE ACCESS FULL| TBL      |    11 |   165 |     3   (0)| 00:00:01 |
                          ------------------------------------------------------------------------------
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             1 - access("T2"."COUNTRY"=:B1 AND "T2"."STATE"=0)
                             2 - access("T2"."COUNTRY"=:B1 AND "T2"."STATE"=:B2 AND "T2"."CITY"=0)
                          
                          16 rows selected.
                          
                          SQL> 
                          SY.
                          • 10. Re: SQL Help: display country/state/city structure
                            952498
                            create table location_old (
                            country_id number,
                            state_id number,                         
                            city_id number,                                                                 
                            name varchar2(100))                         
                            /                          

                            --just an example               
                            insert into location_old values (1, 0, 0, 'US');                         
                            insert into location_old values (1, 1, 0, 'CA');                         
                            insert into location_old values (1, 1, 1, 'San Francisco');                         
                            insert into location_old values (1, 1, 2, 'San Diego');                         
                                                     

                            create table location_new (
                            country_id number,
                            country_name varchar2(100))                         
                            state_id number,                    
                            state_name varchar2(100))                              
                            city_id number,                              
                            city_name varchar2(100))                                                            
                            )                         
                            /


                            Jane
                            • 11. Re: SQL Help: display country/state/city structure
                              Solomon Yakobson
                              Actually, city calc is wrong. Should be:
                              when city != 0 then name
                              not
                              when city = 1 then name
                              SY.
                              • 12. Re: SQL Help: display country/state/city structure
                                Solomon Yakobson
                                I overcomplicated it. All you need is analytic first_value:
                                select  country,
                                        case
                                          when state = 0 then name
                                          else first_value(name) over(partition by country order by state)
                                        end country_name,
                                        state,
                                        case
                                          when state = 0 then null
                                          when city = 0 then name
                                          else first_value(name) over(partition by country,state order by city)
                                        end state_name,
                                        city,
                                        case
                                          when city != 0 then name
                                        end city_name
                                  from  tbl t1
                                  order by country,
                                           state,
                                           city
                                /
                                
                                   COUNTRY COUNTRY_NAME       STATE STATE_NAME          CITY CITY_NAME
                                ---------- ------------- ---------- ------------- ---------- -------------
                                         1 US                     0                        0
                                         1 US                     1 NY                     0
                                         1 US                     1 NY                     1 NYC
                                         1 US                     1 NY                     2 Long Island
                                         1 US                     2 CA                     0
                                         1 US                     2 CA                     1 San Francisco
                                         2 Canada                 0                        0
                                         2 Canada                 1 Ontario                0
                                
                                   COUNTRY COUNTRY_NAME       STATE STATE_NAME          CITY CITY_NAME
                                ---------- ------------- ---------- ------------- ---------- -------------
                                         2 Canada                 1 Ontario                1 Toronto
                                         2 Canada                 2 Qubec                  0
                                         2 Canada                 2 Qubec                  1 Montreal
                                
                                11 rows selected.
                                
                                SQL> 
                                SY.
                                • 13. Re: SQL Help: display country/state/city structure
                                  952498
                                  Soloman,

                                  thanks.

                                  So for curiosity, right now this table only has 2 or 3 levels, if the table hierarchy structure has many levels, let's say 10 levels, would you still using this approach?
                                  • 14. Re: SQL Help: display country/state/city structure
                                    Frank Kulash
                                    Hi,

                                    Here's a different approach, using the analytic LAST_VALUE function:
                                    WITH     got_names     AS
                                    (
                                         SELECT     tbl.*
                                         ,     CASE WHEN state  = 0 THEN place_name END     AS country_name
                                         ,     CASE WHEN state  = 0 THEN NULL
                                                   WHEN city   = 0 THEN place_name END     AS state_name
                                         ,     CASE WHEN city   = 0 THEN NULL
                                                                    ELSE place_name END     AS city_name
                                         FROM     tbl
                                    --     WHERE     ...     -- If you need any filtering, this is where it goes
                                    )
                                    SELECT       country
                                    ,       LAST_VALUE (country_name IGNORE NULLS) 
                                               OVER ( ORDER BY      country, state, city)     AS country_name
                                    ,       state
                                    ,       LAST_VALUE (state_name   IGNORE NULLS) 
                                               OVER ( PARTITION BY  country
                                                         ORDER BY                   state, city)     AS state_name
                                    ,       city
                                    ,       LAST_VALUE (city_name    IGNORE NULLS) 
                                               OVER ( PARTITION BY  country, state
                                                         ORDER BY                          city)     AS city_name
                                    FROM       got_names
                                    ORDER BY  country
                                    ,       state
                                    ,            city
                                    ;
                                    Output:
                                    COUNTRY COUNTRY_NAME  STATE STATE_NAME    CITY CITY_NAME
                                    ------- ------------- ----- ------------- ---- -------------
                                          1 US                0                  0
                                          1 US                1 NY               0
                                          1 US                1 NY               1 NYC
                                          1 US                1 NY               2 Long Island
                                          1 US                2 CA               0
                                          1 US                2 CA               1 San Francisco
                                          2 Canada            0                  0
                                          2 Canada            1 Ontario          0
                                          2 Canada            1 Ontario          1 Toronto
                                          2 Canada            2 Qubec            0
                                          2 Canada            2 Qubec            1 Montreal
                                    I used Solomon's sample data, but I called the VARCHAR2 column place_name, because NAME is an Oracle keyword, and therefore not a good column name.
                                    You can easily add county.
                                    949495 wrote:
                                    So for curiosity, right now this table only has 2 or 3 levels, if the table hierarchy structure has many levels, let's say 10 levels, would you still using this approach?
                                    No. I would use the Adjacency Model , like the manger-employee relationship in scott.emp, and use CONNECT BY (or, if you have Oracle 11.2, maybe a recursive WITH clause) to get the results. You can easily add or remove levels, and the exact same queries can run with any number of levels.
                                    CONNECT BY may not perform as well, so that may affect the table design.

                                    Edited by: Frank Kulash on Jul 27, 2012 5:07 PM
                                    I was typing this message when Solomon posted the FIRST_VALUE solution.
                                    1 2 Previous Next