Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Employees dropping off when using Telephone_Nbr table

Status
Not open for further replies.

abowling

Technical User
Apr 27, 2005
33
US
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
 
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

Specializing in ReportSmith Training and Consulting
 
Oddly enough, it's not effective dated. Should I still try the list option?
 
I tried an "any" list where PHONE is equal to HOME or null, but no luck.
 
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.


Specializing in ReportSmith Training and Consulting
 
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" )
 
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)

Specializing in ReportSmith Training and Consulting
 
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.
 
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.
 
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.
 
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?

Specializing in ReportSmith Training and Consulting
 
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.
 
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.


Specializing in ReportSmith Training and Consulting
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top