Sql in Parameter

0

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.
abhinashdora's picture
Joined: Apr 30 2012 - 12:28pm
Last seen: 6 years 6 months ago

2 Answers:

0

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

hozawa's picture
64906
Joined: Apr 24 2010 - 4:31pm
Last seen: 1 year 3 months ago

Thanks hozawa, i read the documentation and tried this code
<queryString>
<![CDATA[$P!{Query}]]>
</queryString>

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

abhinashdora - 6 years 6 months ago
0

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.

 

 

hozawa's picture
64906
Joined: Apr 24 2010 - 4:31pm
Last seen: 1 year 3 months ago
Feedback