HAVING clausule om te filteren op uitkomst groepfunctie

Vragen
,
1

Hoe is het mogelijk een HAVING statement te gebruiken in Invantive Cloud en Invantive Query Tool?
Zie onderstaande voorbeeld query:

select Country, count(code) as Aantal
from   systemdivisions
group 
by      Country
having count(code) <> 0

Levert fout itgensql056:

itgensql056: Statement #1 'select Countr...unt(code) <> 0' beginnend op regel 1 veroorzaakte een fout.

Syntax error between the two '***' on line 3, column 0:

select Country, count(...ons<CRLF>group by Country<CRLF>***having*** count(code) <> 0

Error: mismatched input 'having' expecting {<EOF>, ';'}.

Bericht ID: 6d815aa7-4396-4eb9-8d41-ab3ee3f6fde9

Opgetreden (UTC): 28-12-2023 11:41:28

select Country, count(...ons<CRLF>group by Country<CRLF>***having*** count(code) <> 0

select Country, count(code) as Aantal from systemdivisions
group by Country
having count(code) <> 0

Invantive.Basics.InvantiveSqlException
ValidationException
   bij Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId, String url)
   bij Invantive.Basics.InvantiveSqlException..ctor(GlobalState owner, ExecutionOptions executionOptions, String validationCode, String errorMessage, String kindRequest, String stackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId)
   bij Invantive.Data.InvantiveParserErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException triggeringException)
   bij Antlr4.Runtime.ProxyErrorListener`1.SyntaxError(IRecognizer recognizer, Symbol offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e)
   bij Antlr4.Runtime.Parser.NotifyErrorListeners(IToken offendingToken, String msg, RecognitionException e)
   bij Invantive.Sql.InvantiveSQL.sqlBatch()
   bij Invantive.Sql.V1.SqlEngine.GetParseTrees(GlobalState owner, ExecutionOptions executionOptions, String sqlStatement)
   bij Invantive.Sql.V1.SqlEngine.ParseStatement(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, Boolean allowSelect)
   bij Invantive.Sql.V1.SqlEngine.Execute(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, BO queryPlanState)
   bij Invantive.Sql.V1.SqlEngine.Parse(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, BO queryPlanState)
   bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   bij Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   bij Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   bij Invantive.Producer.Windows.Forms.QueryToolResults.TPQ(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean )
   bij Invantive.Producer.Windows.Forms.QueryToolResults.FPQ(GlobalState , ExecutionOptions , String , String , Boolean )
   bij Invantive.Producer.Windows.Forms.QueryToolResults.ExecuteStatements(GlobalState owner, ExecutionOptions executionOptions)
   bij Invantive.Producer.Windows.Forms.QueryToolDocumentViewModel.WV.KV.MoveNext()
   bij System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
   bij Invantive.Producer.Windows.Forms.QueryToolDocumentViewModel.WV.B()
   bij System.Threading.Tasks.Task`1.InnerInvoke()
   bij System.Threading.Tasks.Task.Execute()
   bij System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   bij System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   bij System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
   bij System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution)
   bij System.Threading.ThreadPoolWorkQueue.Dispatch()
   bij Invantive.Data.InvantiveParserErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException triggeringException)
   bij Antlr4.Runtime.ProxyErrorListener`1.SyntaxError(IRecognizer recognizer, Symbol offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e)
   bij Antlr4.Runtime.Parser.NotifyErrorListeners(IToken offendingToken, String msg, RecognitionException e)
   bij Invantive.Sql.InvantiveSQL.sqlBatch()
   bij Invantive.Sql.V1.SqlEngine.GetParseTrees(GlobalState owner, ExecutionOptions executionOptions, String sqlStatement)
   bij Invantive.Sql.V1.SqlEngine.ParseStatement(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, Boolean allowSelect)
   bij Invantive.Sql.V1.SqlEngine.Execute(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, BO queryPlanState)
   bij Invantive.Sql.V1.SqlEngine.Parse(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, BO queryPlanState)
   bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---
   bij System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   bij Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   bij Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- Einde van stacktracering vanaf vorige locatie waar uitzondering is opgetreden ---
   bij System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   bij Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   bij Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   bij Invantive.Producer.Windows.Forms.QueryToolResults.TPQ(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean ) in c:\smoke\ws-22.0\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolTraceViewModel.cs:regel 17
   bij Invantive.Producer.Windows.Forms.QueryToolResults.FPQ(GlobalState , ExecutionOptions , String , String , Boolean ) in c:\smoke\ws-22.0\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolResultsViewModel.cs:regel 22
2

Invantive SQL biedt momenteel in geen van versies ondersteuning voor de HAVING-clause waarmee gefilterd kan worden op de uitkomst van groepsfuncties. Er zijn geen plannen om die op afzienbare termijn toe te voegen.

Having is eenvoudig te implementeren met een inline-view zoals:

select *
from ( select kolommen, groepfunctie g from t group by kolommen )
where filter-op-g

3

Dit topic is 7 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.