Home » Developer & Programmer » Reports & Discoverer » Count Records with a not null column (ORACLE 9 REPORTS 6I SERVER 2003)
Count Records with a not null column [message #588349] Tue, 25 June 2013 04:24 Go to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

i want to count group above report records!
want to count department group employees there intime is not null!

my report qury

SELECT div.division,
DEP.DEPARTMENT,
DEP.STRENGTH,
E.EMPLOYEECODE,
E.NAME,
DES.DESIGNATION,
E.SHIFT,
I.IDATE,
TO_CHAR(MIN(I.INTIME),'HH:MI AM'),
TO_CHAR(MAX(I.OUTTIME),'HH:MI AM'),
ROUND(SUM(I.BTIME)/60)
FROM HRM_EMPLOYEES E,
HRM_DEPARTMENT DEP,
HRM_DESIGNATION DES,
HRM_INOUT I,
HRM_DIVISION div
WHERE E.EMPLOYEECODE=I.EMPLOYEECODE(+)
AND DEP.DEPARTMENTCODE=E.DEPARTMENTCODE
AND DES.DESIGNATIONCODE=E.DESIGNATIONCODE(+)
and div.divisioncode = e.divisioncode
and (div.divisioncode = :p_divcode or :p_divcode is null)
and (I.idate(+) between :from_date and :to_date )
GROUP BY div.division,DEP.DEPARTMENT,E.EMPLOYEECODE,E.NAME ,DES.DESIGNATION,I.IDATE,e.shift,DEP.STRENGTH
ORDER BY E.EMPLOYEECODE,dep.department;

im doing it with farmula column!

function CF_PRESENTFormula return Number is
v_emps number;
begin
select count(1)
into v_emps
from hrm_employees e,hrm_inout i
where e.employeecode=I.employeecode
and i.intime=:TO_CHAR_MIN_I_INTIME_HH_MI_AM
and :TO_CHAR_MIN_I_INTIME_HH_MI_AM is not null;
return v_emps;
end;

please help im new in this field!
Re: Count Records with a not null column [message #588363 is a reply to message #588349] Tue, 25 June 2013 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Please help us to help you in this field.

Regards
Michel
Re: Count Records with a not null column [message #588379 is a reply to message #588363] Tue, 25 June 2013 07:11 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

dear i tried sql formater but did ot format it?
Re: Count Records with a not null column [message #588380 is a reply to message #588379] Tue, 25 June 2013 07:16 Go to previous messageGo to next message
cookiemonster
Messages: 11089
Registered: September 2008
Location: Rainy Manchester
Senior Member
Read the 2nd link in Michel's post above.

Does your formula column not do what you want it to?
Re: Count Records with a not null column [message #588383 is a reply to message #588380] Tue, 25 June 2013 07:52 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

./fa/10917/0/SELECT div.division,
DEP.department,
DEP.strength,
E.employeecode,
E.name,
DES.designation,
E.shift,
I.idate,
To_char(Min(I.intime), 'HH:MI AM'),
To_char(Max(I.outtime), 'HH:MI AM'),
Round(SUM(I.btime) / 60)
FROM hrm_employees E,
hrm_department DEP,
hrm_designation DES,
hrm_inout I,
hrm_division div
WHERE E.employeecode = I.employeecode(+)
AND DEP.departmentcode = E.departmentcode
AND DES.designationcode = E.designationcode(+)
AND div.divisioncode = e.divisioncode
AND ( div.divisioncode = :p_divcode
OR :p_divcode IS NULL )
AND ( I.idate(+) BETWEEN :from_date AND :to_date )
GROUP BY div.division,
DEP.department,
E.employeecode,
E.name,
DES.designation,
I.idate,
e.shift,
DEP.strength
ORDER BY E.employeecode,
dep.department;

i want to a farmula column that return records that dont have intime!
these are in department group!
i want to show them as present!
and i want to show "------" when intime is null;





  • Attachment: untitled.JPG
    (Size: 33.29KB, Downloaded 233 times)
Re: Count Records with a not null column [message #588390 is a reply to message #588380] Tue, 25 June 2013 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 11089
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 25 June 2013 13:16
Read the 2nd link in Michel's post above.


And then follow the instructions it contains. Ignoring requests from the moderators to follow the rules isn't a great way to get help.
Re: Count Records with a not null column [message #588469 is a reply to message #588390] Wed, 26 June 2013 02:17 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

SELECT div.division,
       DEP.department,
       DEP.strength,
       E.employeecode,
       E.name,
       DES.designation,
       E.shift,
       I.idate,
       To_char(Min(I.intime), 'HH:MI AM'),
       To_char(Max(I.outtime), 'HH:MI AM'),
       Round(SUM(I.btime) / 60)
FROM   hrm_employees E,
       hrm_department DEP,
       hrm_designation DES,
       hrm_inout I,
       hrm_division div
WHERE  E.employeecode = I.employeecode(+)
       AND DEP.departmentcode = E.departmentcode
       AND DES.designationcode = E.designationcode(+)
       AND div.divisioncode = e.divisioncode
       AND ( div.divisioncode = :p_divcode
              OR :p_divcode IS NULL )
       AND ( I.idate(+) BETWEEN :from_date AND :to_date )
GROUP  BY div.division,
          DEP.department,
          E.employeecode,
          E.name,
          DES.designation,
          I.idate,
          e.shift,
          DEP.strength
ORDER  BY E.employeecode,
          dep.department; 


i want a farmula column that return records that dont have intime!
like in screan below and i want to show "------" when intime is null i did it in property "value if null"
these are in department group!
i want to show them as present!
./fa/10920/0/
  • Attachment: untitled.JPG
    (Size: 33.29KB, Downloaded 204 times)
Re: Count Records with a not null column [message #588475 is a reply to message #588469] Wed, 26 June 2013 02:40 Go to previous messageGo to next message
cookiemonster
Messages: 11089
Registered: September 2008
Location: Rainy Manchester
Senior Member
I assume the relationship between hrm_employees and hrm_inout is one to many?
What do you want the count to show if the employee has multiple hrm_inout records where intime is null?
Re: Count Records with a not null column [message #588485 is a reply to message #588475] Wed, 26 June 2013 03:16 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

no i just want to count how manay employee has intime!
i don't want to count those have null intime,
and also i want to disply value"-----" if there in time is null,
i did it in property "value if null"
in image it is showing 3 present they have intime
i use a summery column to count intime, if i use value if null then it shows all seven present!
Re: Count Records with a not null column [message #588486 is a reply to message #588485] Wed, 26 June 2013 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, you can't have both. Instead of a summary column, create a formula column which will calculate number of employees whose INTIME is NOT NULL.
Re: Count Records with a not null column [message #588760 is a reply to message #588486] Fri, 28 June 2013 02:04 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

any examples....?
Re: Count Records with a not null column [message #588762 is a reply to message #588760] Fri, 28 June 2013 02:07 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Example of a formula column? The simplest one looks like
function ... return number
is
  retval number;
begin
  select count(*)
    into retval
    from your_table
    where intime is not null;

  return (retval);
end;
Re: Count Records with a not null column [message #589316 is a reply to message #588762] Thu, 04 July 2013 06:21 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

I want to count them department group wise?
Re: Count Records with a not null column [message #589317 is a reply to message #589316] Thu, 04 July 2013 06:24 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Put a formula column into department's group and include department number into SELECT's WHERE clause.
Re: Count Records with a not null column [message #589400 is a reply to message #589317] Fri, 05 July 2013 04:44 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

results are not accurate?
FUNCTION Cf_presentformula
RETURN NUMBER
IS
  v_present NUMBER;
BEGIN
    SELECT Count(1)
    INTO   v_present
    FROM   hrm_employees E,
           hrm_department D,
           hrm_inout I
    WHERE  D.departmentcode = E.departmentcode
           AND E.employeecode = I.employeecode
           AND I.employeecode = :EMPLOYEECODE
           AND E.employeecode = :EMPLOYEECODE
           AND D.department = :DEPARTMENT
           AND I.intime IS NOT NULL;

    RETURN v_present;
EXCEPTION
  WHEN OTHERS THEN
             RETURN NULL;
END;  
Re: Count Records with a not null column [message #589402 is a reply to message #589400] Fri, 05 July 2013 05:00 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know why they aren't accurate (as we don't have your tables nor data).

Try to write a query (in SQL*Plus or any other tool you use) which returns correct results. Then copy it into a report.
Re: Count Records with a not null column [message #589410 is a reply to message #589402] Fri, 05 July 2013 05:35 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

problem is if there is multi intime against employee code it is counting them all,
how to distinct employeecode?
Re: Count Records with a not null column [message #589419 is a reply to message #589410] Fri, 05 July 2013 06:00 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
With DISTINCT?

Seriously, I have difficulties in understanding what you are saying without knowing your data.
Re: Count Records with a not null column [message #589444 is a reply to message #589419] Fri, 05 July 2013 23:31 Go to previous message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

What kind of data you need?
Previous Topic: Oracle Report Engine Error
Next Topic: Get unique values with formula column
Goto Forum:
  


Current Time: Fri Oct 31 18:09:28 CDT 2014

Total time taken to generate the page: 0.12996 seconds