Error in using date functions in birt 2.5.2

Hi All,

I have a query regarding dataset creation.

I am using birt 2.5.2 viewer in eclipse galileo.

The problem is when I create a dataset whose query contains any date function.

I am getting the error as "Unrecogznied column MM in table. Some sql builder functions are disabled. To enable this change the syntax and save the statement"

That query works fine directly on sql server without any problem.

Please help me out to solve it.

Regards

Find more posts tagged with

Comments

Hans_vd

Can you post the query?

Yog

select rcm.MBA_id,rcm.Incentive_Date as DateOfPayment, rcm.Amount,
DATEADD(YYYY, -1, rcm.Incentive_period) as DateOfTagging from table1 rcm,
table2 rid
where rid.incentive_date between '2011-01-01' and '2014-01-01'
and rcm.Is_Tag='Y' and (rcm.recruiter_id=rid.recruiter_id or rcm.MBA_id=rid.recruiter_id)

It gives me the error: Unrecognized column YYYY in the table.Some sql builder functions are disabled. To enable this change the syntax and save the statement"

but if I put only "select DATEADD(YYYY, -1, rcm.Incentive_period) as DateOfTagging from table1 rcm" this works.

Please let me know what is wrong with the query.

Thanks in advance

Hans_vd

That's weird. Seems like a database driver issue.

You could try using the 'year' keyword instead of 'YYYY' and see if that works. (without the quotes of course)
And maybe you could check if there are any updates available on the database driver you are using.

Yog

I have tried with 'year' too. But same problem occurs. Its really weird.

If we assume this is a database driver error, how come the simple query like "select DATEADD(year,rcm.incentive_period) from table1 as rcm" works fine.

The results are quite contradictory.

Let me know if you have any other findings or inputs to it.

Thanks

Hans_vd

I'd guess that the problem arises as soon as you have two tables in the FROM.
If there is only one table in the FROM, there is no need to check what column comes from what table, so the check is not executed. If there are two tables in the from, the parser takes all parts of the query of which it thinks it is a column and tries to find out in which table the column resides.

I can't test it though, I have no access to a SQL Server installation.

Yog

Yes, this is quite logical and is practical too. Tried out few cases, the praser is working in the similar fashion thus causing a problem. How can we solve it or get a feasible solution? Is this a bug in the birt reporting plugin or anything else? 
 
Regards 
 
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="114722" data-time="1362153762" data-date="01 March 2013 - 09:02 AM">
I'd guess that the problem arises as soon as you have two tables in the FROM. 
If there is only one table in the FROM, there is no need to check what column comes from what table, so the check is not executed. If there are two tables in the from, the parser takes all parts of the query of which it thinks it is a column and tries to find out in which table the column resides. 
 
I can't test it though, I have no access to a SQL Server installation. </blockquote>

Hans_vd

Hi, 
 
I still think this is a bug in the database driver, did you check for a newer version? 
 
What happens if you write the query like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select rcm.MBA_id,
rcm.DateOfPayment,
rcm.Amount,
rcm.DateOfTagging
from ( select MBA_id,
Incentive_Date as DateOfPayment,
Amount,
DATEADD(YYYY, -1, Incentive_period) as DateOfTagging,
Is_Tag,
recruiter_id
from table1
) rcm,
table2 rid
where rid.incentive_date between '2011-01-01' and '2014-01-01'
and rcm.Is_Tag='Y'
and ( rcm.recruiter_id=rid.recruiter_id or rcm.MBA_id=rid.recruiter_id
</pre>

Yog

Great... It works. The findings are correct. Also the solution given works

Thanks 
 
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="114733" data-time="1362173015" data-date="01 March 2013 - 02:23 PM">
Hi, 
 
I still think this is a bug in the database driver, did you check for a newer version? 
 
What happens if you write the query like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select rcm.MBA_id,
rcm.DateOfPayment,
rcm.Amount,
rcm.DateOfTagging
from ( select MBA_id,
Incentive_Date as DateOfPayment,
Amount,
DATEADD(YYYY, -1, Incentive_period) as DateOfTagging,
Is_Tag,
recruiter_id
from table1
) rcm,
table2 rid
where rid.incentive_date between '2011-01-01' and '2014-01-01'
and rcm.Is_Tag='Y'
and ( rcm.recruiter_id=rid.recruiter_id or rcm.MBA_id=rid.recruiter_id
</pre></blockquote>

Hans_vd

Great indeed!
I suppose the problem also exists when using other functions like datediff, datepart,... ?

Yog

Yes, actually i had tried every possible date function with it like datepart, datediff, datename, etc. Same problem. Anyway, but now atleast I know how to handle it.

Thanks a lot ! 
 
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="114739" data-time="1362217555" data-date="02 March 2013 - 02:45 AM">
Great indeed! 
I suppose the problem also exists when using other functions like datediff, datepart,... ? </blockquote>