Retrieve data by executing SQL

Logic Name

Retrieve data by executing SQL

Function

Returns data retrieved by executing SQL.

Data Types

Location Type
Logic output String
lightbulbRefer to "Logic Specifications" for details.

Properties

Category Property Description
Required Destination Select global resource.
Required SQL statement Specify SQL statement to be executed.
Required Parameter definition Specify parameter type.
0 match record Processing option Specify how to handle the case of 0 matching record.
0 match record Alternative string Specify the alternative string.
Comment Comment The comment will also be output to the specifications.It does not affect the conversion process.

0 match record / Processing option

Option Description
Raise exception Throws the following exception.

Caused by:[com.appresso.ds.vp.mapper.fastconverter.NoSuchRecordException: ExecuteSelectQueryLogic - <SQL statement>
Replace by alternative string Replaces with the value of [Alternative string].

Function Details

SQL Parameter

SQL parameter can be used in this logic.

How to use SQL parameter

SQL parameter is described as "?{name}". You can map parameters to increased input handler.
For example, when you write "select value from test_table where key1 = ?{param1} and key2 => ?{param2}" in [SQL statement], 2 input handlers are configured allowing you to make a mapping to SQL parameter (param1 and param2).

Available Global Resources

Global resources of the following adapters can be specified in [Resource name].

Adapter Type
Access Adapter
DB2 Adapter
MySQL Adapter
Oracle Adapter
PostgreSQL Adapter
SQL ServerAdapter
JDBC Adapter
ODBC Adapter
Dr.Sum EA Adapter
Amazon RDS for MySQL Adapter
Amazon RDS for Oracle Adapter
Amazon RDS for PostgreSQL Adapter
Amazon RDS for SQL Server Adapter
Azure SQL Database Adapter

Specification Limits

Logic Sample

Mapping Definition

Logic Settings and Processing Result

Execute SQL and obtain data from a table.
Property Settings
Property Setting
Destination DB
SQL statement select VALUE from test_table where DAY_ID = ?{param1} and LOCALE = ?{param2}
Parameter definition
Parameter name Type
param1 int (int)
param2 varchar (String)
0 match record / Processing option Replace by alternative string
0 match record / Alternative string NOT_EXIST
Input Data
1JAPANESE
2JAPANESE
3JAPANESE
4JAPANESE
5ENGLISH
6ENGLISH
7ENGLISH
Search target table
DAY_IDLOCALEVALUE
1JAPANESENichiyoubi
2JAPANESEGetsuyoubi
3JAPANESEKayoubi
4JAPANESESuiyoubi
5JAPANESEMokuyoubi
6JAPANESEKinyoubi
7JAPANESEDoyoubi
1ENGLISHSunday
2ENGLISHMonday
3ENGLISHTuesday
4ENGLISHWednesday
5ENGLISHThursday
6ENGLISHFriday
7ENGLISHSaturday
Result Data
Nichiyoubi
Getsuyoubi
Kayoubi
Suiyoubi
Thursday
Friday
Saturday