Sql in Parameter

[abhinashdora]

Hi Guys,

I need to pass a sql query in a parameter in ireport, i have tried some but failed to do that. 

Down here is my parameter and query

<parameter name="sql" class="java.lang.String" isForPrompting="false"/>

<queryString>

<![CDATA[sELECT

     date_format(a.application_date,'%d-%m-%Y'),

     concat(ifnull(a.present_address,""),', '),

     concat(ifnull(a.present_village,""),', '),

     concat(ifnull(a.present_post,""),', '),

     concat(ifnull(a.present_ps,""),', '),

     concat(ifnull(a.present_dist,""),', '),

     concat(ifnull(a.present_pin,""),', '),

     concat(ifnull(a.present_landline_no,"none"),', '),

     concat(ifnull(a.present_mobile,"none"),''),

     concat(ifnull(a.permanent_address,""),', '),

     concat(ifnull(a.permanent_village,""),', '),

     concat(ifnull(a.permanent_post,""),', '),

     concat(ifnull(a.permanent_ps,""),', '),

     concat(ifnull(a.permanent_dist,""),', '),

     concat(ifnull(a.permanent_pin,""),', '),

     concat(ifnull(a.permanent_landline_no,"none"),', '),

     concat(ifnull(a.permanent_mobile,"none"),''),

     b.`school_name`,

     b.`school_address_1`,

     b.`school_address_2`,

     b.`school_address_3`,

     a.`admission_no`,

     a.`academic_year`,

     a.`form_no`,

     a.`admission_class`,

     a.`student_name`,

     a.`dob_day`,

     a.`dob_day`,

     a.`dob_month`,

     a.`dob_year`,

     a.`dob_words`,

     a.`age`,

     a.`nationality`,

     a.`religion`,

     a.`mother_tongue`,

     a.`gender`,

     a.`category_abbr`,

     a.`prev_school`,

     a.`prev_result`,

     a.`prev_rank`,

     a.`prev_mark_pc`,

     a.`blood_group`,

     a.`mother_name`,

     a.`father_name`,

     a.`guardian_name`

FROM `application_form` a,

     `config` b, class c

WHERE b.config_id = 1 $P!{sql}

GROUP BY

     admission_class,

     form_no

ORDER BY

     admission_class ASC]]>

</queryString>

 

inside parameter i want to pass

and c.class_id=6 and a.admission_class=c.class_desc

 

Can somebody tell me how to do that in a proper way.

[hozawa]

Check "Jasperesoft Studio User Guide" p 84, 8.3.1 "Using Parameters in a SQL Query"

http://community.jaspersoft.com/documentation/jaspersoft-studio-user-guide

[abhinashdora]

Thanks hozawa, i read the documentation and tried this code

$P!{Query}

 

the default value of the parameter is

SELECT date_format(a.application_date,'%d-%m-%Y'),concat(ifnull(a.present_address,""),', ')," +

"concat(ifnull(a.present_village,""),', '),concat(ifnull(a.present_post,""),', ')," +

"concat(ifnull(a.present_ps,""),', '),concat(ifnull(a.present_dist,""),', ')," +

"concat(ifnull(a.present_pin,""),', '),concat(ifnull(a.present_landline_no,"none"),', ')," +

"concat(ifnull(a.present_mobile,"none"),''),concat(ifnull(a.permanent_address,""),', ')," +

"concat(ifnull(a.permanent_village,""),', '),concat(ifnull(a.permanent_post,""),', ')," +

"concat(ifnull(a.permanent_ps,""),', '),concat(ifnull(a.permanent_dist,""),', ')," +

"concat(ifnull(a.permanent_pin,""),', '),concat(ifnull(a.permanent_landline_no,"none"),', ')," +

"concat(ifnull(a.permanent_mobile,"none"),''),b.`school_name`,b.`school_address_1`,b.`school_address_2`," +

"b.`school_address_3`,a.`admission_no`,a.`academic_year`,a.`form_no`,a.`admission_class`,a.`student_name`," +

"a.`dob_day`,a.`dob_day`,a.`dob_month`,a.`dob_year`,a.`dob_words`,a.`age`,a.`nationality`,a.`religion`," +

"a.`mother_tongue`,a.`gender`,a.`category_abbr`,a.`prev_school`,a.`prev_result`,a.`prev_rank`," +

"a.`prev_mark_pc`,a.`blood_group`,a.`mother_name`,a.`father_name`,a.`guardian_name` " +

"FROM `application_form` a,`config` b, class c " +

"WHERE b.config_id = 1 " +

"GROUP BY admission_class,form_no " +

"ORDER BY admission_class ASC

and I want to send

SELECT date_format(a.application_date,'%d-%m-%Y'),concat(ifnull(a.present_address,""),', ')," +

"concat(ifnull(a.present_village,""),', '),concat(ifnull(a.present_post,""),', ')," +

"concat(ifnull(a.present_ps,""),', '),concat(ifnull(a.present_dist,""),', ')," +

"concat(ifnull(a.present_pin,""),', '),concat(ifnull(a.present_landline_no,"none"),', ')," +

"concat(ifnull(a.present_mobile,"none"),''),concat(ifnull(a.permanent_address,""),', ')," +

"concat(ifnull(a.permanent_village,""),', '),concat(ifnull(a.permanent_post,""),', ')," +

"concat(ifnull(a.permanent_ps,""),', '),concat(ifnull(a.permanent_dist,""),', ')," +

"concat(ifnull(a.permanent_pin,""),', '),concat(ifnull(a.permanent_landline_no,"none"),', ')," +

"concat(ifnull(a.permanent_mobile,"none"),''),b.`school_name`,b.`school_address_1`,b.`school_address_2`," +

"b.`school_address_3`,a.`admission_no`,a.`academic_year`,a.`form_no`,a.`admission_class`,a.`student_name`," +

"a.`dob_day`,a.`dob_day`,a.`dob_month`,a.`dob_year`,a.`dob_words`,a.`age`,a.`nationality`,a.`religion`," +

"a.`mother_tongue`,a.`gender`,a.`category_abbr`,a.`prev_school`,a.`prev_result`,a.`prev_rank`," +

"a.`prev_mark_pc`,a.`blood_group`,a.`mother_name`,a.`father_name`,a.`guardian_name` " +

"FROM `application_form` a,`config` b, class c " +

"WHERE b.config_id = 1 and c.class_id=6 and a.admission_class=c.class_desc " +

"GROUP BY admission_class,form_no " +

"ORDER BY admission_class ASC

[hozawa]

Your sql statement is too long to try to find where the problem is.

I usually use a database tool such as MySQL Workbench to test SQL statements before pasting them in Query dialog. (I have to hardcode the values where the parameters will be used).

If the sql statement doesn't worki with the database tool, it's not going to work in jasperreports.