Recent content by tcimis

Sorry it took me awhile to get back to you but I had to take a break from this to work on something else. I have the dates coming out in the correct format of YYYYMMDD but I am getting confused on which version of the record should be pulled when. Can you look at what record I'm pulling where...

I am close but not quite there. I am showing the output and the sql code. I'm hoping you can see where I messed up. In your sample code you had PS_JOB C twice and I don't think I translated it properly so I didn't have 2 PS_JOB C's. I also had issues with the to_char so I used substr to get...

This still isn't exactly what I want. Please see below: Thank you for your assistance. The query returns: ID Eff Date Sequence DeptID Eff Date Sequence DeptID 12345 7/25/2009 0 B1B014A000 2/10/2007 0 B1B0014A00 The history for the employee is as follows: ID Eff Date Sequence DeptID 12345...

Thank you for your assistance. I will let you know how it goes.

We are on PS 8.9, Oracle Database. I am trying to write a query to track department changes. The action used is not always department change. I am joining Job to Employees and selecting where employees.deptid <> job.deptid. This works fine with the exception of when multiple changes are made...

I was able to correct the issue using running totals. Thank you for your assistance.

See detail below. Empl A has 8 hours of VAU which doesn't appear in the details. It is not added in at the employee level but is added in at the union level. NAME DATE REG HRS CODE OTH_HRS TOT HOURS 5/3/2009 40.00 0.00 40.00 5/3/2009 0.00 OT1 12.50 12.50 5/3/2009 0.00 OT2 8.00 8.00...

I have a formula: IF {SR_UNION_REPORT.ERNCD} in ['HUN', 'HOL', 'VAU', 'VAC', 'VPO','JUR','BON', 'LAP'] THEN 0 ELSE {SR_UNION_REPORT.OTH_HRS} The formula works fine at the employee level. When I go to get a sum of all the employees at the union level, the total includes the hours...

I switched to PS_EMPLOYEES which maxes out the query result same as PS_JOB. I placed in the criteria an EMPLID I know has an email address and 2 that don't. I received one record back for the one with an email address and 24143 records for each EMPLID that did not have an email address...

Here is the SQL when I try an outer join. The query result set is to large - it returns over 900000 rows. I have approx 8175 employees I'm trying to pull. I need to return email addresses and not everyone has their email address in PS. SELECT A.EMPLID, B.EMAIL_ADDR FROM (PS_JOB A LEFT...

Here is the sql. I now have it returning all the records but I need it to return the actual email address. SELECT A.EMPLID FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.OPRID = '4' AND ( A.EFFDT = (SELECT...

I was able to figure out a way to do this. The only issue I have now is outer join to email addresses or a "does not exist" subquery for email addresses. I don't seem to be able to get either one to work.

I have the following issue: I need to write a query using the PeopleSoft query tool (don't have access to SQLPlus) to track employees changing from regular to temp status. Need to report emplid, name and effdt of change. I need to do the same thing to track name changes. Need to report emplid...

I have this working now. I am using the expression: substr(to_char(B.DT_ISSUED,'YYYY-MM-DD'),1,4) I don't use this as a field just use for criteria. Thank you for your assistance.

I am now trying to use the trunc function since I'm wondering if I'm receiving no rows because it is actually a date/time field. The error I receive is: SQL error. Stmt #: 5653 Error Position: 249 Return: 1722 - ORA-01722: invalid number A SQL error occurred. Please consult your system log...