I have two tables. One is a list of employees and info about them. The second is a table of personal phone numbers for employees. The two tables are joined by ‘employee_id’. There may be one or more entry in the personal_phone table for each employee. Ex:
Table 1 Table 2
Listserv Archives (BOB member since 2002-06-25)
Cindy,
If I understand correctly you want the report to look as follows:
EmpNO TEL_BUSN TEL_CELL PAGR
12345 1234567890 1234567899 1234567891
i.e. one row for each employee with the three ph nos in the same row.
You can create the following formula in the three phone columns:
Column1: =Max() In ( ) Where (=“BUSN”)
Column2: =Max() In ( ) Where (=“CELL”)
Column3 =Max() In ( ) Where (=“PAGR”)
Please note that EMPNO should be dimension object. The max esentially avoids the
multiple rows that you want to eliminate. You need not create three aliases etc.
if this is the purpose.
Pls let me know if you need any further help.
Manoj
______________________________ Reply Separator _________________________________
Author: Business Objects Query Tool BUSOB-L@LISTSERV.AOL.COM at INTERNET
Date: 01/15/1999 12:28 PM
I have two tables. One is a list of employees and info about them. The second
isa table of personal phone numbers for employees. The two tables are joined by
‘employee_id’. There may be one or more entry in the personal_phone table for
each mployee. Ex:
Table 1 Table 2
Listserv Archives (BOB member since 2002-06-25)
I think there are 2 options: 1 is the solution you outlined, the other is
using formulae in the report. Manoj suggested the following formulae.
Column1: =Max() In ( ) Where (=“BUSN”)
Column2: =Max() In ( ) Where (=“CELL”)
Column3 =Max() In ( ) Where (=“PAGR”)
Both solutions produce the correct result. The outerjoin solution takes a
bit longer at the query time, the formula solution takes longer in the
reporter module. If the reports contain alot of rows, the computation delay
can be annoying.
Personally, I prefer the solution you came up with. The outer joins should
peform quite well since you are not dealing with thousands of rows per
employee.
There is a 3rd option (which is probably overkill for this). You could
create separate contexts for each type of phone number. This would
eliminate the outer join.
Listserv Archives (BOB member since 2002-06-25)
Personally, I prefer the solution you came up with.
The outer joins should perform quite well since you
are not dealing with thousands of rows per
employee.
Ditto. The user doesn’t have to create the formulas for every report.
It is a performance issue, but on Oracle 8 I have tested similar aliases
against 1,000,000 rows with acceptable performance.
Greg Mills
Conoco Inc.
(580) 767-5597 ETN 442-5597
s-greg.mills@usa.conoco.com
Listserv Archives (BOB member since 2002-06-25)
On Sat, 16 Jan 1999 08:38:12 Steve Krandel wrote:
I think there are 2 options: 1 is the solution you outlined, the other isusing formulae in the report. Manoj suggested the following formulae.
Column1: =Max()In()Where(=“BUSN”)
Column2:=Max()In()Where(=“CELL”)
Both solutions produce the correct result. The outerjoin solution takes a bit longer at the query time, the formula solution takes longer in the
reporter module. If the reports contain alot of rows, the computation delay can be annoying.
Listserv Archives (BOB member since 2002-06-25)