dynamic count(*)

Database
Enthusiast

dynamic count(*)

Hello all,

I'm trying to do a count(*) inside a DBC.SysExecSQL statement.
I'd like to do something like:

CALL DBC.SysExecSQL('select count(*) into ' || ':oNB_LIGNES' || 'FROM' || 'iDBNAME.' || ':iTABLE_NAME'||';);

But I know that SELECT INTO is not allowed in a dynamic query.
I read a solution from this page : http://www.teradata.com/t/go.aspx/index.html?id=117793

So my question is, can I do something like this instead :

CALL DBC.SysExecSQL('insert into tmp_table select count(*) into ' || ':oNB_LIGNES' || 'FROM' || 'iDBNAME.' || ':iTABLE_NAME');

Thanks

7 REPLIES
Enthusiast

Re: dynamic count(*)

Re HI,

I'm gonna precise my demand, it will maybe bring more answers.

Inside a for loop, how to count(*) from a table, knowing that the name of this table is into a variable and so does the name of the database ?

REPLACE PROCEDURE suivi_diis.COUNT_GENERIC()
BEGIN
DECLARE iDBNAME VARCHAR(30);
DECLARE iDOMAINE VARCHAR(30);
DECLARE iTABLE_NAME VARCHAR(30);
DECLARE oNB_LIGNES INTEGER DEFAULT 0;

FOR compteur AS curseur1 CURSOR FOR
select DBNAME as db ,DOMAINE as dom, TABLE_NAME as tab_nam from dwh1.source_table
DO
SET iDBNAME = compteur.db;
SET iTABLE_NAME = compteur.tab_nam;

select count(*) into :oNB_LIGNES from [highlight=#ffff11]iDBNAME.iTABLE_NAME[/highlight];

INSERT INTO dwh1.target_table (DOM, TRM, COD_TAB,NBR_LIGNES)
VALUES(:iDBNAME,:compteur.dom,:iTABLE_NAME,:oNB_LIGNES);

END FOR;
END;

I receive this error SPL1027:E(L14), Missing/Invalid SQL statement'E(3807):Object 'iDBNAME' does not exist.'.

Thank you for your help,
BR
rgs
Enthusiast

Re: dynamic count(*)

Try this:

BEGIN
DECLARE iDBNAME VARCHAR(30);
DECLARE iDOMAINE VARCHAR(30);
DECLARE iTABLE_NAME VARCHAR(30);
DECLARE stmt1 VARCHAR(500);
FOR compteur AS curseur1 CURSOR FOR
select DBNAME as db ,DOMAINE as dom, TABLE_NAME as tab_nam from dwh1.source_table
DO
SET iDBNAME = compteur.db;
SET iTABLE_NAME = compteur.tab_nam;

SET stmt1 = 'INSERT INTO dwh1.target_table ' || 'SELECT ''' || iDBNAME ||
''',''' || compteur.dom || ''',''' || iTABLE_NAME ||
''', count(*) FROM ' || iDBNAME || '.' || iTABLE_NAME || ';';

call DBC.SysExecSQL(stmt1);

END FOR;
END;
Enthusiast

Re: dynamic count(*)

Thank you much for your answer.
It seems to be a great idea.

I cannot test for the moment coz I don't have Dynamic SQL rights on the database I use.
Basically I receive this error :

I will contact my administrator and let you know asap.
See u later
rgs
Enthusiast

Re: dynamic count(*)

You probably missed this piece of information on the dynamic SQL statement from the documentation:

• The CALL DBC.SysExecSQL statement checks whether or not the creating user is also the
immediate owner of the stored procedure and thus has the right to use dynamic SQL.

• No specific privilege is required to use the CALL DBC.SysExecSQL statement

In other words bullet one above means if you want to use the dynamic SQL call statement and assuming you are USER A the procedure has to be created in DATABASE A.

That’s to prevent someone from creating the procedure in another database and getting access to everything in that database via the stored procedure dynamic statement.
Enthusiast

Re: dynamic count(*)

Hi,
Thank you for this precision.
I read the documentation carefully.

What I understood from bullet one is "if you are the creator of the procedure then you can use Dynamic SQL".
What you tell me about bullet one is "if you want to use Dynamic SQL Statement then you have to be the owner/creator of the database".
That's it ?

Anyway i'm the creator of the procedure of course but i'm not the owner of the database. So am I the owner of the procedure ?

BR
rgs
Enthusiast

Re: dynamic count(*)

The owner of the procedure is the database where the procedure was created and not necessarily the creator of the procedure. In other words if you are user A and you create the procedure in database B you are the creator of the procedure but not the owner of it. The owner is database B. User B has the right to get rid of your procedure (if they want) any time since user B owns it, not you, and you can’t take that privilege away from them.

So as is stated, if you want to use dynamic SQL the owner and creator have to be the same database. What you want to do is to set up a user database for creating those sorts of procedures that require the use of the dynamic SQL statement and create those procedures while logged onto that user database. That database would have to have the privileges to the objects the procedures need to access.
Enthusiast

Re: dynamic count(*)

The other not so popular workaround is to create a dummy proc under user A which takes a character string as argument and executes it using dynamic sql... and to give execute permission on this proc to database B... and when the proc under database B wants to execute the dynamic sql, it calls the dummy proc under user A with the necessary sql string as argument ....

yeah... it's no so popular ;)