CODEWITHSUNDEEP
sqloracle-database
Know-nothing
Know-nothing

Reputation: 132

The right order of join - build up a new query

So I have 2 working sub-queries:

Query #1:

select aa.kod_id, aa.rid_ct_a, aa.dodavatel 
from 
    (select pl.dodavatel, po.kod_id, po.rid_ct_a 
     from prijemky_list pl
     join prijemky_obsah po on pl.rid = po.rid_o
     where pl.datum_p >= trunc(sysdate) - 365 
       and pl.dodavatel not in (100, 330, 522) 
       and pl.storno = 0
       and not exists (select 1 
                       from prijemky_list pl 
                       join kody_oper on skratka = pl.kod_o
                       where id_cis = 3 and typ_skl_poh = 8) 
   group by 
       pl.dodavatel, po.kod_id, po.rid_ct_a, pl.datum_p 
   order by 
       pl.datum_p desc) aa

Query #2:

select bb.partner, bb.kod_id, bb.cislo_e, bb.rid_obj 
from
    (select ll.partner, o.kod_id, o.rid_obj, ll.cislo_e 
     from ct_l l
     join ct_o o on l.rid = o.rid_o
     join ct_l ll on substr(l.cislo_e, 1, 5) = ll.cislo_e) bb

I want to create a new query with a LEFT JOIN:

NVL((select decode(op.blizsia_spec, null,  'COOP AKCIA', op.blizsia_spec)||' /'||cislo_e  from (

 --**FIRST QUERY**

LEFT JOIN

 --**SECOND QUERY**

ON aa.dodavatel=bb.partner and bb.kod_id=aa.kod_id bb.rid_obj=aa.rid_ct_a ) 
cc

JOIN obch_partneri op    ON cc.partner = op.id 
where rownum = 1),'bežný nákup')

But I get an error:

invalid identifier

Can somebody build up this query correctly?

Upvotes: 0

Views: 38

Answers (1)

Littlefoot
Littlefoot

Reputation: 142873

To me, it looks like

WITH
   tfirst AS (SELECT whatever FROM first_query),
   tsecond AS (SELECT whatever FROM second_query)
SELECT NVL (
             DECODE (op.blizsia_spec, NULL, 'COOP AKCIA', op.blizsia_spec)
          || ' /'
          || cislo_e,
          'bežný nákup') result
  FROM tfirst aa
       LEFT JOIN tsecond bb
          ON     aa.dodavatel = bb.partner
             AND bb.kod_id = aa.kod_id
             AND bb.rid_obj = aa.rid_ct_a
       JOIN obch_partneri op ON bb.partner = op.id

Upvotes: 1

Related Questions