Oracle SQL multiple left joins with duplicate records or "invalid identifier"

Question

I am new to SQL and have done some joins before, but nothing complicated like this.

Basically, I am trying to combine many tables to show general data for reporting. The main table gwhere I would like to see the level of detail. ghas a one-to-many relationship with all tables attached to it. Would delete GROUP BYthese duplicates?

I tried using SELECT DISTINCT, but I'm not sure if this is the best solution.

Here is the SQL result that I am running now ORA-00904: "a"."akey": invalid identifier.

SELECT 
a.afield123 ORDER_QTY,
b.bfield123 MANUF,
c.cfield123 ORDER_DATE,
d.dfield123 PATIENT_CREATE_DATE,
e.efield123 PAYOR_CLASS,
f.ffield123 ID

FROM

schema123.a12345            a,
schema123.b12345            b,
schema123.c12345            c,
schema123.d12345            d,
schema123.e12345            e,
schema123.f12345            f,

LEFT JOIN schema123.g12345  g ON     
(a.akey = b.akey AND
 c.ckey = d.ckey AND 
 e.ekey = f.ekey)

Here are some sample data.

Table

G (main table) Primary Key: gfield123 Foreign Keys: akey,bkey,ckey,dkey,ekey,fkey,hkey

gfield123   gfield124   akey    bkey    ckey    dkey    ekey    fkey   hkey
1           8/1/2017    1234    2234    3234    4234    5234    6234   7234

Table Primary Key: akey Foreign Keys: gfield123, bkey

akey    afield123   gfield123   bkey        
1234    20          1           2234        
1235    40          1           2234        

Table

B Primary Key: bkey Foreign Keys: gfield123, ckey, akey

bkey    bfield123   gfield123 akey  ckey    
2234    COMPANY1    1         1234  3234    

Table

C Primary Key: ckey Foreign Keys: gfield123, bkey, dkey

ckey    cfield123   gfield123   bkey    dkey    
3234    8/5/2017    1           2234    4234     

Table

D Primary Key: dkey Foreign Keys: gfield123, ckey, ekey

dkey    dfield123       gfield123   ckey    ekey    
4234    7/1/2017        1           3234    5234    

Table

E Primary Key: ekey Foreign Keys: gfield123, fkey

ekey    efield123       gfield123   fkey
5234    MEDICARE        1           6234 

Table

F Primary Key: fkey Foreign Key: gfield123

fkey    ffield123         gfield123 
6234    ABC123            1         

Table h Primary Key: hkey

hkey    hfield123           
7234    9999                   

Expected Result

gfield123   afield123   bfield123 cfield123 efield123 ffield123 hfield123
1           60          COMPANY1  8/5/2017  MEDICARE  ABC123    9999    

: SQL, ,

Answer 1

, G , a-b-c-d-e-f,

, . Distinct , , , .

SELECT sum(a.afield123) as ORDER_QTY
     , b.bfield123 as MANUF
     , c.cfield123 as ORDER_DATE
     , d.dfield123 as PATIENT_CREATE_DATE
     , e.efield123 as PAYOR_CLASS
     , f.ffield123 as ID
     , g.gfield123 as gkey
     , g.hfield123 as hField123
FROM schema123.g12345 g
LEFT JOIN schema123.a12345 a
  ON g.gkey = a.gkey
LEFT JOIN schema123.b12345 b
  ON a.akey = b.akey
 AND g.gkey = b.gkey
LEFT JOIN schema123.c12345 c
  ON b.bkey = c.bkey
 AND g.gkey = c.gkey
LEFT JOIN schema123.d12345 d
  ON c.ckey = d.ckey
 AND g.gkey = d.gkey
LEFT JOIN schema123.e12345 e
  ON d.dkey = e.dkey
 AND g.gkey = g.gkey
LEFT JOIN schema123.f12345 f
  ON e.ekey = f.ekey
 AND g.gkey = g.gkey
LEFT JOIN schema123.f12345 
  on g.hkey = h.hkey
GROUP BY , b.bfield123 as MANUF
     , c.cfield123 as ORDER_DATE
     , d.dfield123 as PATIENT_CREATE_DATE
     , e.efield123 as PAYOR_CLASS
     , f.ffield123 as ID
     , g.gfield123 as gkey
     , g.hfield123 as hField123

,

• from the the 89 . , 30 92, / .
• g.field123 gkey , , ?
• g from ( , G , , ?
• on .
• , , select.
• , .
• join g.gkey = a.akey, , g .
• WHERE, g.
• , AND , and d.dfield123 >= to_date('2017-01-01','YYYY-MM-DD'), , 2017 .