Hallo,
ich hab da eine frage.. da ich absolut keine idee haben, wie ich das aufbauen bzw anstellen soll.
Im ersten Code "CODE1" wird mir mir meine ZONE_ID, zne.ZONE_TYPE, zne.ADMIN_PLACE_ID etc dargestellt.
Ich möchte hier gerne eine kalkulation staten.. die für alle ISO_COUNTRY_CODEs jedes ZONE_TYP die ZONE_ID zusammenkalkuliert..
Beispiel:
ISO_COUNTRY_CODE = DEU
ZONE_TYP_KA = 11111
ZONE_TYP_KD = 22222
ISO_COUNTRY_CODE = ESP
ZONE_TYP_KA = 33333
ZONE_TYP_KD = 444444 etc..
Und diese Query müsste im CODE2 implementiert werden..
Die Referenz vom CODE2 zu CODE1 ist ISO_COUNTRY_CODE..
Irgend welche Ideen..
CODE1
CODE2
ich hab da eine frage.. da ich absolut keine idee haben, wie ich das aufbauen bzw anstellen soll.
Im ersten Code "CODE1" wird mir mir meine ZONE_ID, zne.ZONE_TYPE, zne.ADMIN_PLACE_ID etc dargestellt.
Ich möchte hier gerne eine kalkulation staten.. die für alle ISO_COUNTRY_CODEs jedes ZONE_TYP die ZONE_ID zusammenkalkuliert..
Beispiel:
ISO_COUNTRY_CODE = DEU
ZONE_TYP_KA = 11111
ZONE_TYP_KD = 22222
ISO_COUNTRY_CODE = ESP
ZONE_TYP_KA = 33333
ZONE_TYP_KD = 444444 etc..
Und diese Query müsste im CODE2 implementiert werden..
Die Referenz vom CODE2 zu CODE1 ist ISO_COUNTRY_CODE..
Irgend welche Ideen..
CODE1
Code:
Select /*+ PARALLEL(4) */ zne.ZONE_ID, zne.ZONE_TYPE, zne.ADMIN_PLACE_ID, zne.government_code GVTC
, plz.ADMIN_PLACE_ID PLACE_ZONE
, NVL(nmt.NAME, nme.NAME) ZN_NAME_DIRECT
, NVL(amt.NAME, ame.NAME) ZN_NAME_ADM
, ahr.ISO_COUNTRY_CODE ISO
, ahr.ADMIN_ORDER ORD
, ahr.ORDER8_ID
, NVL(o8mt.NAME, o8me.NAME) o8_NAME
, ahr.BUILTUP_ID
, NVL(bumt.NAME, bume.NAME) bu_NAME
from RDF_ZONE zne
left join RDF_PLACE_ZONE plz on zne.ZONE_ID = plz.ZONE_ID
left join RDF_ADMIN_HIERARCHY ahr on plz.ADMIN_PLACE_ID = ahr.ADMIN_PLACE_ID
left join RDF_FEATURE_NAMES zns on zne.ZONE_ID = zns.FEATURE_ID and zns.IS_EXONYM = 'N' and zns.NAME_TYPE = 'B' and zns.OWNER = 'Z'
left join RDF_FEATURE_NAME nme on zns.NAME_ID = nme.NAME_ID
left join RDF_FEATURE_NAME_TRANS nmt on zns.NAME_ID = nmt.NAME_ID
left join RDF_FEATURE_NAMES ans on zne.ADMIN_PLACE_ID = ans.FEATURE_ID and ans.IS_EXONYM = 'N' and ans.NAME_TYPE = 'B' and zns.OWNER = 'A'
left join RDF_FEATURE_NAME ame on ans.NAME_ID = ame.NAME_ID
left join RDF_FEATURE_NAME_TRANS amt on ans.NAME_ID = amt.NAME_ID
left join RDF_FEATURE_NAMES buns on ahr.BUILTUP_ID = buns.FEATURE_ID and buns.IS_EXONYM = 'N' and buns.NAME_TYPE = 'B' and buns.OWNER = 'A'
left join RDF_FEATURE_NAME bume on buns.NAME_ID = bume.NAME_ID
left join RDF_FEATURE_NAME_TRANS bumt on buns.NAME_ID = bumt.NAME_ID
left join RDF_FEATURE_NAMES o8ns on ahr.ORDER8_ID = o8ns.FEATURE_ID and o8ns.IS_EXONYM = 'N' and o8ns.NAME_TYPE = 'B' and o8ns.OWNER = 'A'
left join RDF_FEATURE_NAME o8me on o8ns.NAME_ID = o8me.NAME_ID
left join RDF_FEATURE_NAME_TRANS o8mt on o8ns.NAME_ID = o8mt.NAME_ID
order by ahr.ISO_COUNTRY_CODE
, zne.ZONE_TYPE
, zne.ZONE_ID
;
-- Admin Type/Level/Order/Feature Type to name mapping
Select /*+ PARALLEL(4) */ distinct
ahr.ISO_COUNTRY_CODE ISO
, ahr.ADMIN_ORDER
, apl.ADMIN_LEVEL
, apl.ADMIN_TYPE
, case apl.ADMIN_LEVEL
when 1 then ctr.admin_level_admin_type_1
when 2 then ctr.admin_level_admin_type_2
when 3 then ctr.admin_level_admin_type_3
when 4 then ctr.admin_level_admin_type_4
when 5 then ctr.admin_level_admin_type_5
when 6 then ctr.admin_level_admin_type_6
when 7 then ctr.admin_level_admin_type_7
end as ADMIN_LEVEL_TYPE
, case apl.ADMIN_LEVEL
when 1 then ctr.ADMIN_LEVEL_DESCRIPTION_1
when 2 then ctr.ADMIN_LEVEL_DESCRIPTION_2
when 3 then ctr.ADMIN_LEVEL_DESCRIPTION_3
when 4 then ctr.ADMIN_LEVEL_DESCRIPTION_4
when 5 then ctr.ADMIN_LEVEL_DESCRIPTION_5
when 6 then ctr.ADMIN_LEVEL_DESCRIPTION_6
when 7 then ctr.ADMIN_LEVEL_DESCRIPTION_7
end as ADMIN_LEVEL_DESCRIPTION
, crt.FEATURE_TYPE
, met.ATTR_DESCRIPTION
from RDF_ADMIN_HIERARCHY ahr
inner join RDF_ADMIN_PLACE apl on ahr.ADMIN_PLACE_ID = apl.ADMIN_PLACE_ID
inner join RDF_COUNTRY ctr on ahr.COUNTRY_ID = ctr.COUNTRY_ID
left join RDF_CARTO crt on ahr.ADMIN_PLACE_ID = crt.NAMED_PLACE_ID
left join RDF_META met on crt.FEATURE_TYPE = met.ATTRIBUTE and 'RDF_CARTO' = met.TABLE_NAME and 'FEATURE_TYPE' = met.COLUMN_NAME
-- where ahr.ISO_COUNTRY_CODE = 'BRA'
order by ahr.ISO_COUNTRY_CODE, ahr.ADMIN_ORDER
;
CODE2
Code:
SELECT
ahr.iso_country_code,
pa.cnt_postal_code,
COUNT(DISTINCT ahr.order1_id) AS order1_1112,
COUNT(DISTINCT ahr.order2_id) AS order2_1113,
COUNT(DISTINCT ahr.order8_id) AS order8_city,
COUNT(DISTINCT ahr.BUILTUP_ID) AS BUILTUP_ID
FROM
rdf_admin_hierarchy ahr,
(SELECT country_id,
COUNT(DISTINCT pa.postal_code) AS cnt_postal_code
FROM rdf_postal_area pa
GROUP BY country_id) pa
WHERE
ahr.country_id = pa.country_id
GROUP BY
ahr.iso_country_code,
pa.cnt_postal_code