Example of a Complex Query

Use this example to build your own complex queries when no predefined database object exists for the data you want to export.

select rate_geo.*,  \
cursor (select rate_geo_stops.* from rate_geo_stops where rate_geo_stops.rate_geo_gid = rate_geo.rate_geo_gid) as rate_geo_stops,  \
cursor (select rate_geo_accessorial.* from rate_geo_accessorial where rate_geo_accessorial.rate_geo_gid = rate_geo.rate_geo_gid) as rate_geo_accessorial,  \
cursor (select rg_special_service.* from rg_special_service where rg_special_service.rate_geo_gid = rate_geo.rate_geo_gid) as rg_special_service,  \
cursor (select rg_special_service_accessorial.* from rg_special_service_accessorial where rg_special_service_accessorial.rate_geo_gid = rate_geo.rate_geo_gid) as rg_special_service_accessorial, \
cursor (select rate_geo_cost_group.*,   \
cursor (select rate_geo_cost.* ,   \
cursor (select rate_geo_cost_weight_break.*  \
from rate_geo_cost_weight_break  \
where rate_geo_cost_weight_break.rate_geo_cost_seq = rate_geo_cost.rate_geo_cost_seq and rate_geo_cost_weight_break.rate_geo_cost_group_gid = rate_geo_cost.rate_geo_cost_group_gid) as rate_geo_cost_weight_break  \
from rate_geo_cost   \
where rate_geo_cost.rate_geo_cost_group_gid = rate_geo_cost_group.rate_geo_cost_group_gid ) as rate_geo_cost   \
from rate_geo_cost_group   \
where rate_geo.rate_geo_gid = rate_geo_cost_group.rate_geo_gid) as rate_geo_cost_group \
from rate_geo "

The main thing to notice is the use of nested cursors to specify a hierarchical query.