Major Description
Hi All,
I am trying to run below queries using SQLParser object, the first query is executing as expected but when am adding sum(ppd.value) function I am getting exception, but same query I am able to run in SQL SERVER.
Working query:
insert into planning_point_data_cache (ts_business_name_id,planning_point_seq,detail_id,detail_id,period_id,cache_value)
select ppd.ts_business_name_id,denorm.input_ppt_cache_seq,denorm.detail_id,denorm.period_id,cache_value
from rule_output_input_map_normalize denorm join planning_point_data ppd on denorm.input_ppt_seq=ppd.planning_point_seq
where denorm.param_order='raghu' and denorm.input_ppt_cache_seq 0 group by ppd.ts_business_name_id.
Problem facing query:
insert into planning_point_data_cache(ts_business_name_id,planning_point_seq,detail_id,detail_id,period_id,cache_value)
selectppd.ts_business_name_id,denorm.input_ppt_cache_seq,denorm.detail_id,denorm.period_id,sum(ppd.value)
from rule_output_input_map_normalize denorm join planning_point_data ppd ondenorm.input_ppt_seq=ppd.planning_point_seq
where denorm.param_order='raghu' and denorm.input_ppt_cache_seq 0 group byppd.ts_business_name_id,ppd.value
Below is the stack trace:
org.apache.calcite.sql.parser.SqlParseException: Encountered ". value" at line 1, column 221.
Was expecting one of:
")" ...
"ORDER" ...
"LIMIT" ...
"OFFSET" ...
"FETCH" ...
"," ...
"UNION" ...
"INTERSECT" ...
"EXCEPT" ...
"." ...
"NOT" ...
"IN" ...
"BETWEEN" ...
"LIKE" ...
"SIMILAR" ...
"=" ...
">" ...
"<" ...
"<=" ...
">=" ...
"<>" ...
"+" ...
"-" ...
"*" ...
"/" ...
"||" ...
"AND" ...
"OR" ...
"IS" ...
"MEMBER" ...
"SUBMULTISET" ...
"MULTISET" ...
"[" ...
"." <IDENTIFIER> ...
"." <QUOTED_IDENTIFIER> ...
"." <BACK_QUOTED_IDENTIFIER> ...
"." <BRACKET_QUOTED_IDENTIFIER> ...
"." <UNICODE_QUOTED_IDENTIFIER> ...
"." "*" ...
"(" ...
at org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:388)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:118)
at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:130)
at TestSqlParser.main(TestSqlParser.java:30)
Caused by: org.apache.calcite.sql.parser.impl.ParseException: Encountered ". value" at line 1, column 221.
Was expecting one of:
")" ...
"ORDER" ...
"LIMIT" ...
"OFFSET" ...
"FETCH" ...
"," ...
"UNION" ...
"INTERSECT" ...
"EXCEPT" ...
"." ...
"NOT" ...
"IN" ...
"BETWEEN" ...
"LIKE" ...
"SIMILAR" ...
"=" ...
">" ...
"<" ...
"<=" ...
">=" ...
"<>" ...
"+" ...
"-" ...
"*" ...
"/" ...
"||" ...
"AND" ...
"OR" ...
"IS" ...
"MEMBER" ...
"SUBMULTISET" ...
"MULTISET" ...
"[" ...
"." <IDENTIFIER> ...
"." <QUOTED_IDENTIFIER> ...
"." <BACK_QUOTED_IDENTIFIER> ...
"." <BRACKET_QUOTED_IDENTIFIER> ...
"." <UNICODE_QUOTED_IDENTIFIER> ...
"." "*" ...
"(" ...
at org.apache.calcite.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:16646)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:16475)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.FunctionParameterList(SqlParserImpl.java:725)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.NamedFunctionCall(SqlParserImpl.java:4146)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.AtomicRowExpression(SqlParserImpl.java:2718)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression3(SqlParserImpl.java:2562)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2(SqlParserImpl.java:2394)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression(SqlParserImpl.java:2366)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectExpression(SqlParserImpl.java:1326)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectItem(SqlParserImpl.java:1298)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectList(SqlParserImpl.java:1284)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlSelect(SqlParserImpl.java:819)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQuery(SqlParserImpl.java:578)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:2351)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:2270)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:496)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlInsert(SqlParserImpl.java:1009)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmt(SqlParserImpl.java:754)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:776)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:177)
at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:123)
... 1 more