Error creating derived table !!!

[former_member672670]

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 

[Former Member · Accepted solution]

Instead of IFNULL use a case statement. NVL won't work in all environments:

CASE WHEN tcv_print.custom_value IS NULL THEN 0 ELSE tcv_print.custom_value END

Also, yes, you may need the comma

[former_member672670 · Accepted solution]

Thank You. That worked.

[former_member193452]

Since the Sql works in crystal reports or can be run directly on your database,  I think the issue would be something minor.  Such as using the qualified domain names in your derived table.

a simple way to check from the universe layer, is to create a new test derived table and select the objects from the underlying tables  Just a few not the whole sql.

As you see how the sql is generated, with correct format expected by the universe designer.

You can adapt your query as necessary format.

[former_member672670]

It's difficult to reproduce the query with the database tables as there are many joins and aliases in the SQL which is difficult for me to figure out. I ran the SQL in Squirrel SQL and IDT. It runs fins. The problem is running it in UDT. My guess is this line of code - IFNULL(tcv_print.custom_value,0). If I remove it however, I get an error. Thanks.

[Former Member]

Easy way to solve the issue by executing same query in SQL client and check whether you are getting any error.

Most probably you will get. After solving the issue, paste the same query in derived table.

[former_member672670]

I ran the query in SQuirrel SQL & it runs without an error. Also, it runs in IDT. I'm guessing its this line - IFNULL(tcv_print.custom_value,0) that's causing the problem in UDT. If i remove it though, then I get an error. Thanks.

[Former Member]

You can not have a IFNULL function it should be NVL and the in your statement the comma is missing as well that is causing the issue

[Former Member]

Can you explain what the below lines are doing in your code? That do not look correct syntactically..

ti.issue_id

IFNULL(tcv_print.custom_value,0)

[former_member672670]

I got the SQL from the manager, so I do not know what that line is doing there. However, I ran the query in Squirrel SQL & Information Design Tool and it runs without an error there. I don't know why it's not running in Universe Design Tool. I am guessing its that line of code - IFNULL(tcv_print.custom_value,0). Do you know what is wrong with it? Thanks.

[Former Member]

If you database is Oracle try with a NVL(tcv_print.custom_value,0) along with a comma in the begining (,)

[former_member672670]

The database is MySQL and yes, I was missing a comma after "ti.issue_id". But, how did it run successfully in IDT & not UDT?

[Former Member]

It will fail in any SQL parcer let it be UDT or IDT or database client tools as ultimately it fires the same query to database which will fail. Please check once again it should fail in IDT but even though it succeeds (may be a bug in IDT sql parser) it will fail any report you will build on top of it...

[former_member672670]

I just cannot figure out why the query is giving an error in UDT. I am thinking about recreating the report by joining the tables based on the SQL, thus creating  a full universe.