I have a report SQL query in iReport by parameters [$P{DATE_FROM} + $P{DATE_TO}] for a PostgreSQL datetime column [offer_date] in iReport by parameter:
SELECT *
FROM tbl_customer
FULL OUTER JOIN tbl_offers ON (tbl_offers.id_customer = tbl_customer.id_customer AND tbl_offers.offer_no = $P{OFFER_NO})
OR (tbl_offers.id_customer = tbl_customer.id_customer AND tbl_offers.offer_date BETWEEN $P{DATE_FROM} AND $P{DATE_TO})
OR (tbl_offers.id_customer = tbl_customer.id_customer AND $P{OFFER_NO} IS NULL AND $P{DATE_FROM} IS NULL AND $P{DATE_TO} IS NULL)
FULL OUTER JOIN tbl_offers_items ON tbl_offers_items.id_offer = tbl_offers.id_offer
WHERE tbl_customer.id_customer = $P{CUSTOMER_ID}
ORDER BY tbl_offers.offer_no;
I would like to get all related offers of the customer if the parameters [$P{OFFER_NO} + $P{DATE_FROM} + $P{DATE_TO}] are NULL. Else it should return only an offer with the value of $P{OFFER_NO} or the offers of the daterange between $P{DATE_FROM} and $P{DATE_TO}.
But I don’t get this working because of the parameter for the datetime column [offer_date]. If I execute the query with real values in the SQL Editor in Servoy Dev. I get the results. Do I make a Preview in iReport and set the parameter values in the promp, I get errors in the output!?
What Parameter Class shuld the Parameters $P{DATE_FROM} + $P{DATE_TO} have?
I tried with the property settings java.lang.String, java.util.Date and java.sql.Timestamp without success.
How should I set the parameters in my Servoy method?
Are you able to run the report in iReport ‘Preview’ mode (sans Servoy)?
You can get iReport to prompt you for the parameter input by selecting the parameter from the Report Inspector (left-side of display), then checking ‘Use as prompt’ in the parameter properties (right-side of display). Do this for all the paramters your SQL statement is expecting. Then, when you preview the report, iReport will prompt you to enter the parameters. This way, you can debug your report without using Servoy.
What Parameter Class shuld the Parameters $P{DATE_FROM} + $P{DATE_TO} have?
I have used both ‘java.util.Date’ and ‘java.lang.String’ (for display only) classes in reports with no problem. Since you are using the parameters in the SQL statement, you should pass them as ‘java.util.Date’.
I have tried the iReport build in preview function with the parameters prompts.
But if I enter e.g. 2012-05-10 for DATE_FROM and 2012-05-15 for DATE_TO as Class java.lang.string, I get this error:
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= character varying
Do I change the parameters Class to java.util.Date, the error output is:
Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $5
Both results are PostgreSQL exceptions, and I have limited experience with that database engine. Perhaps, one of the PostgreSQL experts would care to comment?
I am guessing that the first exception is due to an incompletely specified ‘datetime’ entry. You could try the ‘2012-05-15’ with variations of the time specification (e.g. ‘00:00:00.000’, etc.).
I am baffled by the second exception but, again, am guessing it is the manner in which the ‘datetime’ data is being entered. Here is a link that may help with that:
You could create a simple report with one ‘datetime’ parameter and experiment with it until you figure out what PostgreSQL is expecting.
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= character varying
Like in most cases the error says it all. You can’t use a timestamp in a condition against a varchar (character varying). It has no operator (>=) to work with those 2 datatypes.
If you want to pass a string you should cast it to a timestamp in SQL using the to_timestamp() function. You can pass any string together with the mask it should use (very convenient!).
See http://www.postgresql.org/docs/9.1/stat … tting.html .
PostgreSQL is expecting two strings to_timestamp(text, text) and is receiving to_timestamp(character varying) so you will have to pass in the two strings as two arguments seperated by a comma. Also be careful when converting dates to timestamp because selecting a daterange with timestamps can be tricky. I prefer to convert the datetime to a date and compare with that.