Home » Developer & Programmer » Forms » (select count(*) from table) > 0 then (Oracle Forms 11g)
(select count(*) from table) > 0 then [message #661680] Mon, 27 March 2017 21:43 Go to next message
ecca
Messages: 9
Registered: March 2017
Junior Member
Hi,

For your info, I'm currently working on a script to count consecutive working days. The script works fine on TOAD but somehow it triggered Encountered the symbol "SELECT" error every time when I tried to compile it in Form Builder 11g.

I have read the suggestion to store the result into a variable using select into but somehow I didn't quite understand how to fix the error.

Any feedback will be much appreciated. Thanks

select floor(sysdate-max(dt)) workingDays
from(
select distinct dt, 
(case when (select count(*) 
from leave_staff_trx where date_trx = t1.dt and staff_no = '1024') > 0
then 1 else 0 end) cnt
from(select trunc(sysdate) as DT from dual 
union
SELECT TRUNC (SYSDATE-rownum) dt FROM DUAL CONNECT BY ROWNUM < 30) t1
order by 1 desc) x1
where cnt =0;
Re: (select count(*) from table) > 0 then [message #661682 is a reply to message #661680] Tue, 28 March 2017 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Format your SQL, If you don't know how to do it, learn it using SQL Formatter and here's the result:
SELECT Floor(SYSDATE - Max(dt)) workingDays
FROM  (SELECT DISTINCT dt,
                       ( CASE
                           WHEN (SELECT Count(*)
                                 FROM   leave_staff_trx
                                 WHERE  date_trx = t1.dt
                                        AND staff_no = '1024') > 0 THEN 1
                           ELSE 0
                         END ) cnt
       FROM  (SELECT Trunc(SYSDATE) AS DT
              FROM   dual
              UNION
              SELECT Trunc (SYSDATE - ROWNUM) dt
              FROM   dual
              CONNECT BY ROWNUM < 30) t1
       ORDER  BY 1 DESC) x1
WHERE  cnt = 0; 
Easier to read, isn't it?

Try this way:
with
  t1 as (     SELECT Trunc(SYSDATE) AS DT
              FROM   dual
              UNION
              SELECT Trunc (SYSDATE - ROWNUM) dt
              FROM   dual
              CONNECT BY ROWNUM < 30
        ),
  cnt (       SELECT t1.dt, nvl2(Count(*),1,0) cnt
              FROM   t1 left outer join leave_staff_trx on date_trx = t1.dt
              WHERE  staff_no = '1024'
              group by t1.dt
      )
SELECT Floor(SYSDATE - Max(dt)) workingDays
from cnt
WHERE  cnt = 0
Note: I just try to fix your error but didn't try to understand your query which may be functionally wrong.
Note:
1/ DISTINCT and ORDER BY are useless, just a waste of resources
2/ cnt always returns the same dates than dt and the following always returns 0:
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  SELECT Floor(SYSDATE - Max(dt)) workingDays
 10  from t1
 11  /
WORKINGDAYS
-----------
          0
So this query is equivalent to:
select 0 workingDays 
from dual
where not exists ( select null from leave_staff_trx 
                   where staff_no = '1024'
                   and date_trx >= trunc(sysdate-30)
What is the question your query wants to answer?

[Updated on: Tue, 28 March 2017 02:07]

Report message to a moderator

Re: (select count(*) from table) > 0 then [message #661684 is a reply to message #661682] Tue, 28 March 2017 02:10 Go to previous messageGo to next message
ecca
Messages: 9
Registered: March 2017
Junior Member
Hi Michel,

Thanks for your advice and apologize for the unformatted query earlier.

Actually the query is suppose to count total consecutive days based from staff attendance record from leave_staff_trx table. For your info, we have an access card control system that will capture staff attendance into the leave_staff_trx table every time the employee swap his/her access card.

Let's say if I come to work on Monday and Tuesday, then the query will return 2 consecutive days.

Later I decided to take a leave on Wednesday and come to work on Thursday, then the query will return 1 days only as I was absent from work the day before.

As of now, the query that I shared earlier works as per I had expected. It just that I have encountered some error while trying to compile the query using Oracle Forms Builder.

However, I am more than happy if someone could help to improvise the query so that I can compile the script with no error.

Thanks in advance.
Re: (select count(*) from table) > 0 then [message #661685 is a reply to message #661684] Tue, 28 March 2017 02:37 Go to previous messageGo to next message
ecca
Messages: 9
Registered: March 2017
Junior Member
The leave_staff_trx table is basically consists of the following columns;
|date_trx|time_trx|reader|staff_no|name|company|
Re: (select count(*) from table) > 0 then [message #661686 is a reply to message #661684] Tue, 28 March 2017 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
However, I am more than happy if someone could help to improvise the query so that I can compile the script with no error.
I gave you a query you could try (the first one).

If I understand your specification, you want the latest number of consecutive working days during the last 31 days.
To be able to help you please provide: CREATE TABLE for a table with just date_trx and staff_no columns (the other ones seem irrelevant to the question), INSERT statements for several different cases with different staff_no, and the result you want for each staff_no.
Then we will be sure about the result and can provide a query.

Re: (select count(*) from table) > 0 then [message #661687 is a reply to message #661686] Tue, 28 March 2017 03:40 Go to previous messageGo to next message
ecca
Messages: 9
Registered: March 2017
Junior Member
Ok noted. I will provide the required data latest by tomorrow.
Have a nice day.

Re: (select count(*) from table) > 0 then [message #661705 is a reply to message #661680] Tue, 28 March 2017 20:44 Go to previous messageGo to next message
ecca
Messages: 9
Registered: March 2017
Junior Member
Hi Michel,

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.
Re: (select count(*) from table) > 0 then [message #661710 is a reply to message #661705] Wed, 29 March 2017 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
-----------
          4
should 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
Slightly modifying my first query to be more efficient, it works:
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
The following should be more efficient:
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
Or, for only one badge:
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
Note: do not enclose your number between quotes you will invalidate index use.

[Edit: add limit of 30 days]

[Updated on: Wed, 29 March 2017 01:52]

Report message to a moderator

Re: (select count(*) from table) > 0 then [message #661715 is a reply to message #661680] Wed, 29 March 2017 03:25 Go to previous messageGo to next message
ecca
Messages: 9
Registered: March 2017
Junior Member
Hi Michel,

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.
/forum/fa/13486/0/

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

Re: (select count(*) from table) > 0 then [message #661717 is a reply to message #661715] Wed, 29 March 2017 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I would have thought 11g would recognise a WITH statement, but apparently not, in which case you're going to have to put that in a stored proc or view.
Re: (select count(*) from table) > 0 then [message #661718 is a reply to message #661717] Wed, 29 March 2017 04:05 Go to previous messageGo to next message
ecca
Messages: 9
Registered: March 2017
Junior Member
Hi Cookiemonster,

Thanks for the suggestion.
I will give it a try first and share the result later.
Re: (select count(*) from table) > 0 then [message #661724 is a reply to message #661715] Wed, 29 March 2017 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

WITH is easier to read but you can embed the subquery as inline views in the FROM clause:
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.
Re: (select count(*) from table) > 0 then [message #661734 is a reply to message #661680] Thu, 30 March 2017 01:43 Go to previous messageGo to next message
ecca
Messages: 9
Registered: March 2017
Junior Member
Hi Guys

I'm sorry to inform you that, my latest attempt was not successful. As of now, I have tried to compile the query using trigger and also procedure(program unit) but the result is still the same.

This time around, the forms builder prompt out Encountered the symbol "LEFT" when expecting..... error message.

/forum/fa/13487/0/

Honestly, I have no idea why the forms builder cannot 'understand' the query and keep on prompting such error. Is there any other method... perhaps a simpler way to calculate consecutive days?

p/s: I found out it strange that every time I executed the WITH query in TOAD, it always return '1' as the result even after several attempt using different badges.
Re: (select count(*) from table) > 0 then [message #661735 is a reply to message #661734] Thu, 30 March 2017 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can use the first query or change the other one like:
SQL> Insert into LEAVE_STAFF_TRAX
  2     (DATE_TRX, BADGE)
  3   Values
  4     (TO_DATE('03/30/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414);

1 row created.

SQL> commit;

Commit complete.

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                ( select * from LEAVE_STAFF_TRAX where badge = 201414 )
 10         where date_trx (+) = t1.dt
 11         group by t1.dt ) cnt
 12  WHERE  cnt = 0
 13  /
WORKINGDAYS
-----------
          5

1 row selected.
Re: (select count(*) from table) > 0 then [message #661736 is a reply to message #661734] Thu, 30 March 2017 02:59 Go to previous messageGo to next message
Littlefoot
Messages: 21598
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ecca wrote on Thu, 30 March 2017 08:43

I have tried to compile the query using trigger and also procedure(program unit) but the result is still the same.
Of course it is. Every PL/SQL program unit within Forms Builder behaves the same. Maybe you didn't notice what Cookiemonster told you: create a stored procedure or a view (i.e. objects stored in the database, not in your form), and then call them from the form.
Re: (select count(*) from table) > 0 then [message #661761 is a reply to message #661680] Thu, 30 March 2017 23:18 Go to previous messageGo to next message
ecca
Messages: 9
Registered: March 2017
Junior Member
Afternoon guys,

I'm glad to inform you that, I have successfully compiled the query and my application is now able to calculate total consecutive working days as per what I had expected. Please find the final version as per screenshot below:

/forum/fa/13488/0/


Hi Michel,

Thank you for being patient with me. I just can't thank you enough. Cool


Hi Cookiemonster & Littlefoot,

I really appreciates your suggestions. Thank you for replying to my posts. Smile


With that I would like to declare that my question has been resolved. Thanks.

Re: (select count(*) from table) > 0 then [message #661762 is a reply to message #661761] Fri, 31 March 2017 01:36 Go to previous message
Littlefoot
Messages: 21598
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad you solved it.

One more objection: it is the KEY-NEXT-ITEM you use. Consider switching to WHEN-VALIDATE-ITEM trigger. KEY-NEXT-ITEM was used in old, character-mode Forms versions (such as 3.0). For backward compatibility, it is still here. However, it wouldn't fire if user navigates out of that item using any other option (such as mouse click, <Next block> key, and similar).
Previous Topic: Update Alert
Next Topic: LIST ITEM
Goto Forum:
  


Current Time: Wed Sep 30 02:33:38 CDT 2020