Scribd
Upload
39 views2 pages

Rural SHG Tree Plantation Data Query

The document contains SQL queries for retrieving data related to tree plantation information for rural Self-Help Groups (SHGs). The first query selects distinct plant and user information based on specific conditions, while the second query retrieves detailed plantation data along with user and plant details, allowing for filtering by district, urban local body, and SHG codes. Both queries utilize joins to combine data from multiple tables within the kalpvriksh_sch schema.

Uploaded by

rajraushan2257
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
39 views2 pages

Rural SHG Tree Plantation Data Query

The document contains SQL queries for retrieving data related to tree plantation information for rural Self-Help Groups (SHGs). The first query selects distinct plant and user information based on specific conditions, while the second query retrieves detailed plantation data along with user and plant details, allowing for filtering by district, urban local body, and SHG codes. Both queries utilize joins to combine data from multiple tables within the kalpvriksh_sch schema.

Uploaded by

rajraushan2257
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

// FOR RURAL SHG//

@Query(value = "select * from ( \r\n"


+ " select distinct pl.plant_name, tpi.plant_id, [Link] as reg_id,
tpi.mobile_number as citizen_mob_no, lgd_gp_nm, \r\n"
+ " [Link] as citizen_name, [Link] \r\n"
+ " from kalpvriksh_sch.tree_plantation_info_shg as tpi \r\n"
+ " inner join kalpvriksh_sch.plant_list as pl on [Link] =
CAST(tpi.plant_id AS int) \r\n"
+ " inner join kalpvriksh_sch.shg_user_info as sui on sui.mobile_no
= tpi.mobile_number \r\n"
+ " where tpi.district_cd = ?1 and tpi.lgd_ulb_cd is null \r\n"
+ " and (tpi.assigne_status is null or tpi.assigne_status = 0)
\r\n"
+ " and tpi.grow_plant_image is not null and lgd_gp_cd is not
null \r\n"
+ ") a ORDER BY random()", nativeQuery = true

@Query(value = "SELECT " +


"[Link], " +
"tpi.file_name, " +
"tpi.mobile_number, " +
"tpi.plant_id, " +
"tpi.district_cd, " +
"tpi.verifiy_status, " +
"tpi.number_of_plant, " +
"tpi.grow_plant_image, " +
"tpi.verified_plant_image, " +
"tpi.ub_nm_e, " +
"tpi.assigne_status, " +
"tpi.other_plant_name, " +
"tpi.third_plant_image, " +
"tpi.eight_plant_image, " +
"tpi.fifth_plant_image, " +
"tpi.fourth_plant_image, " +
"tpi.seventh_plant_image, " +
"tpi.sixth_plant_image, " +
"tpi.health_condition_2, " +
"tpi.health_condition_3, " +
"tpi.health_condition_4, " +
"tpi.health_condition_5, " +
"tpi.health_condition_6, " +
"tpi.health_condition_7, " +
"tpi.health_condition_8, " +
"tpi.no_plants_alive_2, " +
"tpi.no_plants_alive_3, " +
"tpi.no_plants_alive_4, " +
"tpi.no_plants_alive_5, " +
"tpi.no_plants_alive_6, " +
"tpi.no_plants_alive_7, " +
"tpi.no_plants_alive_8, " +
"tpi.reason_missing_plants_2, " +
"tpi.reason_missing_plants_3, " +
"tpi.reason_missing_plants_4, " +
"tpi.reason_missing_plants_5, " +
"tpi.reason_missing_plants_6, " +
"tpi.reason_missing_plants_7, " +
"tpi.reason_missing_plants_8, " +
"sui.shg_groupcode, " +
"[Link] AS user_name, " +
"sm.shg_name, " +
"pl.plant_name, " +
"[Link] " +

"FROM kalpvriksh_sch.tree_plantation_info_shg tpi " +


"LEFT JOIN kalpvriksh_sch.shg_user_info sui " +
" ON tpi.mobile_number = sui.mobile_no " +
"LEFT JOIN kalpvriksh_sch.shg_master sm " +
" ON sui.shg_groupcode = sm.shg_groupcode " +
"LEFT JOIN kalpvriksh_sch.plant_list pl " +
" ON CAST(tpi.plant_id AS INTEGER) = [Link] " +
"WHERE (CASE WHEN :districtCd = '0' THEN TRUE ELSE tpi.district_cd
= :districtCd END) " +
"AND (CASE WHEN :ulbCd = '0' THEN TRUE ELSE tpi.lgd_ulb_cd
= :ulbCd END)" +
"AND (CASE WHEN :shgCd = '0' THEN TRUE ELSE sui.shg_groupcode
= :shgCd END)",
nativeQuery = true)

You might also like