Runnig multiple queries through MS Excel uusing ODBC DSN

Connectivity
N/A

Runnig multiple queries through MS Excel uusing ODBC DSN

 Hello,

I need to run the below queries using a single Connection Wizard, but it gies error :

Only an ET or null statement is legal after a DDL Statement.

Please advice...it's urgent!!!

Query:

CREATE VOLATILE TABLE profile_info

(

ProfileName VARCHAR(128)

)ON COMMIT PRESERVE ROWS;

INS profile_info VALUES('PROF_BO_USERS');

INS profile_info VALUES('PROF_DBA');

INS profile_info VALUES('PROF_LOADCDR_USERS');

INS profile_info VALUES('PROF_LOADNCDR_USERS');

INS profile_info VALUES('PROF_LOW_BUSINESSUSERS');

INS profile_info VALUES('PROF_POWER_BUSINESSUSERS');

INS profile_info VALUES('PROF_SAS_USERS');

CREATE VOLATILE TABLE hour_info

(

"Hour" INTEGER

)ON COMMIT PRESERVE ROWS;

INS hour_info VALUES(0);INS hour_info VALUES(1);INS hour_info VALUES(2);INS hour_info VALUES(3);

INS hour_info VALUES(4);INS hour_info VALUES(5);INS hour_info VALUES(6);INS hour_info VALUES(7);

INS hour_info VALUES(8);INS hour_info VALUES(9);INS hour_info VALUES(10);INS hour_info VALUES(11);

INS hour_info VALUES(12);INS hour_info VALUES(13);INS hour_info VALUES(14);INS hour_info VALUES(15);

INS hour_info VALUES(16);INS hour_info VALUES(17);INS hour_info VALUES(18);INS hour_info VALUES(19);

INS hour_info VALUES(20);INS hour_info VALUES(21);INS hour_info VALUES(22);INS hour_info VALUES(23);

LOCKING ROW FOR ACCESS

SEL m.ProfileName

,m."Hour"

,COALESCE(d.CPU,0) CPU

FROM

(SEL ProfileName

,"Hour"

FROM profile_info,hour_info

) m

LEFT OUTER JOIN

(

SELECT

p.ProfileNameI ProfileName

,EXTRACT(HOUR FROM a.starttime) "Hour"

,SUM(a.AmpCpuTime) CPU

FROM dbc.DBQLogTbl a

,dbc.profiles p

WHERE a.ProfileId = p.ProfileId

AND CAST(a.starttime AS DATE) = DATE - 1

AND p.ProfileNameI IN ('PROF_BO_USERS','PROF_DBA','PROF_LOADCDR_USERS',

'PROF_LOADNCDR_USERS','PROF_LOW_BUSINESSUSERS','PROF_POWER_BUSINESSUSERS',

'PROF_SAS_USERS')

GROUP BY 1,2) d

ON m.ProfileName = d.ProfileName

AND m."Hour" = d."Hour"

ORDER BY 1,2;

3 REPLIES
Teradata Employee

Re: Runnig multiple queries through MS Excel uusing ODBC DSN

In VBA code you can issue multiple requests on the same connection, but the wizard expects only a single data-returning SQL statement. It is submitting all the entered SQL above as one multi-statement request, which is invalid.

Ahead of time, you could create and load permanent tables to support this query, or probably even create global temporary tables and include both the INSERTs to populate instances of the GTT and the SELECT in the SQL passed to the wizard. But not CREATE.

Junior Supporter

Re: Runnig multiple queries through MS Excel uusing ODBC DSN

Hi Fred,

I am also facing a problem while executing sql through excel. I have a query that uses pdcrinfo dbql views- dbqllogtbl_hst and dbqlsqltbl_hst. I have just one select statement. When i run this query through excel, it doesn't give me any error, however no rows are returned.

When i run the same query in sql assistant, it gives me 12 rows of o/p.

There is no access issue, as i have checked them all. IS is that we need to do some other settings to use dbql views. When i use the same macro (just call another query in the same macro) to run another query, it gives me results. Only this query doesn't give results.

When i checked the dbql tables for my query, it shows errorcode=0 and numofrows returned=0

Thanks !

Junior Supporter

Re: Runnig multiple queries through MS Excel uusing ODBC DSN

Hi Everyone,

Could someone look into this

-Thanks