The following code can be used to create a sql view in a given, external database.
// local variablesADOConnection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.ConnectionADOCommand Automation 'Microsoft ActiveX Data Objects 2.8 Library'.CommandADORecSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.RecordsetconnString Text 1024activeConnection VariantviewName Text 100// the code// replace the server ip and the ext. databasename with your valuesconnString := 'Driver={SQL Server};Server=127.0.0.1;Database=Cronus600';CREATE(ADOConnection);ADOConnection.ConnectionString(connString);ADOConnection.Open;// convert ADO Connection to VariantactiveConnection := ADOConnection;viewName := 'CRONUS$Item View';CREATE(ADOCommand);ADOCommand.ActiveConnection := activeConnection;ADOCommand.CommandType := 1;ADOCommand.CommandText := 'SELECT * FROM INFORMATION_SCHEMA.TABLES where ' + '(TABLE_TYPE = ''VIEW'') and (TABLE_NAME =''' + viewName + ''')';ADORecSet := ADOCommand.Execute;IF ADORecSet.EOF = TRUE THEN BEGIN ADOCommand.CommandText := 'CREATE VIEW [' + viewName + ']' +'AS SELECT No_, Description FROM dbo.[CRONUS$Item]' +'WHERE No_ like ''100%'''; ADOCommand.Execute; message('view created');END ELSE MESSAGE('view already exists');ADORecSet.Close;ADOConnection.Close;CLEARALL;
follow also posting “Sql Statements in C/AL – Part 1 : Select”.
cheers