[TRAFODION-3091] Unnamed Dynamic parameter doesn't work in WITH statement

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql-cmp
• Assignee: Yang, Yongfeng
• Reporter: Yang, Yongfeng
• Created: 30/May/18 16:06
• Updated: 30/May/18 16:06

Description

prepare s1 from WITH req AS (
SELECT 'Y' apr_tgt_yn
FROM msg_sign_rslt
WHERE signer_id = DECODE(signer_gbn, 'G', (SELECT 'D' FROM usr_user WHERE user_id = ?))
)
,req_info AS (
SELECT apr_tgt_yn FROM req
UNION ALL
SELECT apr_tgt_yn FROM req
)
select * from req_info;

execute s1 using admin;

 

create a simple table:     create table t1 (a int , b varchar(10)); insert data:     insert into t1 values(1,'bbb');   in trafci or sqlci: prepare s from with aa as ( select a from t1 where b = ? ) select * from aa; execute s using 'bbb'; ----no result prepare s from with aa as ( select a from t1 where b = ?a ) select * from aa;   set param ?a 'ING'; execute s ; ----has result