CODEWITHSUNDEEP
db2db2-400
learningbyexample
learningbyexample

Reputation: 1547

How do I concat in DB2 so that my select query returns the inserts that I need to make

I have a select that gives me the data that I need to look up after analyzing I need to insert that data into another table so that I can do the process again

SELECT distinct 'insert into ftdata values ( '& char(39) & user, partner, '', timedt, datetm,'',0,0 ')'
FROM ttab
JOIN
ttab2
on user2=user
where timedt>= '20170611'
and (statusdue='now');

I also tried

SELECT distinct CONCAT('insert into ftdata values (' user, partner, '', timedt, datetm,'',0,0 ')')

This is suppose to return for all the records found

insert into ftdata values (user, partner, '', timedt, datetm, '', 0, 0)
insert into ftdata values (user, partner, '', timedt, datetm, '', 0, 0)
insert into ftdata values (user, partner, '', timedt, datetm, '', 0, 0)
insert into ftdata values (user, partner, '', timedt, datetm, '', 0, 0)
insert into ftdata values (user, partner, '', timedt, datetm, '', 0, 0)

I tried to concat like shown in my query with '&' and also tried '+'

Upvotes: 1

Views: 777

Answers (2)

Esperento57
Esperento57

Reputation: 17462

Multiple method for concat (its depend of interpretor SQL) :

1) select concat(zone1, zone2, 'test', zone3, ..., zoneN) from yourtable
2) select zone1 concat zone2 concat  'test' concat  zone3 concat  ... concat zoneN) from yourtable
3) select zone1 || zone2 || 'test' || zone3 || ... || zoneN) from yourtable

If you want dynamiclly build a query with datas tables, dont forget to add double quote for insert char or varchar type column

be carefull to date or timestamp type : do || '''' || cast(zonedateortimestamp as varchar(35)) || '''' ||

try this :

SELECT distinct 'insert into ftdata values ( ''' || user || ''', ''' || partner || ''', ''' || timedt || ''', ''' || datetm || ''', '''', 0, 0 )'
FROM ttab inner join ttab2 on user2=user
where timedt>= '20170611' and statusdue='now';

Upvotes: 1

Dam
Dam

Reputation: 1074

Concat with double pipe || : 'insert into ftdata values (' || user || partner || ...

Upvotes: 0

Related Questions