SQL command not properly ended
I'm using the following SQL and receiving error:
Could Not Run Query
ORA-00933: SQL command not properly ended
Select ID ,ID_PARENT ,ID_BASE ,C_PROCEDURE_CLASS_DESC ,C_DRG_CD as "DRG Code" ,C_LINE_ITEM_NUM as "CLI Num" FROM (SELECT rownum
I'm trying to return rows between 1 and 300.
Could Not Run Query
ORA-00933: SQL command not properly ended
Select ID ,ID_PARENT ,ID_BASE ,C_PROCEDURE_CLASS_DESC ,C_DRG_CD as "DRG Code" ,C_LINE_ITEM_NUM as "CLI Num" FROM (SELECT rownum
I'm trying to return rows between 1 and 300.
Select
ID
,ID_PARENT
,ID_BASE
,C_PROCEDURE_CLASS_DESC
,C_DRG_CD as "DRG Code"
,C_LINE_ITEM_NUM as "CLI Num"
FROM
(SELECT rownum rn,ID,ID_PARENT,ID_BASE,C_PROCEDURE_CLASS_DESC,C_DRG_CD ,C_LINE_ITEM_NUM from T_APL_CLAIM_LINE_ITEM)
WHERE
rn BETWEEN 1 AND 300ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
A word of caution with the accepted answer:
You shouldn't use ROWNUM like that. It sort of implies you could then do "rownum between 10 and 20" which you cannot do.
Would be better to use:
where rownum<=300
You shouldn't use ROWNUM like that. It sort of implies you could then do "rownum between 10 and 20" which you cannot do.
Would be better to use:
where rownum<=300
ASKER
How should I use it?
ROWNUM is a virtual column applied before any "order by" so it is just the order the query returns the rows. Nothing more.
If you want to do pagination based on some column order, say id descending:
If you want to do pagination based on some column order, say id descending:
Select
ID
,ID_PARENT
,ID_BASE
,C_PROCEDURE_CLASS_DESC
,C_DRG_CD as "DRG Code"
,C_LINE_ITEM_NUM as "CLI Num"
FROM (
Select
ID
,ID_PARENT
,ID_BASE
,C_PROCEDURE_CLASS_DESC
,C_DRG_CD as "DRG Code"
,C_LINE_ITEM_NUM as "CLI Num",
row_number() over(order by id desc) rn
FROM
T_APL_CLAIM_LINE_ITEM
)
where rn between 10 and 20;
slightwv's comment is correct. The code would be better like this:
Select
ID
,ID_PARENT
,ID_BASE
,C_PROCEDURE_CLASS_DESC
,C_DRG_CD as "DRG Code"
,C_LINE_ITEM_NUM as "CLI Num"
FROM
T_APL_CLAIM_LINE_ITEM
WHERE
ROWNUM <= 300
You can use "SET SQLBLANKLINES" to allow them but it usually isn't worth the trouble.