Scribd
Upload
120 views2 pages

SQL Queries in 4GL for Sage X3

The document explains how to execute SQL queries using 4GL language, particularly when aggregate functions are needed. It provides examples of SQL query syntax within 4GL, including how to define database types and retrieve data from a database. Additionally, it details how to identify the database type used in Sage X3 to ensure correct SQL execution.

Uploaded by

Axl Axl
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
120 views2 pages

SQL Queries in 4GL for Sage X3

The document explains how to execute SQL queries using 4GL language, particularly when aggregate functions are needed. It provides examples of SQL query syntax within 4GL, including how to define database types and retrieve data from a database. Additionally, it details how to identify the database type used in Sage X3 to ensure correct SQL execution.

Uploaded by

Axl Axl
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Executing a sql query using 4GL language

By Greytrix | November 22, 2013


0 Comment
There are many a time when we need to get the aggregate or maximum function but 4GL
Language do not offers a syntax for the same.
But we definitely have an option to use SQL queries in 4GL and this does the most of the
job for us.

Just go through the statements mentioned below:

Format:
Local char REQSTR(250)(10)
For [Table name] where document no
REQSTR(0) = “SQL query ”
For fields_0 from Databasetyp Sql REQSTR(0…10) as tablename
Databasetyp: it is a database type for this we have create function so it will identify that
current database uses by X3 server
We have applied this with example.
Local Char REQSTR(250)(10)
For [Z3T] Where 1=1
For [ZQRY] Where DOCNO = [F:Z3T]DOCNO
REQSTR(0) = “SELECT DOCDATE_0, QRNO_0 FROM ZETIQUERY WHERE ROWID    
=”+num$([F:ZQRY]       ROWID)+””
For (Date DOCDATE, Char QRNO) From func GETDBTYP  Sql REQSTR(0..10) As
[ZYOP]
Update [Z3T] Where DOCNO = [ZQRY]DOCNO With DOCDATE = [F:ZYOP]DOCDATE
Update [Z3T] Where DOCNO = [ZQRY]DOCNO With QRNO = [F:ZYOP]QRNO
Update [Z3T] Where DOCNO = [ZQRY]DOCNO With MARK = 2
Next
Next
Next

OTHER EXAMPLE
$GETBANKRECORDS
#**
#* Use this technique of a SQL_CLOB and Setlob to be able to write your sql easier.
#* Append works nicely here to save valuable keystrokes on your keyboard
#*!
Local Clbfile SQL_CLOB(5)
Local Char SQL_REQ(255)(20)
Local Char RES1
SQL_CLOB = "SELECT BAN_0 FROM [Link]"
Append SQL_CLOB, " ORDER BY BAN_0"

# Assign to a char array the value of SQL_CLOB


Setlob SQL_REQ With SQL_CLOB

# CHAR(RES(80(1)) will create an array of results


For (Char RES(80)(1)) From "5" Sql SQL_REQ As [REQ]
RES1 = num$([F:REQ]RES(0))
Infbox num$(RES1)
Next
End
Return

Find the Database Type to execute the SQL


Query from Sage X3
By Greytrix | June 4, 2016
0 Comment
To implement the business logic we need to use SQL Query to fetch the required data from
the Database. To execute the SQL Query from Sage X3 we need to know the database
type. We can fetch the database type as below:

The “TYPDBA” column in “ADOSSIER” table holds the value which will help to identify the
Database type.

The above logic will return which database type is used to store the data.

“3”: This will return when the database type is “Oracle”


“5”: This will return when the database type is “SQL”
And according to database type, the SQL query is executed and data is fetched from
database.

Hope this blog helps!

Common questions

Powered by AI

The append function is critical as it allows for the dynamic building of SQL queries, making it easier to modify, extend, or combine queries without manually rewriting large volumes of text. This function reduces keystrokes and minimizes errors, improving both the efficiency and accuracy of SQL query construction within 4GL .

Dynamically updating table entries with SQL in 4GL allows for real-time data manipulation and integration, which streamlines processes. Benefits include increased efficiency, real-time data accuracy, and enhanced application interactivity. Risks involve potential data integrity issues from malformed queries and the complexity of maintaining dynamic SQL within 4GL environments .

The process involves executing an SQL query to fetch data (e.g., select DOCDATE_0 and QRNO_0), storing the results in local variables, and then using these variables to update fields in another table via SQL updates. This method makes use of data extracted via SQL to pinpoint and modify specific entries in the destination table efficiently .

The 'Local Char' is used to define string variables in which SQL queries are constructed and prepared for execution. This allows for modular and manageable code within 4GL, where queries are dynamically constructed before execution. Its impact is seen in the ease of query modification and execution control, facilitating better management of query logic and parameters .

The recommended method for managing SQL queries involving large text in 4GL is using SQL_CLOB and Setlob, as it allows writing SQL more easily and efficiently. This technique enables the appending of SQL queries, saving keystrokes and making query construction more manageable .

Storing SQL_CLOB data in a char array such as SQL_REQ in 4GL could provide advantages like simplifying SQL command manipulation, enhancing readability, and facilitating dynamic SQL execution. This storage method allows for efficient restructuring or appending of SQL queries, thereby optimizing the usage of SQL within the 4GL environment .

Fetching database types using the 'TYPDBA' column ensures that SQL operations are tailored to the specific database environment, whether it be Oracle or SQL Server. This streamlines operations by dynamically adapting queries to the correct syntax and functionalities available for each database type, reducing errors and enhancing performance .

The 'num$' function converts numeric database values into string format, aiding in the handling and display of SQL query results. It impacts execution by ensuring relational data can be easily manipulated or processed within 4GL scripts, simplifying operations that require string formatting or concatenation of numeric results .

Since 4GL does not directly support aggregate or maximum functions, SQL queries can be integrated into 4GL to perform such tasks. This is done by composing SQL commands within the 4GL framework, allowing the execution of complex queries through SQL that 4GL cannot handle alone .

Identifying the database type is crucial because the syntax and execution of SQL queries can differ significantly between database systems like Oracle and SQL Server. Knowing the database type ensures that the appropriate SQL dialect is used, preventing errors and improving the accuracy and performance of data retrieval processes in Sage X3 .

You might also like