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.
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
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.
Thanks for this information.
Best regards,
brinda
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
I have also tried
FROM PROD_INT_SL_LPI_VIEWS.LPI_Treasury_Audit
where CD13005 ' || @Cd || ' ,
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'' "
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
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.
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>.