Using DJX to Create SQL Statements
DJX provides pragmatic information to the compiler. A pragma is an instruction to the compiler to perform an action at compile time.
Use DJX to escape into the expression language where you can design SQL statements that have meaning to the compiler. Otherwise, the SQL statement action is treated as a literal SQL statement.
Dynamic SQL Statements
When you use DJX to escape into the expression language, you have the ability to build your SQL statements dynamically. This is the same way that you build select statements and stored procedure calls with the DJImport object using variables and source fields. This method works for SQL statement actions in multimode and SQL steps in the Process window.
Source query statements can also be built using DJX. These more complex expressions are also covered in this topic.
Example of SQL Statement with Integer and Character Data Types
In this example, an integer and a character are used intentionally. The character data requires quoted identifiers, so this example will demonstrate how to use both integer and character data types:
Create table TEST(
F1 int,
F2 char(10))
In a static SQL statement, the Insert action would be:
Insert into TEST (F1,F2) values (1,'hi')
If you want to use values or variables from some other source, you can use DJX to build the insert statement dynamically. DJX, in its role as a pragma, tells the compiler that the SQL statement must be resolved before it is sent to the database engine. Here is an example:
DJX("Insert into TEST (F1,F2) values (" & FieldAt("/SOURCE/R1/Account No") & ",'" & variable & "')")
Before you continue, review the following points:
• Values within the double quotes are literal strings.
• The literal strings and the variables are concatenated in the result.
• The integer value does not require double quotes.
• The character data is enclosed in single quotes.
• The first single quote follows the comma that separates the values to be inserted, and both are within double quotes.
• The closing single quote, and the closing parenthesis (closes the values clause) follow the last ampersand.
If FieldAt("/SOURCE/R1/ACCOUNT") equals 10019 and the variable value is "hello", the SQL statement produced in DJX is:
Insert into TEST (F1,F2) values (10019,'hello')
This demonstrates how you can build a SQL statement dynamically using DJX to incorporate values from field references and variables.
DJXBegin and DJXEnd
DJXBegin and DJXEnd are used together to define the boundaries of a more complex SQL statement. Extending the utility of the DJX pragma, you can use DJXBegin and DJXEnd to design multiple line SQL statements that contain Where or Values clauses.
Example
Here is an example of a source query statement with a Where clause based on the value of a variable:
Select * from tester.TUTOR1
djxbegin
Dim start
start = ""
Select Case var
Case 1
start = " where ACCOUNTNO = 10019"
Case 2
start = " where ACCOUNTNO = 10023"
Case Else
start = ""
End Select
Return Start
djxend
Review the following points in this expression:
• DJXBegin tells the compiler that multiple lines of the expression language follow for evaluation.
• The Select statement is static in this example.
• The Where clause is based on a Private variable named "var". (Passed into the transformation map from a process.)
• If the variable does not match a value in the Select Case statement, the expression returns the entire record set. ( start = "" )
• "Return start" appends the Where clause to the Select statement.
If the value of var is 1, the Query statement is:
select * from tester.TUTOR1 where ACCOUNTNO = 10019
Using DJXBegin, DJXEnd, and techniques from the DJX section you can build a Where clause dynamically. The Where clause can be built for this example or for a Delete statement as shown in the DJX section.
Last modified date: 10/22/2024