Hie Universe Design Gurus,
I need to design a new universe in UDT based on a SQL query which I’ve attached at the bottom. Then, I need to create a Webi Report from the Universe. The universe is suppose to be based on the report generated from the query. The universe is going to have only one report as of now (I know right !!!). The query contains a lot of joins, aliases & sub queries, which is why I’m confused as to how to go about it. I’ve figured out the tables along with their aliases based on the SQL query, which I need in the universe. Correct me if I’m wrong.
They are - [b]tbl_issue /b, tbl_category (tc), tbl_custom_value (tcv_project, tcv_sponsor, tcv_end_date, tcv_benefits, tcv_benefits_type, tcv_end_dt), tbl_step (ts, ts_in_fit), tbl_issue_step (tis, tis_start), [b]tbl_process /b, [b]tbl_append /b, tbl_custom_field_option (tcfo).
Could anyone tell me how do I figure out the joins and what do I do with all these aliases that are in the SQL query?
Additional info - I do not have access to the database, but I have access to a pre-existing universe that has all the tables. Also, I will have access to the software to test the query in a day or so… if that helps. I would really appreciate your insights into this problem. Thanks.
SELECT
DISTINCT
tc.category_descr AS cosa,
tcv_project.custom_value AS project_id,
ti.issue_title AS project_name,
tcv_sponsor.custom_value AS exec_sponsor,
tis.own_employee_nbr AS bmpe_name,
DATE_FORMAT(tis_start.step_dt, %m/%d/%Y) AS start_dt,
tcv_end_dt.custom_value AS end_dt,
ts.step_descr AS current_phase,
CONCAT((,DATE_FORMAT(ta.insert_dt, %m/%d/%Y),) - ,ta.append_txt) AS status_descr,
tcv_benefits.custom_value AS benefits,
tcfo.option_descr AS benefits_type,
ti.issue_id
FROM tbl_issue AS ti
INNER JOIN tbl_category AS tc
ON ti.catagory_id = tc.catagory_id
LEFT JOIN tbl_custom_value AS tcv_project
ON ti.issue_id = tcv_project.issue_id
AND tcv_project.custom_field_id = 126
LEFT JOIN tbl_custom_value AS tcv_sponsor
ON ti.issue_id = tcv_sponsor.issue_id
AND tcv_sponsor.custom_field_id =137
INNER JOIN (SELECT tis.issue_id, MAX(tis.issue_step_id) AS issue_step_id
FROM tbl_issue_step AS tis
INNER JOIN tbl_issue AS ti
ON ti.issue_id = tis.issue_id
WHERE ti.process_id = 81
GROUP BY issue_id) AS tis_max
ON ti.issue_id = tis_max.issue_id
INNER JOIN tbl_issue_step AS tis
ON tis_max.issue_step_id = tis.issue_step_id
INNER JOIN tbl_step AS ts
ON tis.step_id = ts.step_id
INNER JOIN tbl_process AS tp
ON ts.process_id = tp.process_id
INNER JOIN tbl_step AS ts_in_flt
ON tp.in_flight_step_id = ts_in_flt.step_id
INNER JOIN(SELECT issue_id, MAX(step_dt) AS step_dt
FROM tbl_issue_step
WHERE step_id = (SELECT in_flight_step_id
FROM tbl_process
WHERE process_id = 81)
GROUP BY issue_id) AS tis_start
ON ti.issue_id = tis_start.issue_id
LEFT JOIN tbl_custom_value AS tcv_end_dt
ON ti.issue_id = tcv_end_dt.issue_id
AND tcv_end_dt.custom_field_id = 131
LEFT JOIN (SELECT ta.issue_id, MAX(ta.append_id) as append_id
FROM tbl_append AS ta
INNER JOIN tbl_issue AS ti
ON ti.issue_id = ta.issue_id
WHERE ti.process_id = 81
AND ta.type_id = 2
AND ta.append_txt <> GROUP BY issue_id) AS ta_max
ON ti.issue_id = ta_max.issue_id
LEFT JOIN tbl_append AS ta
ON ta_max.append_id = ta.appen_did
LEFT JOIN tbl_custom_value AS tcv_benefits
ON ti.issue_id = tcv_benefits.issue_id
AND tcv_benefits.custom_field_id = 141
LEFT JOIN tbl_custom_value AS tcv_benefits_type
ON ti.issue_id = tcv_benefits_type.issue_id
AND tcv_benefits_type.custom_field_id = 138
LEFT JOIN tbl_custom_field_option AS tcfo
ON tcv_benefits_type.custom_value = tcfo.custom_field_option_id
WHERE ti.process_id = 81
AND ts.step_type_id<3
AND ts.sort_order >= ts_in_flt.sort_order
jpandey80 (BOB member since 2013-09-30)
(BOB member since 2004-03-02)
(BOB member since 2005-03-01)