×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Employees dropping off when using Telephone_Nbr table

Employees dropping off when using Telephone_Nbr table

Employees dropping off when using Telephone_Nbr table

(OP)
Hello,

I am trying to include the Home Phone in a simple report; however when I used the Telephone_Nbr table, there are approximately 125 employees dropping off. I assume these are employees who do not have a telephone number entered.  I tried checking the "include unmatched records" box in the linking section to create an outer join, but got the same result.  

Any suggestions?

thanks!
Amie

RE: Employees dropping off when using Telephone_Nbr table

Is this table effective dated?  

If it is you need to click on the effective date line in Selections and select create a list and move this item into it.  Then on the new list change it to any.  And add an item to the list that is data field telephone_nbr.effdt is null

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Employees dropping off when using Telephone_Nbr table

(OP)
Oddly enough, it's not effective dated.  Should I still try the list option?

RE: Employees dropping off when using Telephone_Nbr table

Is there anything in the Selections for this table?

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Employees dropping off when using Telephone_Nbr table

(OP)
I tried an "any" list where PHONE is equal to HOME or null, but no luck.

RE: Employees dropping off when using Telephone_Nbr table

Make a copy of the report.

Get rid of EVERYTHING you can and still have the problem.  If you have anything extra it will make it harder to SEE the problem.

Copy the SQL and post it here.
 

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Employees dropping off when using Telephone_Nbr table

(OP)
SELECT
"PS_JOB"."FILE_NBR", "PS_PERSONAL_DATA"."CITY", "PS_PERSONAL_DATA"."STATE", "PS_PERSONAL_DATA"."STREET1", "PS_PERSONAL_DATA"."STREET2", "PS_PERSONAL_DATA"."ZIP", "PS_TELEPHONE_NBR"."PHONE"
FROM
"PS_JOB", "PS_PERSONAL_DATA", "PS_TELEPHONE_NBR"
WHERE
(((("PS_JOB"."EFFSEQ"= (
    SELECT MAX("INNERALIAS"."EFFSEQ")
    FROM "PS_JOB" INNERALIAS
    WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
    AND "INNERALIAS"."EFFDT" = "PS_JOB"."EFFDT")
AND
"PS_JOB"."EFFDT" = (
    SELECT MAX("INNERALIAS"."EFFDT")
    FROM "PS_JOB" INNERALIAS
    WHERE "INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID"
    AND "INNERALIAS"."EFFDT" <= SYSDATE))) AND
("PS_JOB"."EMPL_STATUS" <> 'T') AND
  (("PS_TELEPHONE_NBR"."PHONE_TYPE" = 'HOME') OR
  ("PS_TELEPHONE_NBR"."PHONE" IS NULL))))
AND
("PS_JOB"."EMPLID" (+)= "PS_PERSONAL_DATA"."EMPLID" ) AND ("PS_JOB"."EMPLID" = "PS_TELEPHONE_NBR"."EMPLID" )

RE: Employees dropping off when using Telephone_Nbr table

It looks like you have "Include Unmatch Records" on for the Job to Personal_Data link but not on for the Job to Telephone link.  It should be the other way around.  OFF for Job to Personal Data ON for Job to Telephone (On the Telephone Side)

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Employees dropping off when using Telephone_Nbr table

(OP)
Oh yes, I was trying anything/everything.  I actaully did it on the correct link/table but when that didn't work, I tried the other one, just for fun.  still no luck either way.   

RE: Employees dropping off when using Telephone_Nbr table

(OP)
The issue seems to be when it is linked to the job and/or personal_data tables.  When I removed all tables, it pulled all employees. Just not sure where to start.

RE: Employees dropping off when using Telephone_Nbr table

(OP)
It's still dropping 87 employees.

When I run the report with the Telephone_Nbr table alone, and then manually pull it into another spreadsheet via vlookup, it works.  But when I add the job table, and exclude anyone with a "T" status, it drops them off.  Very strange.

RE: Employees dropping off when using Telephone_Nbr table

I wonder if the telephone table has some spaces in eather the EMPLID or Type (HOME PHONE) fields.

What happens if you run my example without any Selections except the Effective date line?

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Employees dropping off when using Telephone_Nbr table

(OP)
When I take everything off except the effdt line, it pulls a lot of records (pulling one for each type of phone, home/work, etc)  So I added the 1 line to select "HOME" but didn't do anything with the status on the job table. Still dropping off 74 emps.

RE: Employees dropping off when using Telephone_Nbr table

You will need to figure out a person that is missing and look at their Telephone record and see what is different fome someone who gets pulled in.
 

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Employees dropping off when using Telephone_Nbr table

I had this same problem. I tried all the links possible, with the nulls, too. The only work-around I could do was move the PS_EMPLOYEES file (or whatever connecting table you need) and the TELEPHONE Table to Xls and do a VLOOKUP.  

Tom Walsh, HRIS Analyst
Capital Health System
ADP HR&Payroll wi RSmith

RE: Employees dropping off when using Telephone_Nbr table

I worked on something similar recently. Tom touched on a great idea. Run emptblin and develop the query using PS_EMPLOYEES (or PS_ALL_EMPLOYEES). This will help limit issues with effective dated rows. Once you have the data issues resolved, begin by adding the component tables to the query (JOB, PERSONAL_DATA, etc).

John Sakalauskas
VantageIS,LLC

RE: Employees dropping off when using Telephone_Nbr table

Go back to your basics and you will find the PERSONAL_DATA table's HOME_PHONE field is different from the data in the TELEPHONE_NBR table.  Create 2 different reports side by side each with one of these tables.

I have found running EMPTBLN doesn't help either.

Create a report with the PS_PERSONAL_DATA table (alias P - required for the derived field I built).  Add columns EMPLID, NAME and HOME_PHONE (query only).

Build a derived field HOME_PHONE_NBR as follows:

DECODE(P.HOME_PHONE,
' ', (SELECT T.PHONE
      FROM PS_TELEPHONE_NBR T
      WHERE (( (T.PHONE_TYPE = 'HOME') AND
               (T.EMPLID = P.EMPLID) )) ),
P.HOME_PHONE
)

This looks at the HOME_PHONE in the PERSONAL_DATA table.  If no phone exists, there is actually a space in that field - yes, ahhhh.  So, then I have it looking at a sub sql statement which will pull the PHONE field from the PS_TELEPHONE_NBR table.  Notice the T.EMPLID = P.EMPLID, this is how you link a table referenced in the derived field back to the table you have in your report - yes, another ahhhhh.  Finaly, if the PHONE from the PS_TELEPHONE_NBR table is not needed then return the HOME_PHONE from the PS_PERSONAL_DATA table.

Hope it helps.

RSGeek at WI dot RR dot COM
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close