Hie Guys,
I needed some help regarding my issue creating derived tables in the Universe Design Tool 4.0. When I click on "check syntax" while creating the derived table, I am getting the following error message - "Each calculated column must have an explicit name". The query works fine in Crystal Reports. I've attached the SQL query below. Is it because there is no comma after "ti.issue_id" at the end of the SELECT statement? If so, how did it work in crystal reports? Could anyone tell me what is wrong with the SQl? 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
IFNULL(tcv_print.custom_value,0)
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
LEFT JOIN tbl_custom_value AS tcv_print
ON ti.issue_id = tcv_print.issue_id
AND tcv_print.custom_field_id = 343
WHERE ti.process_id = 81
AND ts.step_type_id<3
AND ts.sort_order >= ts_in_flt.sort_order
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.