using ROWNUM TOP-N

Question

why the external fields can´t be used in these subquery?

SELECT d1_cod,
       D1_VUNIT,
  (
    SELECT sd2.d1_vunit from (select d1_filial,d1_vunit,d1_emissao,d1_cod,d1_doc)
      FROM sd1160 sd12
     WHERE sd12.D1_EMISSAO < **x.d1_emissao**
       And sd12.D1_FILIAL = **x.d1_filial**
       And sd12.D1_COD = **x.d1_cod**
       And sd12.D_E_L_E_T_ = ' ' 
  ORDER BY sd12.d1_emissao DESC)
     Where ROWNUM < 2)
      FROM sd1160 x
     WHERE x.D_E_L_E_T_  = ' '
       AND x.d1_emissao  = '20160808'
  ORDER BY x.d1_emissao DESC
  )

Thanks

You're really lucky that some SO users are mind readers, because your question is very, very unclear. What do you mean by *external fields*? Which are they? What happens with your current query? Do you get an error, which one? These are all very relevant details you should always include in your question. Also, your title is misleading as it has nothing to do with your problem. — sstan, Aug 10 '16 at 10:40

Gordon Linoff · Answer 1 · 2016-08-10T12:09:14.860

Oracle only recognizes correlated references one-level deep. One way around this is to use the keep syntax:

SELECT d1_cod,
       D1_VUNIT,
       (SELECT MAX(st12.d1_vunit) KEEP (DENSE_RANK FIRST ORDER BY sd12.d1_emissao DESC)
        FROM sd1160 sd12
        WHERE sd12.D1_EMISSAO < **x.d1_emissao** AND
              sd12.D1_FILIAL = **x.d1_filial** AND
              sd12.D1_COD = **x.d1_cod** AND
              sd12.D_E_L_E_T_ = ' ' 
       )
FROM sd1160 x
WHERE x.D_E_L_E_T_ = ' ' AND x.d1_emissao = '20160808'
ORDER BY x.d1_emissao DESC;

using ROWNUM TOP-N

1 Answers1