I am trying to intial load data based on order by creationtime column. My sym_trigger_router (for currencies table )has below initial_load_select :-
initial_load_select = "1 = 1 order by t.CreationTime"
which create query as below :-
select count(*) from "board"."dbo"."currencies" t where 1=1 order by t.CreationTime desc;
It works with MySQL but not with MSSQL because MSSQL does not support order by with count(*).
Getting below error :-
[head-85711c60-84d5-4527-b31f-a861aa8caa9a] - JdbcSqlTemplate - SQL caused exception: [select count(*) from "board"."dbo"."currencies" t where 1=1 order by t.CreationTime desc ] java.sql.SQLException: Column "board.dbo.currencies.creationtime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
any Help ?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am trying to intial load data based on order by creationtime column. My sym_trigger_router (for currencies table )has below initial_load_select :-
initial_load_select = "1 = 1 order by t.CreationTime"
which create query as below :-
select count(*) from "board"."dbo"."currencies" t where 1=1 order by t.CreationTime desc;
It works with MySQL but not with MSSQL because MSSQL does not support order by with count(*).
Getting below error :-
[head-85711c60-84d5-4527-b31f-a861aa8caa9a] - JdbcSqlTemplate - SQL caused exception: [select count(*) from "board"."dbo"."currencies" t where 1=1 order by t.CreationTime desc ] java.sql.SQLException: Column "board.dbo.currencies.creationtime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
any Help ?
Yeah, I think that select needs to just be the where clause. What is the advantage or ordering in your initial load select, if you are selecting all?