You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Displaying Home, Cell and Business Phone numbers in one row

9 replies [Last post]
Ed-D's picture
User offline. Last seen 9 years 24 weeks ago. Offline
Joined: 05/14/2008
Posts: 25

Hello,

I am stumped as to how I can do this. Here is my quest:

In PSoft Query 8.8xxx (web-based, and I can view the SQL but not edit the SQL) I am pulling only the Employee Home Addresses from a Personal Data Table. Phone numbers are stored in the PERSONAL_PHONE Table. There are several types of phone numbers that can be stored for each employee, but not everyone has a number stored for every type.

I have linked the PERSONAL_PHONE Table to the PERS DATA Table with a left outer join because some may not have any phone numbers at all.

If I just run the query, I get multiple rows of data for every employee who has multiple types of phone numbers stored, one for each type of phone number an employee has listed. But - I need to have this data in columns for only the following types so only one row is generated per employee, with phone numbers in this order:

Home Phone
Cell Phone
Business Phone

I have tried linking the PERSONAL PHONE Table multiple times and setting a different condition for each linkage - one with Home Phone, One with Cell, etc. But if the employee does not have a phone number returned in the very first phone number column, all subsequent columns are returned blank.

If the employee has Home, Cell and Business numbers, all 3 come out perfectly. Wherever one in that exact sequence is missing, the next ones will not appear - even if it exists in their file. No Home Phone means no numbers are returned - even though the employee has a Cell and/or Business phone number in their record.

Can anyone help?

Many thanks,

Ed

Lepa's picture
User offline. Last seen 2 years 6 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Displaying Home, Cell and Business Phone numbers in one row

Ed-D,

Quote:
If I just run the query, I get multiple rows of data for every employee who has multiple types of phone numbers stored, one for each type of phone number an employee has listed. But - I need to have this data in columns for only the following types so only one row is generated per employee.

I'm afraid this can not be done in PeopleSoft query. You will have one row for each phone type unfortunately. Please let me know if you can prove me wrong! ;)

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ashar's picture
User offline. Last seen 4 years 39 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Displaying Home, Cell and Business Phone numbers in one row

You can create a PS Query based on the SQL,

SELECT A.EMPLID, A.NAME, ISNULL(B.PHONE, 'Not Available') As 'Home Phone', ISNULL(C.PHONE, 'Not Available') As 'Cell Phone', ISNULL(D.PHONE, 'Not Available') As 'Business Phone'
FROM PS_PERSONAL_DATA A Left Outer Join PS_PERSONAL_PHONE B On B.EMPLID = A.EMPLID AND B.PHONE_TYPE = 'HOME'
Left Outer Join PS_PERSONAL_PHONE C On C.EMPLID = A.EMPLID AND C.PHONE_TYPE = 'CELL'
Left Outer Join PS_PERSONAL_PHONE D On D.EMPLID = A.EMPLID AND D.PHONE_TYPE = 'BUSN'

Is this what you are looking for?

Ed-D's picture
User offline. Last seen 9 years 24 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Displaying Home, Cell and Business Phone numbers in one row

Hi Ashar,

The problem doing this is when I am adding additional tables, PS will only allow Left Outer Joins from the newly added table to the last existing table in the Query. So I can do a Left Outer Join between B.PHONE and A.PERSONAL_DATA -- but I cannot do a Left Outer Join between C.PHONE and A.PERSONAL_DATA. The next Left Outer Join I would be permitted to do would have to be from C.PHONE to B.PHONE.

I'm going to keep playing with this, but during slow times in my other projects.

Thanks,

Ed

oddsnends's picture
User offline. Last seen 11 years 5 weeks ago. Offline
Joined: 03/28/2008
Posts: 16
Re: Displaying Home, Cell and Business Phone numbers in one row

Do you have the option of creating a view?

Then you can
create a view (EMPLID,BUSN_PHONE,CELL_PHONE,HOME_PHONE) joining PERSONAL_PHONE to itself threee times.
make the view avalible to query
select the values from the view.

Create View (If you go this far you may want to add all phone #'s you store)
select a.emplid
,a.phone
,b.phone
,c.phone
from ps_personal_phone a
,ps_personal_phone b
,ps_personal_phone c
where a.emplid = b.emplid
and a.emplid = c.emplid
and a.phone_type = 'BUSN'
and b.phone_type = 'CELL'
and c.phone_type = 'HOME'

Ashar's picture
User offline. Last seen 4 years 39 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Displaying Home, Cell and Business Phone numbers in one row

Hi Ed,

In that case, you can create a view and use that view in the PS Query.

/Ashar

Ed-D's picture
User offline. Last seen 9 years 24 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Displaying Home, Cell and Business Phone numbers in one row

Hi oddsnends,

I have never created a view in PSoft before and I am thus unfamiliar with the term, and how to go about setting one up.

Is the view created in an expression? If it must be created in the SQL, I am out of luck as we do not have access to editing the SQL code. Everything we can do involves Criteria, Prompts, and Expressions.

Or am I adding/joining Tables, in addition to the Personal_Data Table (where I would get the name and address information on the employees) and somehow linking them to output one single row of data for each employee which displays each type of phone number I have identified in the view?

Thanks,

Ed

Ashar's picture
User offline. Last seen 4 years 39 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Displaying Home, Cell and Business Phone numbers in one row

Hi Ed,

I guess it is not possible in PS Query, you need to create a Record (Record type: SQL View) in App Designer with the SQL Text i posted earlier and use this view in the PS Query.

/Ashar

Ed-D's picture
User offline. Last seen 9 years 24 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Displaying Home, Cell and Business Phone numbers in one row

Hello everyone,

It turns out that there is a way to do this after all. I have played with it on and off and come up with a solution. Here is what I did, along with the criteria...

Building the query, I started with this table:
A.HRO_DASHBOARD
eff dt <=current date.

I did not use any other criteria, nor did I use any of the fields in table A to output data.

Then I added the table - regular joined to A:
B.EMPLOYEES
eff dt <=current date
B.EMPLID = A.EMPLID
LOCATION = 01234 (or whatever location you want to use)
EMPLOYEE_STATUS In List (A,L,P)

I used several fields from the B.EMPLOYEES table for report output.

Then I added a table with a Left Outer Join to B
C.PERSONAL_ADDRESS
eff dt <=current date
B.EMPLID = C.EMPLOYEE_ID

I used Home Address data from table C as output fields.

Then I added a table - regular joined to B:
D.HRO_DASHBOARD
eff dt <=current date
D.EMPLID = B.EMPLOYEE_ID

I used no fields from table D.

Then I added a table with a Left Outer Join to D
E.PERSONAL_PHONE
eff dt <=current date
E.EMPLID = D.EMPLOYEE_ID
PHONE_TYPE = PCEL
Pulled Personal Cell as output field

Then I added a table -regular joined to B:
F.HRO_DASHBOARD
eff dt <=current date
F.EMPLID = B.EMPLOYEE_ID

I used no fields from table F.

Then I added a table with a Left Outer Join to F
G.PERSONAL_PHONE
eff dt <=current date
G.EMPLID = F.EMPLOYEE_ID
PHONE_TYPE = BUSN
Pulled Business Phone as output field

And you can keep repeating the last two table joins in order to obtain several different types of phone numbers for each employee. All of those different phone number types will be output in only one data row for each employee. Where an employee does not happen to have a particular type of number listed in the PERSONAL_PHONE table (like Personal Cell, for example) no value is output for that column.

As long as you specify in the criteria for each Left Outer Join which one type of phone number to retrieve (PCEL, or BUSN, etc.) for that particular output column, you won't get those pesky multiple output rows for the same employee that display all of the other phone types that are on file for the employee.

By doing it this way you can get around the limitation in PS Query of Left Outer Joins only being possible between the last table and the one you are adding. However, trying to do consecutive Left Outer Joins with between PERSONAL_PHONE (even if it is the last table) and then adding another instance of PERSONAL_PHONE to it does not work.

I can not explain why what I did works, but it does. It made me very happy when it did work!

Best regards to all,

Ed

Lepa's picture
User offline. Last seen 2 years 6 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Displaying Home, Cell and Business Phone numbers in one row

Ed-D,

Thanks for spending the time to revisit the thread and share your solution with us. Greatly appreciate it!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog