带有 where 和 offset fetch 子句的简单选择在 oracle 中不起作用 [英] Simple Select with where and offset fetch clause not working in oracle

问题描述

我正在尝试选择具有 where 条件的行并且需要分页.所以我添加了 Fetch 和 offset[to make it dynamic] 子句，但它给出了这个错误:

I am trying to select rows with where condition and will need pagination. So I have added Fetch with offset[to make it dynamic] clause but then it gives this error:

ORA-00933:SQL 命令未正确结束位置:414

ORA-00933: SQL command not properly ended Position: 414

我的查询:

SELECT
    up.NAME upozilaName_bn,
       up.id upozila,
    dis.NAME districtName_bn,
       dis.id district,
    dv.NAME divisionName_bn,
       dv.id division,
    w.COUNTER_TYPE, w.COUNTER_ID,
    w.STATUS
FROM X w
left join  Y up
    ON w.UPOZILA = up.ID
left JOIN Z dis
    ON w.DISTRICT = dis.id
left join  P dv
    ON w.DIVISION = dv.ID
where 1=1  order by upozilaName_bn asc OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;

没有 FFSET 1 ROWS FETCH NEXT 10 ROWS ONLY 这个，没关系！我搜索了那个错误:933，但没有找到帮助.甲骨文版本:11.2g

Without FFSET 1 ROWS FETCH NEXT 10 ROWS ONLY this, it is okay! I searched for that error: 933, but no help found. Oracle version: 11.2g

推荐答案

OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY 可从 Oracle 12c 获得.

OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY is available from Oracle 12c.

相反，您需要执行查询并对数据进行排序；然后为有序行生成一个行号；最后过滤这些行号.这些步骤需要在嵌套子查询中以正确的顺序进行:

Instead, you need to perform your query and order the data; then generate a row number for the ordered rows; and finally filter on those row numbers. These steps need to take place in the correct order in nested sub-queries:

SELECT *
FROM   (
  SELECT t.*,
         ROWNUM AS rn
  FROM   (
    SELECT up.NAME AS upozilaName_bn,
           up.id AS upozila,
           dis.NAME AS districtName_bn,
           dis.id AS district,
           dv.NAME AS divisionName_bn,
           dv.id AS division,
           w.COUNTER_TYPE,
           w.COUNTER_ID,
           w.STATUS
    FROM X w
    left join  Y up ON w.UPOZILA = up.ID
    left JOIN  Z dis ON w.DISTRICT = dis.id
    left join  P dv ON w.DIVISION = dv.ID
    order by upozilaName_bn asc
  ) T
)
WHERE  rn BETWEEN 2 AND 11;

这篇关于带有 where 和 offset fetch 子句的简单选择在 oracle 中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！