0

Iam trying to use Jpa repository to get data from my sql server using a native query

This is a simple call from my service

repo.testData("h3","h3");

This is my repository query. Select statement can read the binding variable :level but group by is unable to read it.

@Query(value="SELECT sum(pos.total_weekly_sales) as curr_yr_sales, sum(pos.total_weekly_qty) as curr_yr_qty, pos.vendor_nbr,pos.gmm_id,\n" +
            "case \n" +
            "when :level = 'h3' then pos.category_id\n" +
            "else 0\n" +
            "end category_id\n" +
            "from dbo.agg_sams_data pos\n" +
            "join dbo.calendar_dim cal on pos.wm_year_wk_nbr = cal.wm_year_wk_nbr\n" +
            "WHERE \n" +
            "cal.calendar_date BETWEEN '2019-09-11' and '2020-09-09'\n" +
            "and pos.vendor_nbr = 68494\n" +
            "and pos.gmm_id = 45\n" +
            "and (:h3Flag = 'N' or pos.category_id = 52)\n" +
            "GROUP by pos.vendor_nbr,pos.gmm_id,\n" +
            "case \n" +
            "when :level='h3' then pos.category_id\n" +
            "else 0\n" +
            "end",nativeQuery = true)
    List<List<Double>> testData(String level,String h3Flag);

And i get the following error com.microsoft.sqlserver.jdbc.SQLServerException: Column 'dbo.agg_sams_data.category_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If i pass the hardcoded value in the group by clause it works fine(as below)

"GROUP by pos.vendor_nbr,pos.gmm_id,\n" +
            "case \n" +
            "when 'h3'='h3' then pos.category_id\n"
freak_coder
  • 1
  • My guess, it's something to do with your query [Try this solution and see if it helps](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – Fernando Campanate Sep 12 '20 at 03:42

1 Answers1

0

You should try putting pos.category_id into the group by instead of the whole case when statement. The problem is, that SQL Server can't be sure that the parameter in both cases will have the same value so the expressions could be different.

Christian Beikov
  • 15,141
  • 2
  • 32
  • 58
  • Hey the reason im writing case/when statement is to use this query dynamically even if they provide ``pos.category_id`` or not... so trying to make 2 individual queries into one and so on – freak_coder Sep 13 '20 at 23:07