query works via SQLPLUS but not in Birt.

<p>Make sure that in the BIRT report the report parameter and the dataset parameter both are of type String.</p><p>Also, did you bind the report parameter three times to the data set?</p>

<p>Thanks for the reply.. yea, I've been playing around with the the report parameter and dataset parameter as types:</p><p> </p><p>[color=rgb(51,51,51);font-family:verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif;font-size:13px;]if I select the report parameter as Date I get: [/color]</p><p>ORA-01843: not a valid month</p><p>If I select it as a string I get: [color=rgb(51,51,51);font-family:verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif;font-size:13px;] [/color]</p><p>[color=rgb(51,51,51);font-family:verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif;font-size:13px;]ORA-01830: date format picture ends before converting entire input string[/color]</p><p> </p><p>When I always set the dataset parameter to a string.</p><p> </p><p>The actual data is datatype DATE</p>

<p>The parameter should be String, because in the query you are using to_char</p><p> </p><p>The error [color=rgb(51,51,51);font-family:verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif;font-size:13px;]ORA-01830: date format picture ends before converting entire input string [/color]means that the value of the parameter does not match the format string.</p><p>So are you really sure that</p><p>- the data set parameters are all of type String</p><p>- the report parameter that is bound to the dataset parameter is of type String</p>

<p>Still not getting it!</p><p> </p><p>in sqlplus I define a datetime and it returns a result:</p><div> </div><div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]var X DATETIME(20);[/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]exec :X := 'JUN-2013';[/font]</span></div></div><div> </div><p>(replacing ? by :X)</p><p>It's a date variable right?!</p><p> </p><p> </p><p>I am free to change the query to match the table...</p><p> </p><p>If I change the Report Parameter to string as well as the Data Set Parameter I get </p><p>ORA-01843: not a valid month..</p>

<p>Hi,</p><p> </p><p>if you want to define a parameter inside a query, you have to do like this</p><p>1) you have to create a parameter DateTime</p><p>2) in the query editor, type this</p><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><pre class="_prettyXprint _lang-sql">select to_number(to_char(last_day(to_date( ? ,'mon-yyyy')),'dd')) days, Y.*from ( select c, to_char( date_field, 'dd' ) day, d from t where date_field >= to_date( ? , 'mon-yyyy' ) and date_field < last_day( to_date( ? ,'mon-yyyy') )+1 ) pivot (sum(d) for day in('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31') ) Y order by c</pre></div><p>3) link the 3 query parameters (i.e. "?" ) with the report parameter</p><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;">[font="'courier new', courier, monospace;font-size:12px;"] [/font]</div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;">and it should work</div>

<blockquote class="ipsBlockquote" data-author="Glo_FR" data-cid="121182" data-time="1381421376"><div><p> </p><p>1) you have to create a parameter DateTime</p></div></blockquote><p> </p><p>I'm sorry, but that would be a mistake.</p><p>In the query there is </p><pre class="_prettyXprint">to_date (?, 'mon-yyyy')</pre><p>If you would provide a dateTime parameter value, Oracle will implicitly convert it to a varchar2, depending on the database's nls_date_format parameter. Which means unpredictable results.</p><p> </p><p>It is strange that when you change the parameters in BIRT to String datatype, you still get ORA-01843. That shouldn't be.</p><p>Can you create a very simple dataset with this query and show it on the report:</p><p>select ? from dual</p><p> </p><p>What is the output if you do that?</p>

<blockquote class="ipsBlockquote" data-author="Hans_vd" data-cid="121215" data-time="1381476818"><div><p>I'm sorry, but that would be a mistake.</p><p>In the query there is </p><pre class="_prettyXprint">to_date (?, 'mon-yyyy')</pre><p>If you would provide a dateTime parameter value, Oracle will implicitly convert it to a varchar2, depending on the database's nls_date_format parameter. Which means unpredictable results.</p></div></blockquote><p>Oups, you're right, <strong>The param have to be a string.</strong></p><p>Sorry for that</p><p> </p><p>BTW, thelcd, isn't it the same (more simple and with less conversion) if you do this ?</p><p>and then you have just one param to link</p><pre class="_prettyXprint _lang-sql">select *from (select extract(day from last_day(date_field)) as days, c, to_char(date_field, 'dd' ) day, d from t where to_char(date_field, 'MON-yyyy') = ? ) pivot (sum(d) for day in ('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31')) Yorder by c </pre>

<blockquote class="ipsBlockquote">Oups<p> </p></blockquote><p> </p><p>Are you French? :-)</p><p> </p><p> </p><p>btw, in another post I already suggested to use a crosstab instead of the pivot function, that would make the query even more simple. And BIRT crosstabs are very good in dealing with dates.</p>

<p>@Glo_FR</p><p> </p><p>Ok, you are French.</p><p>It's in your profile and even in your username...</p>

<blockquote class="ipsBlockquote" data-author="Hans_vd" data-cid="121222" data-time="1381485335"><div><p>@Glo_FR</p><p> </p><p>Ok, you are French.</p><p>It's in your profile and even in your username...</p></div></blockquote><p>

</p>

<p>Hi thianks again,</p><p>yea, the query:</p><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]select to_number(to_char(last_day(to_date(:X,'mon-yyyy')),'dd')) days, Y.*[/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"] from ( [/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]select c, to_char( date_field, 'dd' ) day, d[/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]from t[/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]where date_field >= to_date( :X, 'mon-yyyy' )[/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]and date_field < last_day( to_date( :X,'mon-yyyy') )+1 [/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"])[/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"] pivot (sum(d) for day in[/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"]('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31')[/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"] ) Y[/font]</span></div><div style="margin:0px;color:rgb(40,40,40);font-family:helvetica, arial, sans-serif;"><span style="font-size:12px;">[font="'courier new', courier, monospace;"] order by c [/font]</span></div><div> </div><div>In the following two examples I do not link to a report parameter:</div><div>And</div><div>1) it works when <strong>param_1 </strong>as Data Type <strong>String </strong>as Input with <strong>default value</strong> specified :<strong> 'Oct-2013' </strong>or <strong>'October-2013'</strong> etc..</div><div>(param_2 and param_3) etc.. (Note all 3 params for the data set are the same value) (with data/table shown above)</div><div> </div><div>When I set <strong>param_1</strong>,<strong>param_2</strong>, and <strong>param_3 </strong>to a <strong>Date/Time</strong> or to the <strong>Date </strong>Types (using input for the Default Value using the f(x) selector, fails.</div><div>ie. "2013-10-01 15:06:36.378+0200" </div><div>ORA-01843: not a valid month</div><div> </div><div>I get the same error (1843) if I use a BritDateTime function to enter a date value for the Defaul:</div><div>ie: BirtDateTime.addMonth("2013-09-01 15:12:11.277+0200",1)</div><div> </div><div>If I try to toString(BirtDateTime.addMonth("2013-09-01 15:12:11.277+0200",1))</div><div>I get:</div><div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"]Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details:[/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"]Failed to prepare the query execution for the data set: RP1_ID[/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"]Cannot convert the parameter value [object Object] to type class java.sql.Date.[/font]</span></div><div><span style="font-size:12px;">[font="'courier new', courier, monospace;"]Can not convert the value of [object Object] to java.sql.Date type.[/font]</span></div><div> </div><div>Am not French, but live in France! .. have a soft side for wine & smelly cheese

</div></div>

<p>Just adding to this.. </p><p>If I change my query first parameter to:</p><p>[color=rgb(40,40,40);font-family:'courier new', courier, monospace;font-size:12px;]select to_number(to_char(last_day(to_date(:X,'MM-yyyy')),'dd')) days, Y.*[/color]</p><p> </p><p>and my param_1 (String) to '10-2013'</p><p>it works, where as before when the query was 'mon-yyyy' and param_1 (String) '10-2013' it didn't work.</p><p> </p><p>so seems to be some compatability thing between the query ie.</p><p>mon-yyyy only works with string 'October-2013'</p><p>and </p><p>MM-yyyy only works with String '10-2013'</p><p> </p><p>So am trying to figure out now what the actual query that lists possible months in the reports could output as..</p><div>[font="'courier new', courier, monospace;"]SELECT DISTINCT TRUNC(STARTTIME, 'MM') AS Month[/font]</div><div>[font="'courier new', courier, monospace;"]from MYTABLE[/font]</div><p> </p><p>is in terms of format in the query....</p>

<p>Been messing about to provide input to the Query as 'October-2013' in String Format as works when hardcoded...</p><p>and get this birt error:</p><p> </p><p>Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details: Failed to prepare the query execution for the data set: RP1_TABLE_STRING Cannot convert the parameter value 'January-2013' to type class java.sql.Timestamp. Can not convert the value of 'January-2013' to Date type.</p>

<p>Now it works...! yaah Friday and all..</p><p> </p><p>My parameters in the main query are String, and linked to a RP_month report parameter:</p><p>RP_month parameter is an unformatted String Type pointing to a dynamic data set String Column of a dataset.</p><p>This dataset converts the date using birt functions to a string (in the format that the query wants ie. October-2013</p><p>based on select option to_date(? ,'Mon-YYYY') </p><p>Script is as follows:</p><p> </p><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]var bob;[/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "1") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "January" } [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "2") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "February" } [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "3") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "March" } [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "4") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "April" } [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "5") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "May" } [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "6") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "June" } [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "7") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "July" } [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "8") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "August" } [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "9") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "September" } <span> [/font]</span></span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "10") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "October" } <span> [/font]</span></span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "11") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "November" } <span> [/font]</span></span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]if ( BirtDateTime.month(row["MONTH"]) == "12") [/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]{ bob = "December" }[/font]</span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"] <span> [/font]</span></span></div><div><span style="font-size:10px;">[font="'courier new', courier, monospace;"]bob<span> [/font]+ "-" + BirtDateTime.year(row["MONTH"]).toString()</span></span></div><div> </div><div>Strange thing as before is that the quote must be removed from the input variable string.</div><div>January-2013</div><div>rather than</div><div>'January-2013'</div><div>as a default or selected value.</div>

<p>[font="arial, helvetica, sans-serif;"][color=rgb(40,40,40);]MM-yyyy format is : month numeric and[/color] year numeric (like 01-2013, 02-2013.....)[/font]</p><p>[font="arial, helvetica, sans-serif;"][color=rgb(40,40,40);]mon-yyyy format is : month string lower case and[/color] year numeric (like jan-2013, feb-2013.....[/font])</p><p>MON-yyyy [font="arial, helvetica, sans-serif;"][color=rgb(40,40,40);]format is : month string upper case and[/color] year numeric (like JUN-2013, OCT-2013.....[/font])</p><p> </p><p>I'm not sure but maybe, you can use full month name with "mon" or "MON" (like mon-yyyy or MON-yyyy), but I think you have to use month or MONTH (month-yyyy, MONTH-yyyy).</p><p>Also not sure if Mon-yyyy is for Jan-2013, Feb-2013 (first letter in uppercase)</p>