Oracle SQL using while loop to select records

Question

I am trying to write a sql for a table which storing geographic location data. That table has 4 columns - location id, parent id, display name and location type. Parent id will link with another record's location id in the same table and it may be null.

Table Structure

location_id	parent_id	display_name	location_type
1	null	United States	Country
2	1	Texas, United States	States
3	2	Amarillo, Texas	City
4	null	Hong Kong	Country

And I want to generate a report which have 3 columns: locationName,States,Country, the logic is like : while the record have a parent record, put its display name to States/Country Column based on its location type. But I have no idea how to do it using oracle sql.

May someone help me with this?

enter image description here

locationName	States	Country
United States	null	United States
Texas, United States	Texas, United States	United States
Amarillo, Texas	Texas, United States	United States
Hong Kong	null	Hong Kong

The logic will be like :

while(record still have parent id){
    if(location type = "States"){
       States column value = record's display_name
    }
    if(location type = "Country"){
       Country column value = record's display_name
    }
}

It sounds like you just want a simple inner join on the parentId. This will only return records in both table. — Icemanind
– Icemanind, Commented Mar 14, 2022 at 16:15
@Icemanind Hi Icemanind, thanks for your reply, but there are some records have no parent id, so I cannot use inner join. And I tried outer join also, but I don't know how to select the value for States and Country column based on the location type. — Dennis Chan
– Dennis Chan, Commented Mar 14, 2022 at 16:33

Peter Lang · Accepted Answer · 2022-03-14 17:27:15Z

1

Here is an approach using a recursive query. Fiddle borrowed from Utsav's answer.

CONNECT_BY_ROOT allows to get attributes of the row used to start the recursion, so we can group by that. CASE returns NULL if the condition is not met which is then ignored by MIN.

SELECT root_display_name,
       MIN(CASE WHEN location_type = 'States'  THEN display_name END) AS states,
       MIN(CASE WHEN location_type = 'Country' THEN display_name END) AS country
FROM
(
  SELECT CONNECT_BY_ROOT(location_id) AS root_location_id,
         CONNECT_BY_ROOT(display_name) AS root_display_name,
         t.*
  FROM t
  CONNECT BY PRIOR parent_id = location_id
)
GROUP BY root_location_id, root_display_name
ORDER BY root_location_id

answered Mar 14, 2022 at 17:27

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Utsav · Accepted Answer · 2022-03-14 16:58:33Z

It would be easier to do it via recursive query, but in this case, as joining level in 3, it could be done with a series of self-left joins.

Note the subqueries below used. First subquery s is deriving state at level 1. Second subquery c is deriving country at level 2. Third c1 is deriving cities' country , with join with s, which would be at level 3. Then with case statements, you can get the desired result.

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e22239082df9132b0c0790c8d1b8ff0e

select 
display_name as location_name
,case when 
    s.state_id is null and location_type='States' 
        then gl.display_name 
    else s.state_name
end as states
,case when
    c.country_id is null and location_type='Country' and gl.display_name is not null
        then gl.display_name
    when c.country_name is not null 
        then c.country_name
    else c1.country_name end as country
from gl
left join 
    (select location_id as state_id,parent_id as country_id,display_name as state_name
    from gl where location_type='States'
    ) s 
on gl.parent_id=s.state_id
left join 
    (select location_id as country_id,display_name as country_name
    from gl where location_type='Country'
    ) c
on gl.parent_id=c.country_id
left join 
    (select location_id as country_id,display_name as country_name
    from gl where location_type='Country'
    ) c1
on s.country_id=c1.country_id
order by gl.location_id

Collectives™ on Stack Overflow

Oracle SQL using while loop to select records

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related