Passing date parameter (current month) to tbuild from command line

Tools
Enthusiast

Passing date parameter (current month) to tbuild from command line

Hello,

We are using TPT to extract data from Teradata to Oracle.

The script that we are using needs to extract data only for the current month from the Teradata table .

How can we pass the current month to the script.

We run tbuild from  command line.

The command being used is

tbuild -f <script_to_which_curr_month_is passed>.

14 REPLIES
Teradata Employee

Re: Passing date parameter (current month) to tbuild from command line

TPT itself cannot send data to Oracle, so it is not clear to me from your post how you are getting the data from Teradata to Oracle.

However, as far as passing information into a script, you can use the -u command line option.

In the script you create a "job variable".

On the command line you pass a value pair for the -u option and assign a value to the job variable.

In the script, if you want this current month to be part of the SELECT statement, you could so this:

Let's assume we call the job variable "curr_month".

The SELECT statement would be:

. . . .

VARCHAR SelectStmt = 'SELECT * FROM TABLE <table name> WHERE CURRENT_MONTH = ' | @curr_month | ';'

. . . .

(Something like that. I do not know the syntax of your SELECT statement. You will have to code accordingly.)

You basically have have to break up the string assigned to the SelectStmt attribute into a series of strings with the concatenation and the job variable.

-- SteveF
Enthusiast

Re: Passing date parameter (current month) to tbuild from command line

Hello,

Thanks for the quick response.

So a little bit more clarification here is

1. The statement  (just pasting a small part ) is

ATTRIBUTES

(

VARCHAR  currmonth  = @jobvar_currmonth,

VARCHAR SelectStmt = 'SELECT

     CAST(COUNTER_POST_IND as VARCHAR(2))

    ,CD04008

 FROM   where  CD04008 ='| @curr_month|'  '

)

The script is test.txt

2) I execute it by

tbuild -f test.txt -u 201204

Does this look correct ?

Thanks.

Regards,

brinda

Teradata Employee

Re: Passing date parameter (current month) to tbuild from command line

If your job variable is "curr_month" then the command line would be something like this:

tbuild -f text.txt -u "curr_month=201204"

Please read the documentation on the proper use of the -u command line option.

-- SteveF
Enthusiast

Re: Passing date parameter (current month) to tbuild from command line

Thanks for this information.

Best regards,

brinda

Enthusiast

Re: Passing date parameter (current month) to tbuild from command line

Hello,

I am still not able to get it work .

My statement is

VARCHAR Cd = @Cd ,

ATTRIBUTES

(

VARCHAR PrivateLogName = 'selector_log',

VARCHAR TdpId = 'ABC.DEF4.NET',

VARCHAR UserName = 'User',

VARCHAR UserPassword = 'passwd',

VARCHAR Cd = @Cd ,

VARCHAR SelectStmt = '

SELECT

CAST(CD13005 as VARCHAR(15))

FROM PROD_INT_SL_LPI_VIEWS.LPI_Treasury_Audit

where CD13005 =' | @Cd | ' ,

VARCHAR ReportModeOn

);

I run it this command

tbuild -f LPI_AUDIT.txt.bak  -u "Cd = '1' "

And I get this error

TPT_INFRA: TPT04017: Error: Exception "TPT_INFRA: TPT02026: Error: End of job script encountered inside of a quoted string

" caught during job script file parsing/compilation.

Job script compilation failed.

Job terminated with status 12.

Kindly help.

Thanks

 

 

Enthusiast

Re: Passing date parameter (current month) to tbuild from command line

I have also tried

FROM PROD_INT_SL_LPI_VIEWS.LPI_Treasury_Audit

where CD13005 ' || @Cd || ' ,

Teradata Employee

Re: Passing date parameter (current month) to tbuild from command line

Ok, a couple of things.

First, I mistyped. The concatenation *is* || and not |.

Second, you cannot put VARCHAR Cd = @Cd in the attribute list.

That will not do anything. The operator does not know about "Cd" as an attribute to the operator. You are using job variables, not attributes.

Third, I believe the issue is with the quotes. Whatever you specified on the command line for the value of Cd will be placed into the script. And the value '1' (with single quotes) will be placed as the value in the concatenation. But the single quotes will be interpreted as part of the concatentaiton and not as quotes you need around the value because you need to give the SELECT statement a string.

Therefore, you need to double the single-quote:

tbuild -f LPI_AUDIT.txt.bak  -u "Cd = ''1'' "

-- SteveF
Enthusiast

Re: Passing date parameter (current month) to tbuild from command line

Thanks you for the quick response. But I still face issues and lookl  forward to your quick suggestions.

I have gone throug the documentation as well. And this is what it says

From the Document

--------------------------

For example, if the job script contains the following attribute list:

ATTRIBUTES

(

VARCHAR UserName = @UsrID,

VARCHAR UserPassword = @Pwd

);

you can assign values to the variables “UsrId” and “Pwd” as follows:

tbuild -f daily_job.tpt -u "UsrID = 'John Doe', Pwd = 'ABC123' "

--------------------------

 So  I have done this

1) Still continued with placing Cd in the attribute list (and I know that is something wrong as you have mentioned )

2) My select statement now is

VARCHAR SelectStmt = '

SELECT

CAST(CD13005 as VARCHAR(15))

FROM PROD_INT_SL_LPI_VIEWS.LPI_Treasury_Audit

where CD13005 =' || @Cd ||' ,

3) Run this as

tbuild -f LPI_AUDIT.txt.bak  -u "Cd = ''1'' "

Still I get errors

"

TPT_INFRA: TPT04017: Error: Exception "TPT_INFRA: TPT02026: Error: End of job script encountered inside of a quoted string

" caught during job script file parsing/compilation.

Job script compilation failed.

Job terminated with status 12.

"

Thanks.

best regards,

brinda

Teradata Employee

Re: Passing date parameter (current month) to tbuild from command line

There must be some other syntax problem with the script. Doubling the single-quote was just to get you to supply the correct value to the WHERE clause (the string '1' instead of the numeric value 1).

Check the entire script to make sure you do not have single quotes embedded inside other single quoted strings.

-- SteveF