mbmorgan Posted April 25, 2014 Share Posted April 25, 2014 In Jaspersoft Studio Professional...my SQL query contains a clause like "WHERE column_A >= $P{From_Date}", and my question is: how do I set the parameter 'From_Date'? Class: java.util.dateIs for Prompting: checkedDefault Value: new SimpleDateFormat("MM/dd/yyyy").parse("01/01/2001")The column in question is of a Date datatype, in the MM/DD/YYYY format. [edit] - The problem comes when I try to read the fields from the database...I get "Error converting SQL statement into byte array..." What am I doing wrong? Any guidance is welcome. Thanks,M.B.Morgan Link to comment Share on other sites More sharing options...
bnee_1 Posted April 25, 2014 Share Posted April 25, 2014 Hi M.B.,Could you provide the full query? The error might be from something else in the query.I tested this query:SELECT employee.last_name, employee.birth_date FROM employee WHERE employee.birth_date <= $P{paramDate}[/code]which uses the sample foodmart db's employee table. birth_date's data type is Date, and the parameter paramDate is of type java.util.Date with the exact same default expression you are using.Another thing to check - in the Outline panel, under the Fields section, what is the Class for column_A? It should be java.util.Date. Link to comment Share on other sites More sharing options...
mbmorgan Posted April 25, 2014 Author Share Posted April 25, 2014 The query also contains a CASE expression, which I'm starting to think is the real cause of the problem. I posted another question about that on the community. Do you know if a CASE expression is allowed in the query dialog? Thanks. Link to comment Share on other sites More sharing options...
ybablyuk Posted April 28, 2014 Share Posted April 28, 2014 Default value for Date paramented should be specify as:new java.util.Date(97, 5, 1)[/code] Link to comment Share on other sites More sharing options...
mbmorgan Posted April 28, 2014 Author Share Posted April 28, 2014 Would you mind checking the query to see if the syntax is correct? I keep getting errors. This is a conditional query, set as a parameter (class java.lang.string), which is why I have it enclosed in quotes, but do I have quotes in the wrong place? "select count(field_1), field_2 from table where field_2 = ... and ProgStart >= '" + $P{From_Date} + "' and ProgStart <= '" + $P{To_Date} + "' and ... group by field_2" Link to comment Share on other sites More sharing options...
ybablyuk Posted April 28, 2014 Share Posted April 28, 2014 Hi,I can create report using query:select count(orderid) as o_count, shippeddate from orderswhere shippeddate >= $P{From_Date} and shippeddate <= $P{To_Date} group by shippeddate[/code]As example you can use my attached jrxml file. It's used sample sugarcrm database Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now