Ir al contenido principal

Sentencia SQL directamente desde Sage

Origen fuente:http://online-help.sageerpx3.com/erp/wp-static-content/static-pages/en_US/v7dev/4gl_sql.html
Autor: SAGE

Commando SQL
Sql allows you to execute a Select statement in the database.

Syntax

   For (VARIA_LIST) From DATAB_TYPE Sql SQL_STATEMENT As  [CLASS]
   Next
  • VARIA_LIST is a list of VARIA separated by commas.
  • VARIA can contain one of the following syntaxes:
    • DECLARATION VARNAME
    • DECLARATION VARNAME ( INDEX_SEP )
  • DECLARATION is a declaration keyword that can be Char, Shortint, Integer, Date, TinyInt, Decimal, Clbfile, Blbfile, Datetime, or Uuident. If DECLARATION is Char, VARNAME must be followed by (N) where N is the maximum size of the string, before a possible list of (INDEX_SEP).
  • VARNAME is a variable name starting with a letter, and followed by letters, digits, or underscores.
  • INDEX_SEP is a list of DIMENSION or DIMENSION_RANGE separated by commas. If 'N' commas are given, it describes an array of dimension 'N+1'. The number of commas is therefore limited to 3.
  • DIMENSION_RANGE has the following syntax: DIMENSION``..``DIMENSION.
  • DIMENSION is an expression returning a numeric dimension.
  • DATAB_TYPE is a string expression that returns a code defining the database used.
  • SQL_STATEMENT is a string expression that returns the SQL Select statement executed by the Sqlinstruction. It can be an array of string variables, with or without a range of indexes. If no range is given, all the different indexes of the array are concatenated to give the SQL statement. Otherwise, only the elements within the range are used.

Examples

  # Example 1: Request to select a maximum value in a table
  Local Char REQUEST(255)
  Local Integer MAX_VAL
  REQUEST="select max(ACCNUM_0) From GACCENTRYD"
  For (Integer NUM) From "3" Sql REQUEST As [XXX]
    MAX_VAL=[F:XXX]NUM
  Next


  # Example 2: Request to select the different fields of a table in data dictionary
  Local Char REQUEST(255)
  Local Char MYTABLE(20)
  ...
  REQUEST="select CODZONE_0, DIME_0 From ATABZON Where CODFIC='"+MYTABLE+"'"
  For (Char FIELD(12),Integer DIMENSION) From "3" Sql REQUEST As [ABB]
    # Here, we have for every loop FIELD that is equal to the column name and DIMENSION to its dimension
  Next


  # Example 3: Request with an array
  Local Char REQUEST(255)(1..5)
  REQUEST(1)="select BPCNAM_0"
  REQUEST(2)=" From BPARTNER"
  REQUEST(3)=" Where BPCFLG_0=1"

  # First request without where
  For (Char NAMES(20))From "S" Sql REQUEST(1..2) As [BPC]
    # All the lines will be seen
  Next

  # Second request with the where 
  For (Char NAMES(20))From "S" Sql REQUEST(1..3) As [BPC]
    # Only the lines with BPCFLG_0 equal to 1 will be seen
  Next


# Example 3: Use the default database
  Local Integer DBTYPE
  Local DATABASE_TYPE(10)
  DBTYPE = fmet GACTX.APARAM.AGETVALNUM([V]CST_ALEVFOLD,"","TYPDBA")
  DATABASE_TYPE=string$(DBTYPE=1,"O")+string$(DBTYPE=2,"S")

  For (Integer INDEX,Char NAMES(20)(1..2)) From DATABASE_TYPE SQL "Select INDEX_0, NAME_0, NAME_1 From MYTABLE" As [MYT]
    # On every loop instance, we have the following values available:
      # [MYT]INDEX, [MYT]NAMES(1), [MYT]NAMES(2) 
  Next

Description

Sql allows you to execute a Select statement on the database. The list of parameters in this syntax will be filled by the data returned in every loop for one of the lines of the cursor.
The database type is given by the following values:
* "o","O","3" indicates Oracle.
- "s","S","5" indicates SQL server.
The SQL expression can be given with a string array. When this happens, all the lines of the array (limited to a range if a range is given) are concatenated to give the SQL request to be executed.
The class abbreviation is used to access the variables in the list.

Associated errors

Error codeDescription
19Database incorrect: the database type is not Oracle or SQL server.
69Incorrect arguments: the argument list has less or more than the number of columns returned by the select statement, or the data type does not fit with the columns returned.
75Database error: a column mentioned in the SQL statement does not exist.

Entradas populares de este blog

Valores de fstat

Fstat fstat  is a numeric status that is returned upon execution of a database operation, a sequential file operation, or a lock instruction. Syntax fstat Examples # MYTABLE is a table with a key called KEY1, that has a unique component called KEYVAL # Create a record in the table MYTABLE with they key value 1 if it doesn't exist Local File MYTABLE [MYT] Read [MYT]KEY1=1 If fstat [MYT]KEYVAL=1 : Write [MYT] If fstat MSG="The key was created in the mean time" Else MSG="Key created" Endif Else MSG="Key already exists" Endif Details fstat  is always set to '0' if the operation is successfully completed, and has a non-null value if there is an error: In a sequential read ( Getseq  and  Rdseq ),  fstat  is set to '1' at the end of the file. On  Lock ,  fstat  is set to '1' if the lock could not be performed. For a database operation ( Read ,  Look ,  Readlock ,  For ,  Write ,  ...
Leer más

SAGE X3 SILENT IMPORT (IMPORTSIL) WITH ERROR CATCHING

FUENTE ORIGINAL: https://pluginx3.com/en/blog/post/sage-x3-silent-import-importsil-with-error-catching.html You already know how to manually generate a CSV file in order to import it threw a template in Sage X3. But If you wonder how to catch detailed errors after a silent import here is the solution. Sometimes you need to catch the incoming errors in order to alert the end-user regarding an issue. Closed period, wrong date etc …. Unfortunalty the standard Sage X3 silent import function doesn’t natively show the detailed errors during the import process. Call IMPORTSIL([M:IMP2]MODIMP,[M:IMP2]NOMIMP) From GIMPOBJ And the standard function ERR_IMPORT will only show the overall status of the importation and not the detailed errors. IMPORTSIL function is pushing all the log data into a tracefile in order to avoid any popup on end-user screen. So you have a log file available in order to search for errors in it. Sage X3 runtime is using the same principals standards as any shell for log...
Leer más

Sage X3 Create a new CLOB Text

Here how to create a CLOB text Funprog F_CRELOB ( YABRFIC , YTEXTE ) Value Char YABRFIC Value Char YTEXTE Local Char XXRTFTXT ( 250 ) ( 3 ) XXRTFTXT ( 0 ) = "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs17 " XXRTFTXT ( 1 ) = YTEXTE XXRTFTXT ( 2 ) = "\par}" Local Char X_RET Local Integer XXOK Local Char XCHAMPLIEN : XCHAMPLIEN = "YYYY" Global Char YYYY : YYYY = YABRFIC Local Clbfile WWCLOB Local Mask ACLOB [ M :ACL ] Setlob WWCLOB With XXRTFTXT [ M :ACL ] CLOB = WWCLOB Call CRE_CLOB_ACL ( XCHAMPLIEN , XXOK ) From TRTX3TEX Close Local Mask [ M :ACL ] X_RET = YYYY Kill YYYY End X_RET
Leer más