Home » Developer & Programmer » Reports & Discoverer » Select Query with minimum values (ORACLE 9 REPORTS 6I SERVER 2003)
Select Query with minimum values [message #585809] Thu, 30 May 2013 02:47 Go to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

The prob is i want to display minimum intime and max outtime in idate against employee,
report keep displaying multi inout records of an employees!


SELECT div.division, 
       DEP.department, 
       E.employeecode, 
       E.name empname, 
       DES.designation, 
       i.idate, 
       To_char (Min(i.intime), 'HH:MI:SS AM'), 
       To_char (Max(I.outtime), 'HH:MI:SS AM'), 
       Round(i.btime / 60), 
       e.shift 
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 ) 
       AND des.designationcode NOT IN ( 11, 25 ) 
GROUP  BY div.division, 
          DEP.department, 
          E.employeecode, 
          E.name, 
          DES.designation, 
          i.idate, 
          To_char(i.intime, 'HH:MI:SS AM'), 
          To_char(I.outtime, 'HH:MI:SS AM'), 
          Round(i.btime / 60), 
          e.shift, 
          Trunc(E.employeecode) 
ORDER  BY E.employeecode; 


[FORMATTED by LF]

[Updated on: Thu, 30 May 2013 03:25] by Moderator

Report message to a moderator

Re: Select Query with minimum values [message #585813 is a reply to message #585809] Thu, 30 May 2013 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ The query is almost unreadable, please format it. If you don't know how to do it, learn it using SQL Formatter.

2/ We have not your tables nor your model so it hard to tell what has to be done.

Regards
Michel
Re: Select Query with minimum values [message #585819 is a reply to message #585813] Thu, 30 May 2013 03:36 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you already used MIN and MAX functions and put everything else into the GROUP BY clause, something among these "else" columns is causing the problem (unless, of course, WHERE clause doesn't do its job correctly). Let's try to find a culprit:
  • div.division, - most probably unique
  • DEP.department, - unique as well
  • E.employeecode, - unique
  • E.name empname, - unique
  • DES.designation, - I *suppose* it is unique; does it differ during the day?
  • i.idate, - suspicious column, especially if it stores date AND time
  • i.btime - what is this? It isn't DATE datatype, so I guess it is OK
  • e.shift - what is this?

My bet would be I.IDATE. So, if you really need to display it, consider TRUNC-ing it (so that you'd remove time component).

Another option is to create functions that return MIN and MAX values for every employee during each day (IN parameters would be EMPLOYEECODE and IDATE, I presume). In that case, instead of directly calling MIN & MAX functions, you'd call your functions which would, in turn, remove necessity of the GROUP BY clause.
Re: Select Query with minimum values [message #585824 is a reply to message #585819] Thu, 30 May 2013 03:58 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd bet on both idate and btime. They come from the same table as intime and outtime.
Unless btime is constant for all the entries for a given employee on a given day it will be part of the problem.
Re: Select Query with minimum values [message #585825 is a reply to message #585819] Thu, 30 May 2013 04:08 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

thanks all for your response!
table data & report attached!


HRM_EMPLOYEES
EMPLOYEECODE VARCHAR2(8) not null
NAME VARCHAR2(25)
SHIFT CHAR(1)
DIVISIONCODE VARCHAR2(3)
DESIGNATIONCODE VARCHAR2(3)
DEPARTMENTCODE VARCHAR2(3)

HRM_DIVISION
DIVISIONCODE VARCHAR2(3)
DIVISION VARCHAR2(30)

HRM_DESIGNATION
DESIGNATIONCODE VARCHAR2(3) not null
DESIGNATION VARCHAR2(30)

HRM_DEPARTMENT
DEPARTMENTCODE VARCHAR2(3) not null
DEPARTMENT VARCHAR2(30)
STRENGTH NUMBER(4)

HRM_INOUT
EMPLOYEECODE VARCHAR2(8)
IDATE DATE
INTIME DATE
OUTTIME DATE
BTIME NUMBER(7)




CM: disabled smilies

[Updated on: Thu, 30 May 2013 06:12] by Moderator

Report message to a moderator

Re: Select Query with minimum values [message #585847 is a reply to message #585825] Thu, 30 May 2013 06:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't see any data.
What's the primary key of hrm_inout?
Does idate contain a time component as Littlefoot suggested?
What is btime?
Re: Select Query with minimum values [message #585852 is a reply to message #585847] Thu, 30 May 2013 07:05 Go to previous messageGo to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

no primary key of hrm_inout
no idate is simply in date no time component
btime is total of intime+outtime
Re: Select Query with minimum values [message #585853 is a reply to message #585852] Thu, 30 May 2013 07:11 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
rana-mudassar wrote on Thu, 30 May 2013 13:05
no primary key of hrm_inout

Don't you think it would be a good idea if it had one?

rana-mudassar wrote on Thu, 30 May 2013 13:05

no idate is simply in date no time component

Are you absolutely certain? See what this gives:
SELECT COUNT(*)
FROM HRM_INOUT
WHERE idate != trunc(idate);


rana-mudassar wrote on Thu, 30 May 2013 13:05

btime is total of intime+outtime

Since you can't add dates together, you're going to have to explain how that's calculated.
Previous Topic: conversion of .rdf canceled
Next Topic: Order a matrix by the cell content
Goto Forum:
  


Current Time: Thu Mar 28 08:55:06 CDT 2024