How to add complex inner query to @Query annotaion

Question

normal sql query which work correctly in db in sql developer passing values for bID and period.

SELECT * FROM A WHERE abcID IN (SELECT abcID FROM B WHERE bID=1) AND period=3

in project at Repository class I passed as this

@Query("select a from A where a.abcID IN:(select b.abcId from B where bID=:RevID) and period=:period")

error comes as

Space is not allowed after parameter prefix ':' [select a from A where a.abcID IN:(select b.abcId from B where bID=:RevID) and period=:period]

I want to know how should I insert above query correctly in @Query annotation

Answer 1

First of all I would tell you below points.

1. you can't use select query as select a from A where a.abcID. Here a is a column so can't define something like a.abcID. It need to be select column from tableA a where a.abcID
2. Same for query use in IN clause. It needs to be like select b.abcId from tableB b where b.bID=:RevID
3. What you use as :RevID, :period need to be passed as @Param("RevID"), @Param("period") to the query method.

This is the query template.

@Query("select a.nameOfcolumnYouWantToRetrieve from tableA a where a.someColumn in(select b.someColumn from tableB b where b.columnValueOfTableBYouwantToMatch=:RevID) and a.period=:period")

Using this points, try below query.

 @Query("select a.id from A a where a.abcID in(select b.abcId from B b where b.bID =:RevID) and a.period=:period")