Invalid Query Gets Passed to DB2 Database for Query with the Use of the 'with' Clause
Article ID: KB0083049
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | - |
Description
This document describes the solution to the problem that occurs when invalid query gets passed to DB2 database for query with the use of the 'with' clause.
Customer identified an issue on how CIS parses the Structured Query Language (SQL) query when they use the 'with' clause in SQL.
CIS query:
WITH st AS
(SELECT risk_level3_node_id
FROM vdp.ref_scenario_type_taxonomy
WHERE scenario_type_id = 'S16' AND legal_entity_code = '0101'
)
SELECT * FROM st;
Invalid query passed to DB2:
WITH st AS
(SELECT "expr8"."RISK_LEVEL3_NODE_ID" AS "expr9"
FROM "VDP"."V_VDP_LE_SCENARIO_RISKS" "expr8"
WHERE ("expr8"."LEGAL_ENTITY_CODE" = '0101'
AND "expr8"."SCENARIO_TYPE_ID" = 'S16'))
SELECT st."risk_level3_node_id" FROM st;
When CIS passes the query to underline DB2 database, it renamed column ‘RISK_LEVEL3_NODE_ID’ to ‘expr9’. Then ‘SELECT st."risk_level3_node_id" FROM st’ fails as column ‘risk_level3_node_id’ does not exist anymore, it is now ‘expr9’. Obviously it is an error when CIS translates query to underline data source.
Customer identified an issue on how CIS parses the Structured Query Language (SQL) query when they use the 'with' clause in SQL.
CIS query:
WITH st AS
(SELECT risk_level3_node_id
FROM vdp.ref_scenario_type_taxonomy
WHERE scenario_type_id = 'S16' AND legal_entity_code = '0101'
)
SELECT * FROM st;
Invalid query passed to DB2:
WITH st AS
(SELECT "expr8"."RISK_LEVEL3_NODE_ID" AS "expr9"
FROM "VDP"."V_VDP_LE_SCENARIO_RISKS" "expr8"
WHERE ("expr8"."LEGAL_ENTITY_CODE" = '0101'
AND "expr8"."SCENARIO_TYPE_ID" = 'S16'))
SELECT st."risk_level3_node_id" FROM st;
When CIS passes the query to underline DB2 database, it renamed column ‘RISK_LEVEL3_NODE_ID’ to ‘expr9’. Then ‘SELECT st."risk_level3_node_id" FROM st’ fails as column ‘risk_level3_node_id’ does not exist anymore, it is now ‘expr9’. Obviously it is an error when CIS translates query to underline data source.
Issue/Introduction
Invalid Query Gets Passed to DB2 Database for Query with the Use of the 'with' Clause
Resolution
Issue addressed in 7.0.3.00.18.
Feedback
Yes
No
Powered by
