Using a job variable in a TPT script to set a default column value

Tools & Utilities
Enthusiast

Using a job variable in a TPT script to set a default column value

Evening folk,

I have a need to insert a default value for a column in a TPT script.  basically i am trying to do an insert into a table specifying a job variable as one of the values, and i have been unable to do so up to this point.  My table is as follows:

 

CREATE TABLE MyTable (

column0 VARCHAR(16) CHARACTER SET LATIN,

column1 VARCHAR(16) CHARACTER SET LATIN

) NO PRIMARY INDEX;

 

)

 

INSERT INTO MyTable (

column0,

column1

) values (

'test',

@columnValue);

 

where i have in my job variables file:

 

columnValue='success'

 

upon execution i get the following error:

 

TPT10508: RDBMS error 3857: Cannot use value (or macro parameter) to match 'columnValue'.

 

i have also tried defaulting the column value for the column1 in the schema creation as follows:

 

 column1 VARCHAR(16) CHARACTER SET LATIN DEFAULT @columnValue

 

but that failed, as:

  • using no ticks gave me: TPT10508: RDBMS error 3546: Default values may not be parameterized.
  • using a single tick gave me a parse error about adjacent quoted strings must be separated by the concatenation character '||'.
  • using double ticks gave me the value of @columnValue as the column value (not the correct substituted job variable value, the literal string '@columnValue')
  • using triple ticks gave me a parse error about adjacent quoted strings must be separated by the concatenation character '||'.

any suggestions, and as always, thanks in advance.

 

tom

 

2 REPLIES
Senior Apprentice

Re: Using a job variable in a TPT script to set a default column value

Hi Tom,

 

In your TPT script you need to code the SQL statement as a concatenation of basic SQL with the variable name.

 

Something like:

APPLY ('INSERT INTO MyTable (
column0,
column1
) values (
'''test''','||@columnValue||');')

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Using a job variable in a TPT script to set a default column value

thank you Dave, that took care of the issue.  the triple tick concatenation did the trick:

 

APPLY ('INSERT INTO MyTable

( column0, column1 )

values (

'''test''',
'''||@columnValue||'''

);')

 

Again, thanks for the help,

Tom