Scribd
Upload
7 views1 page

Tree Plantation Data Queries

The document contains two SQL query definitions for retrieving plantation information from a database. The first query fetches details about tree plantations associated with self-help groups (SHGs) based on district and tehsil codes, while the second query retrieves distinct plantation data linked to citizens, filtering by district code and ensuring certain conditions are met. Both queries utilize inner joins to combine data from multiple tables and include conditions for filtering results.

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
7 views1 page

Tree Plantation Data Queries

The document contains two SQL query definitions for retrieving plantation information from a database. The first query fetches details about tree plantations associated with self-help groups (SHGs) based on district and tehsil codes, while the second query retrieves distinct plantation data linked to citizens, filtering by district code and ensuring certain conditions are met. Both queries utilize inner joins to combine data from multiple tables and include conditions for filtering results.

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

@Query(value = "SELECT pl.plant_name, tpishg.plant_id, tpishg.

id AS
reg_id, tpishg.mobile_number AS shg_mob_no, "
+ "[Link] AS shg_name, [Link],tpishg.lgd_gp_nm "
+ "FROM kalpvriksh_sch.tree_plantation_info_shg AS tpishg "
+ "INNER JOIN kalpvriksh_sch.plant_list AS pl ON [Link] =
CAST(tpishg.plant_id AS int) "
+ "INNER JOIN kalpvriksh_sch.shg_user_info AS shg ON shg.mobile_no =
tpishg.mobile_number "
+ "WHERE tpishg.dist_cd = ?1 AND tpishg.teh_cd = ?2 "
+ "AND (tpishg.assigne_status IS NULL OR tpishg.assigne_status = 0) "
+ "AND tpishg.grow_plant_image IS NOT NULL", nativeQuery = true)
List<Object[]> findPlantationListByUrbunData_ru1SHG(String districtCd,
String tehCd);

@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 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.citizen_user_info as cui on cui.mobile_no=tpi.mobile_number \r\n"
+ " where tpi.dist_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 ) a ORDER BY random()", nativeQuery = true)
List<Object[]> findPlantationListByDistrictCodeTehsil(@Param("districtCd")
String districtCd);

You might also like