Oracle condition to limit sql query

Question

I have this sql for oracle database. How can I write the condition to get the data between 5 and 30? Based on the values from sql I want to make a datagrid in php with pagination.

"select c.case_id as NGM_ID,   
       s.NE_PRIORITAET as NE_PRIO,
       case substr(s.NE_ID, 2,1)
         when '0' then 'Zentrale'
         when '1' then 'Nord'
         when '2' then 'Nord' 
         when '3' then 'Ost'
         when '4' then 'Ost'
         when '5' then 'Mitte'
         when '6' then 'West'
         when '7' then 'Süd'
         when '8' then 'Mitte'
         when '9' then 'Süd'
         else          'Error'
       end as REGION,         
       c.STATUS_NGM as NGM_STATUS,
       s.AUFTRAG as AUFTRAG,
       s.NE_ID as NE_ID,
       s.STATUS as SAP_STATUS,
       substr(to_timestamp(to_char(Sysdate, 'YY/MM/DD HH24:MI:SS'), 'YY/MM/DD HH24:MI:SS') - to_timestamp(to_char(s.CREATION_TIME, 'YY/MM/DD HH24:MI:SS'), 'YY/MM/DD HH24:MI:SS'), 8,12) as DIFF_BEGINN_SAP_ENDE,
      case trim(s.KATEGORIE)
         when '1' then 'INSLA'
         when '2' then 'OUTSLA'
         else          s.KATEGORIE
       end as SLA,
       to_char(c.CREATION_TIME, 'YY/MM/DD HH24:MI:SS')
        as NGM_CREATION_TIME,
       case s.NE_PRIORITAET
         when 'A' then substr(to_char((to_timestamp(to_char((Sysdate + 6/24), 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss') - to_timestamp(to_char(s.CREATION_TIME, 'yyyy.mm.dd hh24:mi:ss'), 'YY/MM/DD HH24:MI:SS')), 'dd hh24:mi:ss'),8,12)
         when 'B' then substr(to_char((to_timestamp(to_char((Sysdate + 1), 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss') - to_timestamp(to_char(s.CREATION_TIME, 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss')), 'dd hh24:mi:ss'), 8,12)
         when 'C' then substr(to_char((to_timestamp(to_char((Sysdate + 1), 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss') - to_timestamp(to_char(s.CREATION_TIME, 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss')), 'dd hh24:mi:ss'),8,12)
         else          substr(to_char((to_timestamp(to_char((Sysdate + 1), 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss') - to_timestamp(to_char(s.CREATION_TIME, 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss')), 'dd hh24:mi:ss'), 8,12)
        end as TIME_LEFT_OVERRUN,

       c.URSACHE_KURZ as URSACHE_KURZ,
       s.BEARBEITER as BEARBEITER
      from dash_omc_cases c, dash_omc_sap s
      where c.CASE_ID = s.CASE_ID
      order by c.CREATION_TIME desc";

neshkeev · Accepted Answer · 2014-08-22 14:35:06Z

Like this:

select * from (
select ROW_NUMBER() OVER(order by c.CREATION_TIME desc) rn, c.case_id as NGM_ID,   
       s.NE_PRIORITAET as NE_PRIO,
       case substr(s.NE_ID, 2,1)
         when '0' then 'Zentrale'
         when '1' then 'Nord'
         when '2' then 'Nord' 
         when '3' then 'Ost'
         when '4' then 'Ost'
         when '5' then 'Mitte'
         when '6' then 'West'
         when '7' then 'Süd'
         when '8' then 'Mitte'
         when '9' then 'Süd'
         else          'Error'
       end as REGION,         
       c.STATUS_NGM as NGM_STATUS,
       s.AUFTRAG as AUFTRAG,
       s.NE_ID as NE_ID,
       s.STATUS as SAP_STATUS,
       substr(to_timestamp(to_char(Sysdate, 'YY/MM/DD HH24:MI:SS'), 'YY/MM/DD HH24:MI:SS') - to_timestamp(to_char(s.CREATION_TIME, 'YY/MM/DD HH24:MI:SS'), 'YY/MM/DD HH24:MI:SS'), 8,12) as DIFF_BEGINN_SAP_ENDE,
      case trim(s.KATEGORIE)
         when '1' then 'INSLA'
         when '2' then 'OUTSLA'
         else          s.KATEGORIE
       end as SLA,
       to_char(c.CREATION_TIME, 'YY/MM/DD HH24:MI:SS')
        as NGM_CREATION_TIME,
       case s.NE_PRIORITAET
         when 'A' then substr(to_char((to_timestamp(to_char((Sysdate + 6/24), 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss') - to_timestamp(to_char(s.CREATION_TIME, 'yyyy.mm.dd hh24:mi:ss'), 'YY/MM/DD HH24:MI:SS')), 'dd hh24:mi:ss'),8,12)
         when 'B' then substr(to_char((to_timestamp(to_char((Sysdate + 1), 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss') - to_timestamp(to_char(s.CREATION_TIME, 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss')), 'dd hh24:mi:ss'), 8,12)
         when 'C' then substr(to_char((to_timestamp(to_char((Sysdate + 1), 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss') - to_timestamp(to_char(s.CREATION_TIME, 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss')), 'dd hh24:mi:ss'),8,12)
         else          substr(to_char((to_timestamp(to_char((Sysdate + 1), 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss') - to_timestamp(to_char(s.CREATION_TIME, 'YY/MM/DD HH24:MI:SS'), 'yyyy.mm.dd hh24:mi:ss')), 'dd hh24:mi:ss'), 8,12)
        end as TIME_LEFT_OVERRUN,

       c.URSACHE_KURZ as URSACHE_KURZ,
       s.BEARBEITER as BEARBEITER
      from dash_omc_cases c, dash_omc_sap s
      where c.CASE_ID = s.CASE_ID
) where rn between 5 and 30
order by rn

Collectives™ on Stack Overflow

Oracle condition to limit sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related