not able to do a UNION in Oracle SQL

Question

i want to do a union of a query but getting error.

SELECT 
  NB.NETBANKID,
  NB.BANKNAME,
  NBMAP.SORTORDER,
  NB.BANKCODE
FROM VTSMNETBNK NB
INNER JOIN CGCMN.VTMESTRNETBNKMAP NBMAP
ON NBMAP.NETBANKID =NB.NETBANKID
WHERE NBMAP.STOREID=133 AND 
NBMAP.EFFDATE <= SYSDATE
AND NBMAP.STATUS   ='A'
AND NB.STATUS      = 'A' 
ORDER BY NBMAP.SORTORDER
FETCH NEXT 6 ROWS ONLY

ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action: Error at Line: 15 Column: 1

Your request title and the introduction phrase are confusing, because your question is obviously not about UNION. You should change this. — Thorsten Kettner

Thorsten Kettner Thorsten Kettner · Accepted Answer · 2019-09-25T10:19:41

The FETCH clause is only available as of Oracle 12c. In Oracle 11g you can work with ROW_NUMBER instead:

select netbankid, bankname, sortorder, bankcode
from
(
  select 
    nb.netbankid,
    nb.bankname,
    nbmap.sortorder,
    nb.bankcode,
    row_number() over (order by nbmap.sortorder) as rn
  from vtsmnetbnk nb
  inner join cgcmn.vtmestrnetbnkmap nbmap on nbmap.netbankid = nb.netbankid
  where nbmap.storeid = 133 
  and nbmap.effdate <= sysdate
  and nbmap.status = 'A'
  and nb.status = 'A' 
)
where rn <= 6
order by rn;

not able to do a UNION in Oracle SQL

1 Answers