SQL> select case when last_day = trunc(sysdate) then trunc(sysdate)-max(date_trx)+1 2 else 0 3 end workingDays 4 from ( select BADGE, DATE_TRX, 5 lead(DATE_TRX) over (order by DATE_TRX desc) prev_date, 6 max(DATE_TRX) over () last_day 7 from LEAVE_STAFF_TRAX 8 where badge = 201414 and DATE_TRX >= trunc(sysdate-30) ) data 9 where prev_date != date_trx-1 10 group by last_day 11 / WORKINGDAYS ----------- 4 1 row selected. SQL> SELECT Floor(SYSDATE - Max(dt)) workingDays 2 from ( SELECT t1.dt, Count(date_trx) cnt 3 FROM ( SELECT Trunc(SYSDATE) AS DT 4 FROM dual 5 UNION 6 SELECT Trunc (SYSDATE - ROWNUM) dt 7 FROM dual 8 CONNECT BY ROWNUM < 30 ) t1 9 left outer join 10 ( select * from LEAVE_STAFF_TRAX where badge = 201414 ) 11 on date_trx = t1.dt 12 group by t1.dt order by t1.dt ) cnt 13 WHERE cnt = 0 14 / WORKINGDAYS ----------- 4 1 row selected.

]]>

And Thanks Michel Feedback me And Sorry For Previous Mesages I Can't Replied Because Of My Internet Connection is Damage. Now That Solved.]]>

I don't know why it was not in my thoughts

Regards,

Pointers]]>

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

]]>

We want to close these ports

12401, 12402, 12403, 12501,12502,12503, 12601,12602,12603

further we have come to know above ports are ajp,rmi and jms...

How can we close them if possible

OR

if not possible then configure them to listen on 127.0.0.1

Regards,]]>

]]>

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]

]]>

]]>

DECLARE UPD NUMBER(20); B NUMBER(10); BEGIN --***************FOR UPDATE ALERT (NOT WORKING THIS BLOCK)********************************** SELECT SALE.BILL_NO INTO B FROM SALE WHERE SALE.BILL_NO=:SALE.BILL_NO; IF :SALE.BILL_NO=B THEN UPD:=SHOW_ALERT('UPDATE'); IF UPD=ALERT_Button1 THEN --SYSTEM.MESSAGE_LEVEL:=5; COMMIT_FORM; --SYSTEM.MESSAGE_LEVEL:=25; END IF; --***************FOR UPDATE ALERT (WORKING ACCURATE)********************************** ELSIF :SALE.PRICE IS NULL THEN MESSAGE ('NOT INSERT BECAUSE OF EMPTY FIELD'); MESSAGE ('NOT INSERT BECAUSE OF EMPTY FIELD'); ELSE COMMIT_FORM; END IF; END;

Please find the requested data as per attached. For your info, I have prepared a sample of attendance data for 5 badges listed below.

Based on the today attendance(29/03/2017) the result should be as follows:

Badge 39496 = 0 day

Badge 543789 = 3 days

Badge 470346 = 2 days

Badge 40386 = 0 day

Badge 201414 = 4 days

Do let me know should you have problem to execute the script.]]>