CSV import aanmaken als table geeft: Syntax error between the two '***' on line 2, column 2:

Vragen
1

Bij het gebruik van van de onderstaande code. Krijg ik de volgende foutmelding.

as' starting on line 1 caused an error.

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

create or replace table mc_ExportKosten@sql<CRLF>as***<EOF>***

Error: no viable alternative at input 'create or replace table mc_ExportKosten@sql\r\nas'.

Message ID: b85f2e24-5366-45a6-8243-29e67b121e95

Occurred (UTC): 2-11-2023 09:00:44

create or replace table mc_ExportKosten@sql<CRLF>as***<EOF>***

create or replace table mc_ExportKosten@sql
as

Invantive.Basics.InvantiveSqlException
ValidationException
   at 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)
   at 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)
   at Invantive.Data.InvantiveParserErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException triggeringException)
   at Antlr4.Runtime.ProxyErrorListener`1.SyntaxError(IRecognizer recognizer, Symbol offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e)
   at Antlr4.Runtime.Parser.NotifyErrorListeners(IToken offendingToken, String msg, RecognitionException e)
   at Invantive.Sql.InvantiveSQL.sqlOrPSqlStatement()
   at Invantive.Sql.InvantiveSQL.sqlBatch()
   at Invantive.Sql.V1.SqlEngine.GetParseTrees(GlobalState owner, ExecutionOptions executionOptions, String sqlStatement)
   at Invantive.Sql.V1.SqlEngine.ParseStatement(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, Boolean allowSelect)
   at Invantive.Sql.V1.SqlEngine.Execute(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, YT queryPlanState)
   at Invantive.Sql.V1.SqlEngine.Parse(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, YT queryPlanState)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   at Invantive.Producer.Windows.Forms.QueryToolResults.PSO(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean )
   at Invantive.Producer.Windows.Forms.QueryToolResults.FSO(GlobalState , ExecutionOptions , String , String , Boolean )
   at Invantive.Producer.Windows.Forms.QueryToolResults.ExecuteStatements(GlobalState owner, ExecutionOptions executionOptions)
   at Invantive.Producer.Windows.Forms.QueryToolDocumentViewModel.BV.SV.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
   at Invantive.Producer.Windows.Forms.QueryToolDocumentViewModel.BV.R()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
   at System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution)
   at System.Threading.ThreadPoolWorkQueue.Dispatch()
   at Invantive.Data.InvantiveParserErrorListener.SyntaxError(IRecognizer recognizer, IToken offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException triggeringException)
   at Antlr4.Runtime.ProxyErrorListener`1.SyntaxError(IRecognizer recognizer, Symbol offendingSymbol, Int32 line, Int32 charPositionInLine, String msg, RecognitionException e)
   at Antlr4.Runtime.Parser.NotifyErrorListeners(IToken offendingToken, String msg, RecognitionException e)
   at Invantive.Sql.InvantiveSQL.sqlOrPSqlStatement()
   at Invantive.Sql.InvantiveSQL.sqlBatch()
   at Invantive.Sql.V1.SqlEngine.GetParseTrees(GlobalState owner, ExecutionOptions executionOptions, String sqlStatement)
   at Invantive.Sql.V1.SqlEngine.ParseStatement(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, Boolean allowSelect)
   at Invantive.Sql.V1.SqlEngine.Execute(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, YT queryPlanState)
   at Invantive.Sql.V1.SqlEngine.Parse(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect, ExecutionEnvironment executionEnvironment, YT queryPlanState)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   at Invantive.Producer.Windows.Forms.QueryToolResults.PSO(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:line 17
   at Invantive.Producer.Windows.Forms.QueryToolResults.FSO(GlobalState , ExecutionOptions , String , String , Boolean ) in c:\smoke\ws-22.0\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolResultsViewModel.cs:line 22

Wat doe ik verkeerd?

create or replace table mc_ExportKosten@sql
as

select t.*
from   csvtable
       ( passing file 'c:\CSVData\ExportKostenBeknopt.csv'
         row     delimiter chr(10)
         column  delimiter ',' 
         skip    lines 1
         columns codeBedrijf               varchar(50) not null position next
         ,       codeProject               varchar(30) not null position next
         ,       codeKostensoort           varchar(30)     null position next
         ,       Period                    varchar(30) not null position next
         ,       QuantityWerkbegroting     decimal     not null position next
         ,       AmountStdWerkbegroting    decimal     not null position next
         ,       QuantityToegestanekosten  decimal     not null position next
         ,       AmountStdToegestanekosten decimal     not null position next
    )  t
2

De Invantive foutcode (itgen......) is niet zichtbaar, maar waarschijnlijk is de oorzaak dat er een witregel zit tussen as en select.

De on-premises Invantive-producten zoals Invantive Query Tool breken de uit te voeren statements op in individuele statements ter uitvoering op witregels.

In dit geval worden er twee statements gezien:

  • create ... as
  • select ... ) t

Advies is de witregel te verwijderen.

3

Klopt. Dit werkt

4

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