SQL and reports
565200Apr 4 2008 — edited May 29 2008I did to do a report in report 6i / bi publisher
I have a the following select:
select r.role_resource_id, r.role_type_name, r.role_name "org name", r.RES_RL_START_DATE, r.RES_RL_END_DATE, s.resource_number "org number",
s.resource_name
from jtf_rs_defresroles_vl r, JTF_RS_DEFRESOURCES_VL s
where s.resource_id = r.role_resource_id
and r.role_type_name = 'Sales Compensation' and r.role_name = 'Subscriber Organiser'
AND ((r.RES_RL_START_DATE <= (:p_end_date))
and (r.RES_RL_END_DATE >= (:p_start_date))
or r.RES_RL_END_DATE is null)
union
select r.role_resource_id, r.role_type_name, r.role_name "hdc name", r.RES_RL_START_DATE, r.RES_RL_END_DATE, s.resource_number "hdc number",
s.resource_name
from jtf_rs_defresroles_vl r, JTF_RS_DEFRESOURCES_VL s
where s.resource_id = r.role_resource_id
and r.role_type_name = 'Sales Compensation' and r.role_name ='Subscriber Carrier'
AND ((r.RES_RL_START_DATE <= (:p_end_date))
and (r.RES_RL_END_DATE >= (:p_start_date))
or r.RES_RL_END_DATE is null)
I need to dispaly the org number and org name as well as the hdc number and hdc name in my report.
but because they both using the same coloumns i cant get the carrier as well as the organiser information. i need to seperate the 2 sets of information.