0

i'm trying to update "PA49_Debut"column with : - the"PA49_Debut" value from the second table if the product exists there - else "PA49_Debut" from the same table if the product exists in the previous rows - else "PA49_Actuel" from the same table take a look to the code in the repository

 public interface FSrepository extends JpaRepository<FSmodel, String> {
  @Transactional 
  @Modifying
  @Query("Update FSmodel FS Set FS.PA49_Debut = CASE WHEN EXISTES (SELECT 1 FROM ISmodel SI where FS.Partnumber=SI.Partnumber)" + 
        " THEN SI.PA49 " + 
        "WHEN EXISTS (SELECT 1 FROM FSmodel FS2 WHERE FS.Partnumber=FS2.Partnumber AND FS.id>FS2.id )" + 
        " THEN FS2.PA49_Debut "+
          " ELSE (FS.PA49_Actuel) "
        + "END" )
        void setPA49Debut(); ```

these are my ISmodel and FSmodel entities

@Entity
public class FSmodel {
      @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    public Long id;
    public String Partnumber;
    public String Materialgroup;
    public String Warehouse;
    public Float Value;
    public Float Stock;
    public Float PA49_Actuel;
    public Float PA49_Debut;
    public String article_fg;
    public Float Priceperunit;}


@Entity
public class ISmodel {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    public Long id;
    public String Partnumber;
    public String Materialgroup;
    public String Warehouse;
    public Float Stock;
    public Float Value;
    public Float  PA49;
    public Float STPA49; }

and that is the error 

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'controller': Unsatisfied dependency expressed through field 'FSrepos'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'FSrepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract void com.example.demo2.repository.FSrepository.setPA49Debut()!
Caused by: java.lang.NullPointerException: null

Any help would be greatly appreciated. Thanks.

maha
  • 21
  • 4
  • 1
    try @Query(vaue="your query goes here",nativeQuery = true) – stacker Mar 07 '20 at 12:59
  • 1
    CASE WHEN EXISTES spelled wrongly. Have to be written CASE WHEN EXISTS – Oleksii Valuiskyi Mar 07 '20 at 13:15
  • thanks @stacker and @alex it solves the NullPointerException problem but i have this error now `could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement ` `java.sql.SQLSyntaxErrorException: Unknown column 'SI.PA49' in 'field list'` any ideas? – maha Mar 07 '20 at 13:58

1 Answers1

0

the solution to NullPointerException: null is to

try @Query(vaue="your query goes here",nativeQuery = true)

as @stacker said but i found out that there is a lot of mistakes in my code,i'll try to explain them in hope it helps someone else. The first

CASE WHEN EXISTES spelled wrongly. Have to be written CASE WHEN EXISTS @alex

The second is using SI out of the select statement i resolved this problem by adding a new select statement. The third mistake is to update my table by subquery the same table in those lines "WHEN EXISTS (SELECT 1 FROM FSmodel FS2 WHERE FS.Partnumber=FS2.Partnumber AND FS.id>FS2.id )" + " THEN FS2.PA49_Debut " and i found the solution as well as the explanation here Eventually this is my final code and it works great @Transactional @Modifying @Query(value="Update FSmodel FS Set FS.PA49_Debut = CASE" + " WHEN exists(SELECT SI.Partnumber FROM ISmodel SI where FS.Partnumber=SI.Partnumber)" + " THEN (select distinct S.PA49 from ISmodel S where FS.Partnumber=S.Partnumber)" + "WHEN exists(SELECT 1 FroM (SELECT * FROM FSmodel) AS FSd WHERE FS.Partnumber=FSd.Partnumber AND FS.id>FSd.id )" + " THEN (select distinct F.PA49_Debut from (SELECT * FROM FSmodel) AS F where FS.Partnumber=F.Partnumber AND FS.id>F.id)+1" + " ELSE (FS.PA49_Actuel )" + "END",nativeQuery = true) void setPA49Debut();

maha
  • 21
  • 4