| Problem with date and sql statement / filter [message #55557] |
Sat, 09 July 2005 08:00  |
Eclipse User |
|
|
|
Originally posted by: birgit.dippelreiter.promac.at
Hi!
I have a problem with my select statement. I select all entries from a
database which are between two dates; but in the sql statement the
where-clause didnŽt work. So I gave the dates to the filter and now it
works.
Now my problem is, that the two dates have to be flexible parameters so
that every user can insert different dates. But I only get errors when I
set parameters for the dates.
Does anyone know what I did/do wrong?
Or does anyone know how I have to do?
Thanks
Birgit
|
|
|
| Re: Problem with date and sql statement / filter [message #55612 is a reply to message #55557] |
Sat, 09 July 2005 10:34  |
| Eclipse User |
|
|
|
Originally posted by: none.none.none
Hi Birgit,
I seem to have a lot of date parameter issues as well. Here are a few
things that I did, that you can try to get it working at least temporarily.
I don't think this is the 'correct' approach, but as responses in this group
are few and far between, I figure this might at least get you going until we
get the 'right' answer....
- First of all, use the "?" notation in the SQL query where you want the
dates.
- In your data set definition add a corresponding parameter for each date.
- Add a default date to each one. The default format for BIRT is like
this,"01/30/2005 00:00:00 AM", for example (make sure you put the quotes
around it).
- Create a corresponding number of Report Parameters. When I created the
report parameters as Date parameters, I had problems... BIRT kept
complaining that it couldn't create a java.util.Date from my params. So my
temporary (hopefully) workaround has been to define my report params as
String. Then things work just fine. The obvious down-side to that, is that
if you use the built-in interface for user-input, there'll be no validation
for date patterns because BIRT doesn't know what to expect.
I had a similar issue when trying to link to tables by date parameters. I
had to create a string representation of my dates in the query as a separate
column and then link on those in BIRT. Either BIRT has some nasty Date
issues, or I'm going about it all wrong... but I am at least running for the
time being. Hopefully somebody in the group will speak up with more
definitive info.
I'm using 1.0.1 M1.
Good luck.
"Birgit" <birgit.dippelreiter@promac.at> wrote in message
news:c86196e53baee61bd96b1844bcd7cd43$1@www.eclipse.org...
> Hi!
>
> I have a problem with my select statement. I select all entries from a
> database which are between two dates; but in the sql statement the
> where-clause didn
|
|
|
|
|
|
| Re: Problem with date and sql statement / filter [message #56405 is a reply to message #55984] |
Tue, 12 July 2005 02:37 |
| Eclipse User |
|
|
|
Originally posted by: none.none.none
Ok, sorry, I think I misunderstood before. The problem is not error
messages anymore, the problem is that it's not filtering data based on dates
through SQL. I'm not sure what the problem is (I've only been using BIRT
for about a week, so I'm a newbie as well)... but I do have a few
suggestions you can try in case you haven't done so already..
When you put parameters on a dataset, you have to put in default values. Do
your default values work correctly? If your dataset preview is returning
all data regardless of what you put as default values in your dataset
parameters, then I would suspect maybe your SQL is not set up correctly. If
it does filter data correctly when you use preview (which utilizes the
default values), but not when you do manual data entry through the client
interface, then I would suspect that your report parameters are not linked
correctly to your dataset parameters.
One thing that would help you narrow down the problem is turning on SQL
tracing in your database and inspect the actual query that is being executed
on the server.... see what date values are being requested by BIRT.
That's about all I can think of right now... hopefully that helps a little.
"Birgit" <birgit.dippelreiter@promac.at> wrote in message
news:32cb4aef3fc217110def55e225270242$1@www.eclipse.org...
> Hi!
>
> Yes, this is what I did, and it doesn
|
|
|
|
| Re: Problem with date and sql statement / filter [message #57380 is a reply to message #56938] |
Wed, 13 July 2005 04:48 |
| Eclipse User |
|
|
|
Originally posted by: birgit.dippelreiter.promac.at
Hi!
I solved the problem. The database is inconsistant in formatting dates.
There are different formats how the date is in the database. So I had to
change the format in the sql query and now it works.
About the problem with the paramters. I formatted the date parameters(in
the sql parameter and the reportparameter) as string and it works.
So thanks a lot!
Birgit
|
|
|
Powered by
FUDForum. Page generated in 0.04832 seconds
Home