Employees dropping off when using Telephone_Nbr table

[abowling]

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

 

[CharlesCook]

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

 

[abowling]

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

 

[CharlesCook]

Is there anything in the Selections for this table?

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[abowling]

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

 

[CharlesCook]

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

 

[abowling]

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" )

 

[CharlesCook]

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

 

[abowling]

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.

 

[abowling]

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.

 

[CharlesCook]

Try this example:

http://charlescook.com/Examples/Phone.rpt

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[abowling]

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.

 

[CharlesCook]

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

 

[abowling]

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.

 

[CharlesCook]

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

 

[tol514]

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

 

[jkgs]

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

 

[FireGeek21]

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