Db2 insert statement having one of the columns as select statement

This is in continuation of other posts that I had for db2 executing dynamic query..
I have a situation where I get insert statement query as a column value in db2 and I am executing the insert query in db2 ..

Table test_query in db2 has column query1 varchar(4000) and it has value 'insert into test_cs values (select ... from table...)

initially I had test_cs table to have only 1 column as GB_Value decimal(22,3) and then I wrote the stored procedure in db2 to execute the above query..

Now in reality the table test_cs has more than 1 column, it will have id column and will have value like sequence number and a description column as to what it inserts

The table test_cs has the following columns
id_value integer,
gb_value decimal(22,3),
description varchar(1000)

I changed the test_query table to have the query1 column value to be insert into test_cs (id_value,description,gb_value) values (1,'This is a test','select <blah blah> from table')

I tried my stored procedure but I have problems now,.

A database manager error occurred.[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "1" was found following "values,".  Expected tokens may include:  space
SQLSTATE=42601

Any ideas as to how to make this work..
mahjagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
> id_value integer,
> gb_value decimal(22,3),
> description varchar(1000)
> ...
> (id_value,description,gb_value) values (1,'This is a test','select <blah blah> from table')

It appears as if you're trying to insert a string into a decimal. Notice that gb_value has a datatype of decimal(22,3), but you're inserting 'select <blah blah> from table' into it.

HTH,
DaveSlash
0
mahjagAuthor Commented:
sorry I jumped early, the order of insert was correct, I had some posted my insert statement wrong,.,

what I am not sure is  - is it possible to insert multiple columns before the actual select query into a table ..?
0
mahjagAuthor Commented:
I am posting my original stored procedure for executing dynamic query..
create procedure RUN_QUERY()
 
LANGUAGE SQL
 
BEGIN
--Declare generic variables
 
 DECLARE        v_counter               INTEGER DEFAULT 0;
 DECLARE        v_message               varchar(70);
 DECLARE        v_sqlstate              varchar(5);
 DECLARE        v_at_delete             smallint;
 DECLARE        v_start_timestamp       timestamp;
 DECLARE        v_end_timestamp         timestamp;
 DECLARE        v_procstart_timestamp   timestamp;
 DECLARE        v_procend_timestamp     timestamp;
 DECLARE        SQLSTATE                char(5);
 DECLARE        at_notfound             SMALLINT DEFAULT 0;
 DECLARE        at_null                 SMALLINT DEFAULT 0;
 
 DECLARE command1 VARCHAR(1000);
 
 
 DECLARE not_found
 CONDITION for SQLSTATE '02000';
 
 DECLARE  SQLEXEPTION
 CONDITION for SQLSTATE '23502';
 
 DECLARE CONTINUE HANDLER FOR not_found
 BEGIN
   SET at_notfound = 1;
 END;
 
 DECLARE CONTINUE HANDLER FOR SQLEXEPTION
 BEGIN
   SET at_null = 1;
 END;
   set v_procstart_timestamp = current timestamp;
   set v_start_timestamp = current timestamp;
 
   BEGIN
 DECLARE cursor1 CURSOR FOR
 SELECT QUERY1 from TEST_QUERY;
  OPEN cursor1;
  WHILE at_notfound = 0 DO
    FETCH cursor1 INTO command1;
    EXECUTE IMMEDIATE command1;
  END while;
  CLOSE  cursor1;
 
 
  set v_procend_timestamp = current timestamp;
  set v_end_timestamp = current timestamp;
 
 
INSERT INTO ERRORS VALUES('TST',current timestamp,command1,'Procedure',
                                 null,char(v_procstart_timestamp)||' '||char(v_procend_timestamp));
 
END;
 
END                     

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Dave FordSoftware Developer / Database AdministratorCommented:

Can you please post the contents of TEST_QUERY?

-- DaveSlash
0
mahjagAuthor Commented:
insert into ais.test_cs (id_value,description,gb_value) values 1,This is a test,select decimal(8) from sysibm.sysdummy1

Now I got different error on executing above query using the stored procedure code posted above..

A database manager error occurred.[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "is" was found following "value) values 1,This".  Expected tokens may include:  "UNION".  SQLSTATE=42601

0
mahjagAuthor Commented:
I tried to change my insert statement to this for test_query..
insert into ais.test_cs values 1,'This is a test','select decimal(8) from sysibm.sysdummy1'                                                                                                                                                                                                                                  
After execution I got error again.. could you tell me why I got this error since I gave all values..

A database manager error occurred.[IBM][CLI Driver][DB2/LINUXX8664] SQL0117N  The number of values assigned is not the same as the number of specified or implied columns.  SQLSTATE=42802
0
mahjagAuthor Commented:
I even specified the column names in the insert statments and still it failed with the same sql state and sqlerror code posted above..

my test_query has

insert into ais.test_cs (id_value,description,gb_value) values 1,'This is a test','select decimal(8) from sysibm.sysdummy1'  
0
Dave FordSoftware Developer / Database AdministratorCommented:

You need parentheses around the values.

e.g.
insert into ais.test_cs (id_value,description,gb_value)
values (1,'This is a test','select decimal(8) from sysibm.sysdummy1')

HTH,
DaveSlash
0
mahjagAuthor Commented:
I appreciate daveslah for your feedback,, I did try parentheses but I found that if an insert statement has got more than 1 value to insert, 1 value being a number (id_value - serial number 1,2..) and other value being sql statement to execute db2 does not accept this to dynamic execute this statemnet

Can you prove me wrong, I will be happy since I am trying to work on a sensitive time line resticted project where I have to dynamic execute insert statment that might insert more than 1 value, we can even try this simpler like I put an example before -

We can try with 2 column insert, first column id value = the value could be 1 or 2
the second column could be the sql statement - in this case very simple one, I get a more complex sql inreal world but fine to see if this is working
value select 8.000 from sysibm.sysdummy1

if we can execute the above insert statement and get the value inserted to test_cs table then it is considered a great achivement  - I think I will fully reward you with the points - even increse the points as this question deservers more than 125.. thanks a lot for your continued support and I am looking forward to your answers..,

Thanks again..
0
Dave FordSoftware Developer / Database AdministratorCommented:
It seems like we have multiple problems here.

Going back to a previous post, what EXACTLY are the data-typs of the columns in your table?  

Earlier, you said the data-type of gb_value was decimal(22,3). But, it looks like you're still trying to insert a string into it.

Also, sometimes you call the table test_cs, and sometimes you call the table test_query.

Also, AFAIK, if you use the VALUES clause, the actual values need to have parentheses around them.

Also, is the INSERT failing, or is the dynamic SELECT failing?  Let's deal with one at a time.

-- DaveSlash
0
mahjagAuthor Commented:
sorry for all the confusion,,
I have 2 tables in db2 - first table test_query has 1 column called query and I defined varchar(4000)
second insert table I called test_cs and it had 3 columns, for the sake of simplicity I am using 2 columns they are all nulls allowed anyway and so I am only trying to insert 2 values
columns
id_val - integer
gb_val - decimal(22,3)

I dont have problems in creating the insert statment in test_query, I am only having problem executing it from stored procedure,.

HTH
mahjag
0
mahjagAuthor Commented:
I did not mean to confuse you any further., just before getting into the actual problem listed above in my post I am trying a direct insert into test_cs table with 2 values from select and I have problems even doing this..

insert into ais.test_cs (id_value,gb_value) values (select a.numval,a.gbval from ( select 1 as numval,8.000 as gbval from sysibm.sysdummy1) as a)

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0412N  Multiple columns are returned from a subquery that is allowed only
one column.  SQLSTATE=42823

can we first get this working..? I am fine if you want to solve the bigger problem , I am only trying to disect the problem into smaller chunks

0
Dave FordSoftware Developer / Database AdministratorCommented:

Ok, I think I see your first problem. You're trying to use SELECT statements in a VALUES clause.  AFAIK, You can't do that.  You can't put a SELECT statement in a VALUES clause.  Elimiate the word VAUES.  Try this exactly, and let me know if it works:

insert into ais.test_cs (id_value,gb_value)
select a.numval,a.gbval from (
   select 1 as numval,
             8.000 as gbval
   from sysibm.sysdummy1
)

If you want to use VALIES, you could simply do:

insert into ais.test_cs (id_value,gb_value)
values (1,8.00)

HTH,
DaveSlash
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mahjagAuthor Commented:
Thanks Dave!!!! - great first step
I had to add as a to the sql at the end (as it is refrencing a )to make it work...

The next step is to insert this insert statement to the test_query table and execute it from stored procedure.. do you think it is going to work? let me try and tell you now..
0
mahjagAuthor Commented:
BTW what is AFAIK?
0
Dave FordSoftware Developer / Database AdministratorCommented:

AFAIK = "As far as I know"
0
mahjagAuthor Commented:
second step worked.. great thanks

The third step I am going to try is to include a constant value like numval in my actual db2 query, when I say actual db2 query it does not involve sysibm.sysdummy1 table..

Question - how do I select constant value and then the actual sql table in db2..
actual table can be any table in your schema like mahjag.employee table

select 1,emp.ename from mahjag.employee

will this work? if this works to give me both the constant value 1 and ename then I will plug the actual values in my query,.,

Thanks a lot for your help!!
0
Dave FordSoftware Developer / Database AdministratorCommented:

Since you're using "emp" as a column qualifier, you have to define it.

select 1,
          emp.ename
from   mahjag.employee emp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.