• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Lists employees who have changed their name/NI/DOBin the last 24 hours - ORACLE SQL

Hi,

I have written a CTE to extract all the employee information i need, although i now need to write something to check whether the name, nino or DOB has changed within the last 24 hours.


with
cte_Emp
as
(
select 
e.PERSON_REF,
e.EMPLOYEE_NUMBER,
coalesce(rtrim(ltrim(known_as)),per.FIRST_FORNAME)  as FORENAME,
per.SURNAME as SURNAME,
per.NI_NO NINO,
per.INITIALS INITS,
per.TITLE TITLE,
post.number_R as POST_NUMBER,
post.long_desc as POST_LONG_DESC,
e.START_DATE EMPSTARTDATE,
e.END_DATE EMPENDDATE,
CGrp.ID CGRPID,
CGrp.LONG_DESC CGRPDESC,
PGrp.ID PGRPID,
PGrp.LONG_DESC PGRPDESC,
narr_occtype.long_desc as OCC_TYPE,
narr_joinreason.long_desc as POST_JOIN_REASON,
narr_joinreason2.long_desc as EMP_JOIN_REASON,
ph.projected_end_date
from D550M e
left outer join D500M per on e.PERSON_REF = per.PERSON_REF
left outer join D100M CGrp on e.LEVEL1_PAY_STR_REF = CGrp.REF
left outer join D100M PGrp on e.PAY_STR_REF = PGrp.REF 
left outer join D580M ph on e.PERSON_REF = ph.PERSON_REF and
( ph.start_date <= sysdate and (ph.end_date is null or ph.end_Date >= sysdate))
left outer join D200M post on ph.ref  = post.ref
left outer join D800M narr_occtype on ph.occ_type = narr_occtype.narrative_code and narr_occtype.narrative_category = 'OCCTYP'
left outer join D800M narr_joinreason on ph.join_reason= narr_joinreason.narrative_code and narr_joinreason.narrative_category = 'RSNCHG'
left outer join D800M narr_joinreason2 on e.orig_how_joined = narr_joinreason2.narrative_code and narr_joinreason2.narrative_category = 'RSNCHG'

)
select 
PERSON_REF,
EMPLOYEE_NUMBER,
FORENAME,
SURNAME,
NINO,
INITS,
TITLE,
POST_NUMBER,
POST_LONG_DESC,
EMPSTARTDATE,
EMPENDDATE,
CGRPID,
CGRPDESC,
PGRPID,
PGRPDESC,
OCC_TYPE,
POST_JOIN_REASON,
EMP_JOIN_REASON,
projected_end_date
from cte_EMP

Open in new window


I was wondering if anyone could provide some advice on how to achieve this?

Many thanks,
0
Richiep86
Asked:
Richiep86
  • 3
  • 2
  • 2
3 Solutions
 
johnsoneSenior Oracle DBACommented:
Not sure what the CTE is doing for you.  You don't actually use it for anything.  Why not just run the query without it?
SELECT e.person_ref, 
       e.employee_number, 
       Coalesce(Rtrim(Ltrim(known_as)), per.first_forname) AS FORENAME, 
       per.surname                                         AS SURNAME, 
       per.ni_no                                           NINO, 
       per.initials                                        INITS, 
       per.title                                           TITLE, 
       post.number_r                                       AS POST_NUMBER, 
       post.long_desc                                      AS POST_LONG_DESC, 
       e.start_date                                        EMPSTARTDATE, 
       e.end_date                                          EMPENDDATE, 
       CGrp.id                                             CGRPID, 
       CGrp.long_desc                                      CGRPDESC, 
       PGrp.id                                             PGRPID, 
       PGrp.long_desc                                      PGRPDESC, 
       narr_occtype.long_desc                              AS OCC_TYPE, 
       narr_joinreason.long_desc                           AS POST_JOIN_REASON, 
       narr_joinreason2.long_desc                          AS EMP_JOIN_REASON, 
       ph.projected_end_date 
FROM   d550m e 
       left outer join d500m per 
                    ON e.person_ref = per.person_ref 
       left outer join d100m CGrp 
                    ON e.level1_pay_str_ref = CGrp.ref 
       left outer join d100m PGrp 
                    ON e.pay_str_ref = PGrp.ref 
       left outer join d580m ph 
                    ON e.person_ref = ph.person_ref 
                       AND ( ph.start_date <= SYSDATE 
                             AND ( ph.end_date IS NULL 
                                    OR ph.end_date >= SYSDATE ) ) 
       left outer join d200m post 
                    ON ph.ref = post.ref 
       left outer join d800m narr_occtype 
                    ON ph.occ_type = narr_occtype.narrative_code 
                       AND narr_occtype.narrative_category = 'OCCTYP' 
       left outer join d800m narr_joinreason 
                    ON ph.join_reason = narr_joinreason.narrative_code 
                       AND narr_joinreason.narrative_category = 'RSNCHG' 
       left outer join d800m narr_joinreason2 
                    ON e.orig_how_joined = narr_joinreason2.narrative_code 
                       AND narr_joinreason2.narrative_category = 'RSNCHG' 

Open in new window

There doesn't appear to be any auditing columns that you are selecting, so I'm not sure we can help you because we don't know what auditing columns you have set up.  There must be some sort of history table(s) set up that are tracking changes.  If there isn't, then I don't think you can do what you are asking.
0
 
Richiep86Author Commented:
normally i need to join to 5 or 6 cte's to get what i need, so thought it made sense to create one for each set of data....

i have this which i believe queries the audit table (not sure if it helps) This is a bit over my head :

SELECT
  D550M.EMPLOYEE_NUMBER as Staff_ID,
  ( D500M.TITLE ) || ' ' || ( D500M.FIRST_FORNAME ) || ' ' || ( D500M.SURNAME ) as Name,
  D996V.ELEMENT_LONG_DESC as ItemChanged,
D996V.OLD_DATA as Old_Value,
D996V.NEW_DATA as New_Value,
D996V.AUDIT_ACTION as Action,
to_char(CASE WHEN ( D996V.AUDIT_DATE ) > 0 THEN
to_date('1799/12/31','yyyy/mm/dd')  +
to_number(rtrim(D996V.AUDIT_DATE)) ELSE NULL END,'DD/MM/YYYY') as Input_Date,
substr(D996V.AUDIT_TIME,1,2) || '.' || substr(D996V.AUDIT_TIME,3,2) || '.' || substr(D996V.AUDIT_TIME,5,2) as Input_Time,
D996V.OPERATOR_ID as Operator,
'X' || bam_v_emp_email.email                  AS EMAILADD
FROM
(D500M INNER JOIN D550M ON (D500M.PERSON_REF=D550M.PERSON_REF) )
LEFT OUTER JOIN  D996V on ( D550M.PERSON_REF=substr(D996V.ENTITY_KEY,1,8))
inner join BAM_V_EMP_EMAIL
on d500m.person_ref = BAM_V_EMP_EMAIL.emp_person_ref
WHERE
D996V.ENTITY_ID  IN  ('D580M')
AND  (CASE WHEN ( D996V.AUDIT_DATE ) > 0 THEN
to_date('1799/12/31','yyyy/mm/dd')  +
to_number(rtrim(D996V.AUDIT_DATE)) ELSE NULL END = TRUNC(sysdate) )
AND  D996V.ELEMENT_ID = 'ENDDTE'
--AND D996V.OLD_DATA IS NOT NULL
AND D550M.EMPLOYEE_NUMBER=D996V.EMPLOYEE_NUMBER
ORDER BY
D550M.EMPLOYEE_NUMBER,
D996V.ELEMENT_ID  

Open in new window


Thanks John,
0
 
johnsoneSenior Oracle DBACommented:
It depends on what you mean my last 24 hours.  I took that to literally mean 24 hours.  If that is not what you intend (like go back to midnight yesterday), you just need to tweak the last line.
SELECT e.person_ref, 
       e.employee_number, 
       Coalesce(Rtrim(Ltrim(known_as)), per.first_forname) AS FORENAME, 
       per.surname                                         AS SURNAME, 
       per.ni_no                                           NINO, 
       per.initials                                        INITS, 
       per.title                                           TITLE, 
       post.number_r                                       AS POST_NUMBER, 
       post.long_desc                                      AS POST_LONG_DESC, 
       e.start_date                                        EMPSTARTDATE, 
       e.end_date                                          EMPENDDATE, 
       CGrp.id                                             CGRPID, 
       CGrp.long_desc                                      CGRPDESC, 
       PGrp.id                                             PGRPID, 
       PGrp.long_desc                                      PGRPDESC, 
       narr_occtype.long_desc                              AS OCC_TYPE, 
       narr_joinreason.long_desc                           AS POST_JOIN_REASON, 
       narr_joinreason2.long_desc                          AS EMP_JOIN_REASON, 
       ph.projected_end_date 
FROM   d550m e 
       join d996v 
         ON ( per.person_ref = Substr(d996v.entity_key, 1, 8) 
              AND per.employee_number = d996v.employee_number ) 
       left outer join d500m per 
                    ON e.person_ref = per.person_ref 
       left outer join d100m CGrp 
                    ON e.level1_pay_str_ref = CGrp.ref 
       left outer join d100m PGrp 
                    ON e.pay_str_ref = PGrp.ref 
       left outer join d580m ph 
                    ON e.person_ref = ph.person_ref 
                       AND ( ph.start_date <= SYSDATE 
                             AND ( ph.end_date IS NULL 
                                    OR ph.end_date >= SYSDATE ) ) 
       left outer join d200m post 
                    ON ph.ref = post.ref 
       left outer join d800m narr_occtype 
                    ON ph.occ_type = narr_occtype.narrative_code 
                       AND narr_occtype.narrative_category = 'OCCTYP' 
       left outer join d800m narr_joinreason 
                    ON ph.join_reason = narr_joinreason.narrative_code 
                       AND narr_joinreason.narrative_category = 'RSNCHG' 
       left outer join d800m narr_joinreason2 
                    ON e.orig_how_joined = narr_joinreason2.narrative_code 
                       AND narr_joinreason2.narrative_category = 'RSNCHG' 
WHERE  d996v.entity_id = 'D580M' 
       AND CASE 
             WHEN ( d996v.audit_date ) > 0 THEN 
             To_date('1799/12/31', 'yyyy/mm/dd') 
             + To_number(Rtrim(d996v.audit_date)) 
             ELSE NULL 
           END >= SYSDATE - 1 

Open in new window

I don't know your system, so I don't know if you need these 2 conditions, or if you need them but with something different on the other side of the =:

D996V.ENTITY_ID  = 'D580M'
D996V.ELEMENT_ID = 'ENDDTE'

One other observation.  Do you really need all those outer joins?  They aren't very efficient.  For example, I did not add the audit table as an outer join because if there is no record in the audit table, then it could not have been modified in the last 24 hours, so there is no need to retrieve that record.  This hold true for the example audit query you posted.  There is an outer join to the audit table, but if no record is in the audit table there is no way it can satisfy the where clause and it would be discarded anyway.

I would also be curious what the datatype of AUDIT_DATE in D996V is.  If it is a number, you are doing an awful lot of conversions on it for nothing.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark GeerlingsDatabase AdministratorCommented:
I agree with johnsone's recommendation to avoid "outer joins" if you don't need them, because they can add a very significant performance penalty.  Replace them with standard (or "inner") joins instead wherever you can.   And, like he says, logically it makes no sense to do a "substr" on a column in an outer-joined table.  Why not? If a matching record there actually exists, you don't need an outer join to it.  And, if it doesn't exist (which is why you might need an outer join) there is no way that a "substr" of a null in that non-existent record will ever equal the non-null value you are comparing it to.

In my opinion, if your query "needs" outer joins that means at least one of these is true:
1. The data model (table structure) is poorly-designed.
2. The application doesn't have good data entry validation.
3. The application was not designed or intended to answer the question your query tries to answer.

Since we don't know where (which column(s) and/or tables) in your application contain the audit information that can determine whether a record was changed in the last 24 hours, it is hard for us to know exactly what your query should look like.

Also, what do you mean by "CTE"?  I don't recognize that acronym or abbreviation.
0
 
johnsoneSenior Oracle DBACommented:
CTE is "Common Table Expression".  Not the term that Oracle uses, I believe it is a Microsoft term.  Oracle calls it a subquery factoring clause.  Essentially it is the WITH part of the query.  A fairly new construct to Oracle and having been writing SQL long before it was available (as you have too), I don't ever use it other than creating test data and having it get a table name like:

with tab1 as (select 1 from dual union all select 2 from dual)
select * from tab1;
0
 
Mark GeerlingsDatabase AdministratorCommented:
To johnsone: Thank you for the explanation.

To Richiep86; Does that mean you have Microsoft SQL server experience but now you are trying to write SQL for a system based on an Oracle database?  If that is true, do not assume that the best (or only?) way to do things in a SQL Server system is the best way to do them in Oracle!  These two database systems are very different.  The use of, or need for "temp" tables is one of the big differences.  They are rarely needed in Oracle.  Yes, the use of the "with" clause in Oracle can help in some multi-table queries by avoiding the need to read the records from a table multiple times.  But, as johnsone indicated earlier, I don't see a reason or benefit for that approach in your query here.
0
 
Richiep86Author Commented:
Good advice = thanks very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now