That said - I'm not sure why you've written any of this code. The ELSIF should be handled by setting the required property on the item, and inserting and updating should probably just be left to default forms behaviour. The user is going to know they're updating a record, because they will have queried it first (and if they haven't you're doing it wrong).]]>

Thanks for the suggestion.

I will give it a try first and share the result later.]]>

As for merging the cursors - OR is a wonderful thing.

And I don't know what you think you're doing with rownum there but what you've got will give rownum of 1 for each query if you use the login MSRDBA.]]>

Thanks for your respond. Just to let you know that your query works fine and the results are correct.

The problem is when I tried to compile the query in Oracle Form Builer.

Honestly, this is my first time using 'With' clause and I don't sure if I had compiled it the correct way.

Appreciates your advice. Thanks

]]>

]]>

SQL> select floor(sysdate-max(dt)) workingDays 2 from( 3 select distinct dt, 4 (case when (select count(*) from leave_staff_trax where date_trx = t1.dt and badge = '201414') > 0 then 1 else 0 end) cnt 5 from(select trunc(sysdate) as DT from dual union SELECT TRUNC (SYSDATE-rownum) dt FROM DUAL CONNECT BY ROWNUM < 30) t1 6 order by 1 desc) x1 7 where cnt =0; WORKINGDAYS ----------- 4should be

SQL> SELECT Floor(SYSDATE - Max(dt)) workingDays 2 FROM (SELECT DISTINCT dt, 3 ( CASE 4 WHEN (SELECT Count(*) 5 FROM leave_staff_trax 6 WHERE date_trx = t1.dt 7 AND badge = 201414) > 0 THEN 1 8 ELSE 0 9 END ) cnt 10 FROM (SELECT Trunc(SYSDATE) AS DT 11 FROM dual 12 UNION 13 SELECT Trunc (SYSDATE - ROWNUM) dt 14 FROM dual 15 CONNECT BY ROWNUM < 30) t1 16 ORDER BY 1 DESC) x1 17 WHERE cnt = 0 18 / WORKINGDAYS ----------- 4

SQL> with 2 t1 as ( SELECT Trunc(SYSDATE) AS DT 3 FROM dual 4 UNION 5 SELECT Trunc (SYSDATE - ROWNUM) dt 6 FROM dual 7 CONNECT BY ROWNUM < 30 8 ), 9 cnt as ( SELECT t1.dt, Count(date_trx) cnt 10 FROM t1 left outer join 11 ( select * from LEAVE_STAFF_TRAX where badge = 201414 ) 12 on date_trx = t1.dt 13 group by t1.dt order by t1.dt 14 ) 15 SELECT Floor(SYSDATE - Max(dt)) workingDays 16 from cnt 17 WHERE cnt = 0 18 / WORKINGDAYS ----------- 4

SQL> with 2 data as ( 3 select BADGE, DATE_TRX, 4 lead(DATE_TRX) over (partition by BADGE order by DATE_TRX desc) prev_date, 5 max(DATE_TRX) over (partition by badge) last_day 6 from LEAVE_STAFF_TRAX 7 where DATE_TRX >= trunc(sysdate-30) 8 ) 9 select badge, 10 case when last_day = trunc(sysdate) then trunc(sysdate)-max(date_trx)+1 11 else 0 12 end workingDays 13 from data 14 where prev_date != date_trx-1 15 group by badge, last_day 16 order by badge 17 / BADGE WORKINGDAYS ---------- ----------- 39496 0 40386 0 201414 4 470346 2 543789 3

SQL> with 2 data as ( 3 select BADGE, DATE_TRX, 4 lead(DATE_TRX) over (order by DATE_TRX desc) prev_date, 5 max(DATE_TRX) over () last_day 6 from LEAVE_STAFF_TRAX 7 where badge = 201414 and DATE_TRX >= trunc(sysdate-30) 8 ) 9 select case when last_day = trunc(sysdate) then trunc(sysdate)-max(date_trx)+1 10 else 0 11 end workingDays 12 from data 13 where prev_date != date_trx-1 14 group by last_day 15 / WORKINGDAYS ----------- 4

[Edit: add limit of 30 days]

]]>

]]>