REST API Reference > Saved SQL query REST APIs > Creating a saved SQL query
  

Creating a saved SQL query

Use the following URI to create a saved SQL query:
POST /datavalidation-service/api/v1/savedSqlQuery
Use the IDS-SESSION-ID header in the POST request.
POST request
Use the following fields in the POST request:
Field
Required?
Description
name
Yes
Name of the saved SQL query.
The name can contain ASCII, Chinese, Hebrew, and Japanese characters, digits, spaces, and the following characters:
, _ -
The name can't contain the following characters:
` " ' ! " # $ % & ( ) * + ' . / : ; < > = ? @ [ ] \ ^ ~ { } |
description
No
Description of the saved SQL query.
query
Yes
Saved SQL query to be used.
The SQL query can have a maximum of 30,000 characters.
databaseType
Yes
Connection ID and database type for the saved SQL query.
databaseTypeName
No
Type of the database that the saved SQL query fetches data from.
Specify one of the following values:
  • - Amazon Redshift v2
  • - Databricks
  • - DB2
  • - Google BigQuery V2
  • - Microsoft Azure Synapse SQL
  • - MySQL
  • - Oracle
  • - PostgreSQL
  • - Snowflake Data Cloud
  • - SqlServer
frsdocLocation
No
The location to save the query.
To save the query in a project, use the following syntax:
"{\"type\":\"Project\",\"id\":\"<project_ID>\",\"path\":\"<project_name>\"}",
To save the query in a folder, use the following syntax:
"{\"type\":\"Folder\",\"id\":\"<folder_ID>\",\"path\":\"<folder_name>\"}",
When you open a project, the numeric value that you see in the URL is the project ID. Similarly, when you open a folder, the numeric value that you see in the URL is the folder ID.
By default, the query is saved under the Default project.
validationInfo
No
Connection details to validate the saved SQL query.
connectionId
Yes
ID of the connection that Data Validation uses to validate the saved SQL query.
When you open the connection in Administrator, the numeric value that you see in the URL is the connection ID.
For example, if the URL is <Informatica Intelligent Cloud Services URL>/cloudUI/products/administer/main/ConnectionDetailsWS/014N8B0B000000000ATW/read, the connection ID is 014N8B0B000000000ATW.
POST request sample
The following snippet shows a POST request sample to create a saved SQL query:
{
"savedSqlQuery": {
"name": "Saved_SQL_Query_EMP_DEPT",
"description": "Fetches employee details by joining Employee and Department tables",
"query": "select * from employees e inner join dept d on e.did = d.id",
"databaseType": {
"id": "1",
"databaseTypeName": "Oracle"
},
"frsdocLocation": "SSQ"
},
"validationInfo": {
"connectionId": "0140EK0B0000000002W3"
}
}
POST response
If the saved SQL query was created successfully, the POST request returns a 200 Successful operation response and the following response fields:
Field
Type
Description
id
String
ID of the saved SQL query.
name
String
Name of the saved SQL query.
frsDocId
String
Location where the SQL query is saved.
validationState
String
Validation status of the SQL query. Displays one of the following values:
  • - INVALID. The query is not valid. Fix the query and validate it again.
  • - VALID. The query is valid and can be used in a test case.
  • - NOT VALIDATED. The query has not been validated. You must validate the query to use it in a test case.
If the saved SQL query creation failed, the POST request returns a 400 Bad request response or a 500 Internal Server Error response. It also returns the following response fields:
Field
Type
Description
code
String
Code of the error message.
message
String
Error message.
debugMessage
String
Message that can be used for debugging the issue.
requestId
String
String that uniquely identifies the request. Used for debugging purposes.
details
String
Detailed information about the error message.