nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Unable to use certain MS SQL functions

tames

Hello. I read a post about the DATEDIFF function on this forum, but there was no solution at that time. I am creating a new post to see if anyone has resolved this.

In MSSQL, there are some date functions that have a first parameter of "datepart". This datepart must be in this form (example using the dateadd function):

DATEADD(ms, 86399997, '06/02/2011')

where the "ms" is milliseconds. I am getting an error: Unable to find column ms.

I have tried 'ms' and "ms" and [ms]. None of these will work. I also tried to create a dummy column alias for the ms using "1 as ms", but nothing (that was a suggestion from the older post).

Needless to say that queries with these functions run perfectly within the MSSQL Management Studio and in java programs using JDBC

Some other MSSQL functions that have this are DATEPART and DATEDIFF.

I will say that these functions were working in previous versions of BIRT. I am trying to work with report designs that were created a couple years ago and am having a great deal of trouble. Currently using Eclipse 3.7.1

Edited: This is occurring when creating a new Data Set. With the original data sets, BIRT would not refresh or show me the query statement since it was getting this error.

Find more posts tagged with

Comments

mwilliams

Are you able to use these functions in any other program besides BIRT? Like it's possibly a limitation of the driver? Have you seen if there is an updated driver you can use?

As a workaround, you could just add the ms in BIRT using the BIRT functions.

tames

Thanks for the reply!

I found the issue - not sure why it does this, but:

There are two ways to configure a jdbc data SOURCE.
1. JDBC Data Source
2. JDBC Data Source for Query Builder

I was using #2 when I defined my data source and had the problems. I created a new data source using #1, and when I created the data SET, I chose SQL Select Query. The SQL then ran fine using the the MSSQL functions.

If anyone else is having this issue, I hope this helps.

Thanks!

mwilliams

No problem. Glad you found a way around the issue. Not sure why the same query would work one way, but not the other, but still glad you got it working. Let us know whenever you have questions!

Linda Chan

>>I was using #2 when I defined my data source and had the problems. I created a new data source using #1, and when I created the data SET, I chose SQL Select Query. The SQL then ran fine using the the MSSQL functions.

When you use the "JDBC Data Source for Query Builder" data source type, its corresponding data set tool is the graphical SQL Query Builder. It generates and parses standard SQL syntax only. So any DB dialect, such as MSSQL-specific functions, are not supported.

Whereas, the Textual Query Editor for the "JDBC Data Source" type has no SQL parser; it simply passes through an user-defined SQL query to the underlying JDBC driver. Thus, DB-specific SQL query would run fine.

Linda

mwilliams

Thanks for the explanation, Linda!