0

I am trying to convert this from SQL server to oracle:

   select top 1 @StatusCount=Count(*),@logidnew=logid 
     from KioskStatuslog 
    where kioskid=@KioskId 
      and cast(logdate as date)=Cast(getdate() as date) --and lanstatus=@LANStatus
    group by logid order by logid desc
Gnqz
  • 3,292
  • 3
  • 25
  • 35
Chirag Tyagi
  • 1

2 Answers2

1

TOP(n) is SQL Server's propriatary counterpart to standard SQL's FETCH FIRST n ROWS.

select count(*) as status_count, logid 
from kioskstatuslog 
where kioskid = :kioskid 
and trunc(logdate) = trunc(sysdate)
group by logid
order by logid desc
fetch first row only;

As to

and trunc(logdate) = trunc(sysdate)

it is recommended to use

and logdate >= trunc(sysdate) and logdate < trunc(sysdate) + interval '1' day

instead, so the DBMS may use an index on that column if such exists. (Of course you could also have an index on trunc(logdate) instead and keep the simpler expression.)

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

Should be something among the lines:

SELECT *
  FROM (SELECT COUNT(1), LOGID
          FROM KIOSKSTATUSLOG
         WHERE KIOSKID   = &KIOSKID --argument
           AND TRUNC(TO_DATE(LOGDATE,<FORMAT_OF_THE_DATE>)) = TRUNC(SYSDATE)
           AND LANSTATUS = &LANStatus --argument
         GROUP BY LOGID
         ORDER BY LOGID DESC)
 WHERE ROWNUM = 1;
Gnqz
  • 3,292
  • 3
  • 25
  • 35
  • 1
    This is indeed how it was done prior to Oracle 12c. Oracle had to violate the SQL policy that a (derived) table is an unordered data set in order to have this working. Nowadays I would rather go with the standard compliant `FETCH FIRST ROWS`. – Thorsten Kettner Mar 15 '22 at 13:48
  • Probably I'm a bit too old-fashioned, it's the way I was taught way back when I first started learning SQL. Is the FETCH more efficient as well? – Gnqz Mar 15 '22 at 14:07
  • I don't think so. I even remember that at first is was slower until they fixed this in the optimizer. After all it does the same thing, so ideally the optimizer should come up with the same plan. I've been working with Oracle for more than two decades, too, so I learned it the same way. But I appreciate a lot of the newer features like window functions, lateral joins etc. The `FETCH FIRST n ROWS` comes with a ties clause, too, that can be handy when looking for the top n per group. – Thorsten Kettner Mar 15 '22 at 14:21
  • Thank you for the quick overview, you got my vote! – Gnqz Mar 15 '22 at 14:23