Parameters and teradata Dynamic SQL

Question

I'm trying to use a parameter within my dynamic sql query, but just can't get it to work. I simplified my code to highlight only the part I'm struggling with.

I first create 't1', to which I insert the record.

create volatile table t1 as(

select date '1900-01-01' as date_col

)with data on commit preserve rows;

First procedure works fine, and insert the given date (2014-01-01).

replace procedure mi_table.dynamic_param()

begin

CALL DBC.SYSEXECSQL(

'insert into t1

select date '||'''2014-01-01'''||' ;'

);

end;

What I really want to do and doesn't work, is something as such:

replace procedure mi_table.dynamic_param() begin

declare max_avail_date date;

set max_avail_date = (select period_dt from db.table) ;

CALL DBC.SYSEXECSQL(

'insert into t1 select '||:max_avail_date||' ;'

);

end;

which doesn't work. It seems like what is returned from '(select period_dt from db.table)' is under 'yy/mm/dd' format. Thanks to the suggestions below, I came up with the following (non-elegant) solution:

Whenever I want to use max_avail_date, I use cast((cast('''||'20'||'''||'''||max_avail_date||''' as int)-19000000) as date) .

It does the job, but I'm sure there's a better way of doing it :)

edited 2014-09-27, 6:50pm

dnoeth · Answer 1 · 2014-09-28T09:56:21.490

1

"doesn't work" is not a very precise error description :-)

You probably got a "invalid date" message?

The concatenated string in strSQL must be a valid SQL statement, your's resulted in

insert into t1 select date 2014-01-01 ;

This will work:

replace procedure mi_table.dynamic_param() begin

declare max_avail_date date;

set max_avail_date = date '2014-01-01' ;

CALL DBC.SYSEXECSQL(
'insert into t1 select '''||:max_avail_date||''' ;'

);

end;

Edit:

Regarding the issue with the 'yy/mm/dd' format this is probably due to the global default on your system. You better implicitly assign a FORMAT:

CALL DBC.SYSEXECSQL(
'insert into t1 select '''|| (:max_avail_date (FORMAT 'yyyy-mm-dd'))||''' ;'

);

edited Sep 28 '14 at 09:56

answered Sep 26 '14 at 20:57

dnoeth

59,503
4
39
56

Weird, I couldn't get the `set max_avail_date = date '2014-01-01' ;` to work, guess I must have fat-fingered something. – Andrew Sep 26 '14 at 21:25
Thanks a lot for your answer. Also, good point, I didn't say what wasn't working. To be honest, I didn't know myself. I did some more tests, and now I think I know where the issue is. Let me edit my initial post. When simplifying my query, I got rid of what I think I just identified as a key element. – Charles Ayotte Sep 27 '14 at 22:41

score 0 · Answer 2 · answered Sep 26 '14 at 19:06

This should work:

declare max_avail_date varchar(10);
declare strSQL varchar(2000);
set max_avail_date = '2014-01-01';
set strSQL = 'insert into t1 select  (date''' || max_avail_date || ''')';
CALL DBC.SYSEXECSQL(:strSQL);

I could never get it to work the way you're trying it. This bit here was killing me: set max_avail_date = date '2014-01-01' ; I think trying to use the cast in the set statement won't work.

Setting it as a string and converting it to a date in the select statement works fine.

Parameters and teradata Dynamic SQL

2 Answers2