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 ,  Delete ,  R
Leer más

3 ways to send a mail from code in Sage X3, with more attachments too

Origen Fuente:  https://en.sagedev.it/sagex3/send-mail-from-code-with-attachments-sage-x3/ Autor:  https://en.sagedev.it/category/sagex3/ In this post I’ll show you how to send a mail from adonix code in Sage X3. The points we will face are: 1) Meladx/Send introduction 2) Sending  through  meladx (the best way for me: at the end with just a single code line you send a mail!) 3) Sending  through  Workflow 4) Sending  through  ENVOI_MAIL(…) From AWRKMEL Sending mail from code in Sage X3: Send vs meladx Sage X3 has two native ways for sending mails. The first way is  meladx  executable file, that you can find in  runtime\bin  directory. The seconde one is Send instruction, that was used to send mail through an application of the client station (for more information on “Send GSERMES”  go here ). The Meladx executable Meladx send messages through SMTP/POP3 protocols by means of the mail  server  specified in the command. When sending is launched by a work
Leer más

How to create an additional line text (ACLOB) on purchase orders for internal notes

Sin conexión Kyle Klinger hace 1 día Origen:  https://www.sagecity.com/support_communities/sage_erp_x3/f/sage-x3-general-discussion-forum/143870/how-to-create-an-additional-line-text-aclob-on-purchase-orders-for-internal-notes I was trying to add internal notes to the purchase orders at a line level and was thought I would share a how to.  If you have a better way, please share.     Add column to PORDERQ; YLINTEX2, Type TXC   Save and validate table This is where the ID of the text will be stored at the record level, i.e. POQ~00000007, this is similar to field LINTEX  On screen POH2 Add column YLINTEX2, to Block 1. Most likely you will want this field to be hidden. On column NBLIG add a button action ACLOB2, description "Text internal" This button action will require an action parameter "CODE2", it will not be available until after save. Set the parameter "CODE2" to [M:POH2]YLINTEX2(nolign-2), the field that was just added.  Save a
Leer más