Hi,
Lordbwithme2.
For your problem, we can have execute , execute immediate. Please let me know the exact syntax once you implemented it.
Thanks and regards,
Raja
Hi raja,
could u please provide me a sample script that uses "EXECUTE" or "EXECUTE IMMEDIATE".?
This is the script that i used.
/* ****************************************************** *********** */
REPLACE PROCEDURE tempcnt()
BEGIN
DECLARE SQL_STRING VARCHAR(4000);
FOR v_cur AS hdcursor CURSOR FOR
SELECT * FROM HARIHAX.PREMHEADER ORDER BY run_date
DO
SET sql_string = 'INSERT INTO temp SELECT ' || V_CUR.SFX || ' as tbl_name, COUNT(*) FROM DB_T_STAG_UNIT_R1_TEST. POLICY_' || v_cur.sfx || ' WHERE
PLCY_CHG_EFF_DT BETWEEN ' || v_cur.from_date || ' AND ' || v_cur.to_date;
CALL dbc.SysExecSQL(:sql_string);
END FOR;
END
/* ****************************************************** ************* */
i need to make a count of records in the table POLICY_MMYY (where MMYY is the suffix stored in HARIHAX.PREMHEADER table).
PREMHEADER table also contains the from_date and to_date as string fields with values in 'YYYY-MM-DD' format.
I tried to cast the from_date & to_date to date format and then pass it to the dynamic SQL string (sql_string).but even then it wasnt working..
when i ran the above procedure, it was loadin only the suffix values to the TEMP table but not the count.
hope i made it clear!!!! :-)
Hi,
I m sorry I dont have a script.Maybe some onlookers have it .
Best of luck
Raja
Did this procedure work for you? what type privileges you had to get for the id that used to execute it?
Kumar,
I was able to compile the proc and run it. But i wasnt getting the desired output as i mentioned in this post.As far as i know, to run dynamic SQL, you need to have a user id for which u hav a certain amount of perm space to create tables/views, macros, procs (i.e USER as a database).
Refer this link below to know more details about running dynamic SQL in teradata stored procedure.
http://www.teradataforum.com/teradata/ 20040811_094022.htm
Thanks,
Dinesh Kalidasan
Hi all,
I have a requirement of passing tables names dynamically to a stored procedure. I have my table names, date fields stored in another table say XYZ.
table XYZ
---------------------------------------------
From_date | To_date | table_nm3
--------------------------------------------
Date1 | date2 | A1
Date3 | date4 | A2
Now i need to store the count of records in table (say A1) that fall between the Date1 and Date2.
insert into temp select count(*) from A1 where A1.dt_column between Date1 and Date2;
OR
To put it in simple terms, i need to count the records for all the table names present in XYZ table with their corresponding from_date and to_date and applied.
Insert into TEMP select count(*) from TABLE_NM3 Where TABLE_NM3.DT_COLUMN Between From_date and To_date;
I tried to use cursors for this. But I am facing a problem when i try to pass the date values from the cursor to the dynamic SQL.
Could someone help me on this.??