Oracle DB SQL Query: Wert einer Tabelle in andere Tabelle übertragen..aber WIE...

madirfan

Erfahrenes Mitglied
Ich habe 2 tabellen..
Tabelle1 = ORDER1, ORDER2, ORDER8, BUILTUP
Tabelle2 = ORDER1, ORDER2, ORDER8,POSTEL_CODE

Um auf den POSTEL_CODE zu kommen, muss ich erst auf der Tabelle1 die ORDER1 bekommen..

Habe diese Query um die ORDER1 von der Tabelle1 zu bekommen..


Code:
Select ahr.*
     , case when fnt1.NAME is null then fne1.NAME else fne1.NAME||' ('||fnt1.NAME||')' end as ORDER1
     , case when fnt2.NAME is null then fne2.NAME else fne2.NAME||' ('||fnt2.NAME||')' end as ORDER2
     , case when fnt3.NAME is null then fne3.NAME else fne3.NAME||' ('||fnt3.NAME||')' end as ORDER8
     , case when fnt4.NAME is null then fne4.NAME else fne4.NAME||' ('||fnt4.NAME||')' end as BUILTUP
  from Tabelle1 ahr
  left join RDF_FEATURE_NAMES fns1 on ahr.ORDER1_ID = fns1.FEATURE_ID and 'N' = fns1.IS_EXONYM and 'B' = fns1.NAME_TYPE and 'A' = fns1.OWNER
  left join RDF_FEATURE_NAME  fne1 on fns1.NAME_ID = fne1.NAME_ID
  left join RDF_FEATURE_NAME_TRANS fnt1 on fns1.NAME_ID = fnt1.NAME_ID
  left join RDF_FEATURE_NAMES fns2 on ahr.ORDER2_ID = fns2.FEATURE_ID and 'N' = fns2.IS_EXONYM and 'B' = fns2.NAME_TYPE and 'A' = fns2.OWNER
  left join RDF_FEATURE_NAME  fne2 on fns2.NAME_ID = fne2.NAME_ID
  left join RDF_FEATURE_NAME_TRANS fnt2 on fns2.NAME_ID = fnt2.NAME_ID
  left join RDF_FEATURE_NAMES fns3 on ahr.ORDER8_ID = fns3.FEATURE_ID and 'N' = fns3.IS_EXONYM and 'B' = fns3.NAME_TYPE and 'A' = fns3.OWNER
  left join RDF_FEATURE_NAME  fne3 on fns3.NAME_ID = fne3.NAME_ID
  left join RDF_FEATURE_NAME_TRANS fnt3 on fns3.NAME_ID = fnt3.NAME_ID
  left join RDF_FEATURE_NAMES fns4 on ahr.BUILTUP_ID = fns4.FEATURE_ID and 'N' = fns4.IS_EXONYM and 'B' = fns4.NAME_TYPE and 'A' = fns4.OWNER
  left join RDF_FEATURE_NAME  fne4 on fns4.NAME_ID = fne4.NAME_ID
  left join RDF_FEATURE_NAME_TRANS fnt4 on fns4.NAME_ID = fnt4.NAME_ID
 where ahr.ISO_COUNTRY_CODE = 'DEU'
;

Leider weiss ich nicht, wie ich jetzt die ORDER1 ID mit der Tabelle2 POSTEL_CODE verbinden kann..

hoffe, ihr könnt helfen..
 
Und wo genau ist denn nun deine Tabelle1? Ist das result set deiner Query = Tabelle1? Ich nehme dann mal an deine Tabelle2 ist auch so eine Query? Wie kannst du denn deine Tabelle1 mit deiner Tabelle2 joinen? Wie hast du das definiert? Über ORDER1, ORDER2 und ORDER8? Angenommen meine Annahmen sind korrekt, dann ca. so:

SQL:
SELECT
--tabelle1.? -- ka. wie deine spalten hier heissen.... ahr.* sagt nicht viel aus
tabelle1.ORDER1,
tabelle1.ORDER2,
tabelle1.ORDER8,
tabelle1.BUILTUP,
tabelle2.POSTEL_CODE
FROM
(
select ahr.* -- das solltest du nicht tun, gib immer genau an welche spalten du dir nimmst, und nimm immer nur die die du auch wirklich brauchst...
     , case when fnt1.NAME is null then fne1.NAME else fne1.NAME||' ('||fnt1.NAME||')' end as ORDER1
     , case when fnt2.NAME is null then fne2.NAME else fne2.NAME||' ('||fnt2.NAME||')' end as ORDER2
     , case when fnt3.NAME is null then fne3.NAME else fne3.NAME||' ('||fnt3.NAME||')' end as ORDER8
     , case when fnt4.NAME is null then fne4.NAME else fne4.NAME||' ('||fnt4.NAME||')' end as BUILTUP
  from Tabelle1 ahr
  left join RDF_FEATURE_NAMES fns1 on ahr.ORDER1_ID = fns1.FEATURE_ID and 'N' = fns1.IS_EXONYM and 'B' = fns1.NAME_TYPE and 'A' = fns1.OWNER
  left join RDF_FEATURE_NAME  fne1 on fns1.NAME_ID = fne1.NAME_ID
  left join RDF_FEATURE_NAME_TRANS fnt1 on fns1.NAME_ID = fnt1.NAME_ID
  left join RDF_FEATURE_NAMES fns2 on ahr.ORDER2_ID = fns2.FEATURE_ID and 'N' = fns2.IS_EXONYM and 'B' = fns2.NAME_TYPE and 'A' = fns2.OWNER
  left join RDF_FEATURE_NAME  fne2 on fns2.NAME_ID = fne2.NAME_ID
  left join RDF_FEATURE_NAME_TRANS fnt2 on fns2.NAME_ID = fnt2.NAME_ID
  left join RDF_FEATURE_NAMES fns3 on ahr.ORDER8_ID = fns3.FEATURE_ID and 'N' = fns3.IS_EXONYM and 'B' = fns3.NAME_TYPE and 'A' = fns3.OWNER
  left join RDF_FEATURE_NAME  fne3 on fns3.NAME_ID = fne3.NAME_ID
  left join RDF_FEATURE_NAME_TRANS fnt3 on fns3.NAME_ID = fnt3.NAME_ID
  left join RDF_FEATURE_NAMES fns4 on ahr.BUILTUP_ID = fns4.FEATURE_ID and 'N' = fns4.IS_EXONYM and 'B' = fns4.NAME_TYPE and 'A' = fns4.OWNER
  left join RDF_FEATURE_NAME  fne4 on fns4.NAME_ID = fne4.NAME_ID
  left join RDF_FEATURE_NAME_TRANS fnt4 on fns4.NAME_ID = fnt4.NAME_ID
 where ahr.ISO_COUNTRY_CODE = 'DEU'
) tabelle1
INNER JOIN
(
-- und hier ist deine zweite query drin...
) tabelle2
ON
(
tabelle1.ORDER1 = tabelle2.ORDER1 
AND
tabelle1.ORDER2 = tabelle2.ORDER2 
AND
tabelle1.ORDER8 = tabelle2.ORDER8
);
 
Zuletzt bearbeitet von einem Moderator:
Zurück