CODEWITHSUNDEEP
javajpql
pheromix
pheromix

Reputation: 19327

How to make a case statement based on the method parameter?

I want to make a case statement like in this query :

@Query(nativeQuery = true, value = "select CASE " + 
            " WHEN TYP_0=1 THEN ACC_0" + 
            " WHEN TYP_0=2 THEN ACC_3" + 
            " WHEN TYP_0=3 THEN ACC_2" + 
            " END  FROM  tomx3v6.TOMCTB.GACCCODE  WHERE ACCCOD_0 IN (SELECT [ACCCOD_0] FROM [tomx3v6].[TOMCTB].[ITMMASTER] WHERE YITMOXA_0=?1) AND COA_0='PCG'")
    public String getPCGvalue(String linpurtyp, String code);

But instead of basing the case on the database column TYP_0 I want to make the case statement to be based on the method parameter linpurtyp. How to do that ?

Upvotes: 0

Views: 396

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

Try this:

@Query(nativeQuery = true, value = "select CASE " + 
            " WHEN ?1=1 THEN ACC_0" + 
            " WHEN ?1=2 THEN ACC_3" + 
            " WHEN ?1=3 THEN ACC_2" + 
            " END  FROM  tomx3v6.TOMCTB.GACCCODE  WHERE ACCCOD_0 IN (SELECT [ACCCOD_0] FROM [tomx3v6].[TOMCTB].[ITMMASTER] WHERE YITMOXA_0=?1) AND COA_0='PCG'")
    public String getPCGvalue(String linpurtyp, String code);

Alternatively, use named parameters

@Query(nativeQuery = true, value = "select CASE " + 
            " WHEN :linpurtyp=1 THEN ACC_0" + 
            " WHEN :linpurtyp=2 THEN ACC_3" + 
            " WHEN :linpurtyp=3 THEN ACC_2" + 
            " END  FROM  tomx3v6.TOMCTB.GACCCODE  WHERE ACCCOD_0 IN (SELECT [ACCCOD_0] FROM [tomx3v6].[TOMCTB].[ITMMASTER] WHERE YITMOXA_0=:linpurtyp) AND COA_0='PCG'")
    public String getPCGvalue(@Param("linpurtyp") String linpurtyp, @Param("code") String code);

Upvotes: 1

Related Questions