hi guys..

i have a problem here..hope someone could help me
i have to create a table consist of 4 column.
CREATE TABLE "TABLE1"
("DMS_DT" DATE NOT NULL,
"TBLNAME" VARCHAR(10) NOT NULL,
"REC_LD_TS" TIMESTAMP NOT NULL,
"REC_COUNT" INTEGER NOT NULL,
)
IN EDWOWN

my question is how can i coded in DB2 for all req. below;
*for DMS_DT and REC_LD_TS data must be taken from TABLE2.
*for REC_COUNT, data must be computed base on TABLE2 total count group by the DMS_DT
*for TBLNAME it must return 'TABLE2'. logically i could just hard coded the table name as parameter.but how could i achieve that in DB2

hope some one could help me

How about this?
INSERT INTO table1 SELECT dms_dt , 'TABLE2' , ... , COUNT(*) FROM table2 GROUP BY dms_dt;

Some issues:
1) Which REC_LD_TS should take for a DMS_DT?
2) I like to remove double quotations for each object names.
If that were removed, the object names would be recorded in upper case in DB2 system catalog tables/views.

INSERT INTO edwown.TABLE1
(
DMS_DT, REC_LD_TS, REC_COUNT,*TBLNAME
)
SELECT
DMS_DT, REC_LD_TS, count (*)
FROM edwown.TABLE2
group by DMS_DT

it might be somthing like this.
*how am i going to retrieve the TBLNAME info? (most tricky part)

Why don't you want to copy the table name from FROM clause?

If you want to execute the insert statement in a procedure, you can use dynamic SQL(EXECUTE IMMEDIATE statement).
And you can use same parameter(or SQL variable) for a literal in SELECT list and a table name in FROM clause to construct a SQL text to be executed.

finally manage to get it..so simple


INSERT INTO edwown.table1
(
DMS_DT, REC_LD_TS, REC_COUNT, TBLNAME
)
SELECT
DMS_DT, REC_LD_TS, count (*) as REC_COUNT, (VALUES 'table2') as TBLNAME
FROM edwown.table2
GROUP BY DMS_DT, REC_LD_TS

UPPER('table2') will return same value(i.e. 'TABLE2').

Did you required GROUP BY DMS_DT(not GROUP BY DMS_DT, REC_LD_TS)?