Geek Answers Handbook

SQL ANSI integrates and the order of the tables in it

The following query automatically translates from the "old" syntax to ANSI syntax and gives an error:

select *    
  from ods_trf_pnb_stuf_lijst_adrsrt2 lst    
  join ods_stg_pnb_stuf_pers_adr pas    
    on (pas.soort_adres = lst.soort_adres)    
 right outer join ods_stg_pnb_stuf_pers_nat nat    
    on (prs.id = nat.prs_id)                     <<<prs.id invalid identifier
      join ods_stg_pnb_stuf_adr adr
        on (adr.id = pas.adr_id)
      join ods_stg_pnb_stuf_np prs
        on (prs.id = pas.prs_id)

I think this is because the prs table is referenced before it was declared. Moving the prs connection to the request solves the problem:

select *
  from ods_trf_pnb_stuf_lijst_adrsrt2 lst
  join ods_stg_pnb_stuf_pers_adr pas
    on (pas.soort_adres = lst.soort_adres)
  join ods_stg_pnb_stuf_np prs               <<< this first
    on (prs.id = pas.prs_id)
 right outer join ods_stg_pnb_stuf_pers_nat nat
    on (prs.id = nat.prs_id)                 <<< now prs.id is known
  join ods_stg_pnb_stuf_adr adr
    on (adr.id = pas.adr_id)
 where lst.persoonssoort = 'PERSOON'
   and pas.einddatumrelatie is null

Is there a way to write this query so that the order is less strict while still using ANSI syntax?

0
source share
4 answers

If a broken request was generated by a tool from an old syntax other than ANSI, the tools generate broken code. However, using ANSI joins should produce the same result regardless of the order of the tables in the from clause. it

select *
from      t1
join      t2 on t2.id = t1.id
left join t3 on t3.id = t1.id

( )

select *
from      t1
left join t3 on t3.id = t1.id
join      t2 on t2.id = t1.id

, from , , . , / from, -, . ,

select *
from       t3
right join t1 on t1.id = t3.id
join       t2 on t2.id = t1.id
+3

, . . ?

+3

( "INNER" ) JOIN

 SELECT ...
 FROM a
 JOIN b ON (a.x = b.y)

SELECT WHERE

SELECT ...
FROM a, b
WHERE a.x = b.y

// - "" .

0

, SQL ,

select *
from  ods_trf_pnb_stuf_lijst_adrsrt2 lst
    , ods_stg_pnb_stuf_pers_adr pas
    , ods_stg_pnb_stuf_pers_nat nat
    , ods_stg_pnb_stuf_adr adr
    , ods_stg_pnb_stuf_np prs
where 
    pas.soort_adres = lst.soort_adres
and prs.id(+) = nat.prs_id
and adr.id = pas.adr_id
and prs.id = pas.prs_id
and lst.persoonssoort = 'PERSOON'
and pas.einddatumrelatie is null

ods_stg_pnb_stuf_np prs from, Oracle, ANSI SQL, prs , . , Oracle ANSI SQL.

Oracle ANSI SQL:

  • .
  • , .

If your colleague needs to rewrite Oracle's proprietary joins to ANSI SQL syntax, the demos (in both java and C #) listed in this article should be helpful.

0
source

More articles:


All Articles