5793 Limit on the number of host variables in USING modifier exceeded

Database
Highlighted
Enthusiast

5793 Limit on the number of host variables in USING modifier exceeded

I have a macro.  The macro includes a delete statement, then 3 insert statements, each selecting from some tables into a results table.  when i execute the macro i get the error:  5793 Limit on the number of host variables in USING modifier exceeded of 2537.

 

I am not declaring any variables.  I have no statements that have a "USING" clause.  What can i do to fix this?

10 REPLIES 10
Teradata Employee

Re: 5793 Limit on the number of host variables in USING modifier exceeded

Is this error on the EXEC or the CREATE/REPLACE? Which client tool are you using to submit the statement? Can you share the macro definition (or an "anonymized" version)?

Enthusiast

Re: 5793 Limit on the number of host variables in USING modifier exceeded

Thanks Fred. 

The error is on the EXEC.

 

Executing in SQL Assistant 16.20.0.2... provider Teradata.Net 16.20.1.0... Database Teradata 15.10.05.06

 

Macro looks like what you see below... Hopefully i did not mess it up too bad when i genericized it.  I tried to include the parts with the most transformation.  Note the three insert statements are identical, the onlydiffierence is the base input table is different.

FYI, when the 4 statements are run as multiple statements (not in a macro) everything works fine.

 

REPLACE MACRO DLAB_CIO.M_MyMacro

AS ResultsTable

(

delete from ResultsTable;

 

insert into  ResultsTable

select

A.COL1

,B.COL1

,B.COL2

,B.COL3

,A.COL2

,A.COL3

,cast(cast(A.FSCL_YR_NUM as char(4))||TD_SYSFNLIB.lpad(cast(A.FSCL_PRD_IN_YR_NUM as varchar(2)),2,'0') as integer) as FSCL_YR_PRD_NUM

,’DATASET_NAME’

,case when A.COL2 = 'BLAH' then null else 'FOO' end as BLAHFOO

,cast(null as decimal(19,4)) as COL_NOT_NEEDED

,A.MEASURE1

,C.MEASURE2

from INPUT_TABLE A

 

inner join OTHER_TABLE B

on

 

left outer join OTHER_TABLE C

on cast(cast(A.FSCL_YR_NUM as char(4))||cast(A.FSCL_DAY_IN_YR_NUM as varchar (3))  as integer) =

        cast(cast(C.FSCL_YR_NUM + 1 as char(4))||cast(C.FSCL_DAY_IN_YR_NUM as varchar (3)) as integer)

 

where A.FSCL_YR_NUM >= extract(year from current_date) - 2;

 

/****  End First insert  *****/

 

insert into  ResultsTable

select

A.COL1

,B.COL1

,B.COL2

,B.COL3

,A.COL2

,A.COL3

,cast(cast(A.FSCL_YR_NUM as char(4))||TD_SYSFNLIB.lpad(cast(A.FSCL_PRD_IN_YR_NUM as varchar(2)),2,'0') as integer) as FSCL_YR_PRD_NUM

,’DATASET_NAME’

,case when A.COL2 = 'BLAH' then null else 'FOO' end as BLAHFOO

,cast(null as decimal(19,4)) as COL_NOT_NEEDED

,A.MEASURE1

,C.MEASURE2

from INPUT_TABLE_2 A  -- coming from a different table than first insert

 

inner join OTHER_TABLE B

on

 

left outer join OTHER_TABLE C

-- next stement is to get the measure from the same fiscal day in year from previous year 

on cast(cast(A.FSCL_YR_NUM as char(4))||cast(A.FSCL_DAY_IN_YR_NUM as varchar (3))  as integer) =

        cast(cast(C.FSCL_YR_NUM + 1 as char(4))||cast(C.FSCL_DAY_IN_YR_NUM as varchar (3)) as integer)

 

where A.FSCL_YR_NUM >= extract(year from current_date) - 2;

 

/****  End Second insert  *****/

 

insert into  ResultsTable

select

A.COL1

,B.COL1

,B.COL2

,B.COL3

,A.COL2

,A.COL3

,cast(cast(A.FSCL_YR_NUM as char(4))||TD_SYSFNLIB.lpad(cast(A.FSCL_PRD_IN_YR_NUM as varchar(2)),2,'0') as integer) as FSCL_YR_PRD_NUM

,’DATASET_NAME’

,case when A.COL2 = 'BLAH' then null else 'FOO' end as BLAHFOO

,cast(null as decimal(19,4)) as COL_NOT_NEEDED

,A.MEASURE1

,C.MEASURE2

from INPUT_TABLE_3 A  -- coming from a different table than first and second insert

 

inner join OTHER_TABLE B

on

 

left outer join OTHER_TABLE C

on cast(cast(A.FSCL_YR_NUM as char(4))||cast(A.FSCL_DAY_IN_YR_NUM as varchar (3))  as integer) =

        cast(cast(C.FSCL_YR_NUM + 1 as char(4))||cast(C.FSCL_DAY_IN_YR_NUM as varchar (3)) as integer)

 

where A.FSCL_YR_NUM >= extract(year from current_date) - 2;

 

/****  End third insert  *****/

);

Supporter

Re: 5793 Limit on the number of host variables in USING modifier exceeded

The "ResultsTable" between "REPLACE MACRO DLAB_CIO.M_MyMacro AS " and "( delete from ResultsTable;", is incorrect.

This is where parameters get defined, somehow this table reference may have been interpreted incorrectly.

Can you try without it ?

Teradata Frank, Certified Master
Enthusiast

Re: 5793 Limit on the number of host variables in USING modifier exceeded

Yes, sorry, that is just something i accidentally inserted when i replaced values in the SQL statement to make it generic.  That does not exist in my "real" query.  This is the correct beginning:

SyntaxEditor Code Snippet

REPLACE MACRO DLAB_CIO.M_MyMAcro 
AS 
(

delete from ResultsTable;

insert into  ResultsTable
sel...
Teradata Employee

Re: 5793 Limit on the number of host variables in USING modifier exceeded

And the failing statement is just this?

EXEC DLAB_CIO.M_MyMacro;
Enthusiast

Re: 5793 Limit on the number of host variables in USING modifier exceeded

Yes.  I have tried it as both EXEC and Execute (i know they shoud be exactly the same).  it takes about a minute to come back with the error.  And just to state the obvious, the failure is happing before any of the statements are executing, i.e., the initial delete never happens.

Junior Contributor

Re: 5793 Limit on the number of host variables in USING modifier exceeded

Can you check details for that failing EXEC in QryLog/Steps?

 

I once encountered a bug in the parser where the internal source code was bad syntax, you might ask your DBA to run

 

SELECT CreateText FROM dbc.tvm WHERE tvmname ='M_MyMacro';

then cut & paste the SQL statements and try to run them as MultiSTatement.

 

 

Enthusiast

Re: 5793 Limit on the number of host variables in USING modifier exceeded

Sorry, my DBA's ran the SQL you sent (obviously i changed the macro name to the real macro name) and they said nothing was returned.  If nothing jumps out at you about the macro, I guess I will do some "old school" troubleshooting and try commenting out sections to see if i can find the offender.  Weird problem though...  Let me know if you have any other thoughts, like:  In general, is this related to the volume of data I am moving?  Or the funky way i get the corresponding previous year's fiscal day-in-year?  Thanks as always, you guys do a fantastic job.  I'll let you know if i figure anything out by commenting out sections.

Teradata Employee

Re: 5793 Limit on the number of host variables in USING modifier exceeded

If CreateText is over 13000 characters then dbc.tvm CreateTxtOverflow is set to 'C' and you will have to retrieve the text from dbc.TextTbl instead.

SELECT x.TextString as CreateText
FROM dbc.TVM t JOIN dbc.TextTbl x
ON t.TvmID = x.TextID
WHERE t.tvmname ='M_MyMacro'
AND x.TextType='C'
ORDER BY x.LineNo;