on 12-19-2014 5:53 AM
hi all ,
we have a requirement to update a custom table during a BODS job execution.
details on the BODS job:
source: csv files
target: oracle table
the dataflow consists of a simple flow from source file format to query transformation to target table.
in the source file format , i have given file name as *.csv to read all files.
in the audit function we are updating the custom table with the values from audit labels,
to fill the fields 'src_row_count' and 'tgt_row_count' of the above custom table.
code used in Dataflow-audit:
sql('TEST_TGT_TABLE','update USER_1.CUSTOM_TABLE_NAME set SRC_ROW_COUNT = {$Count_SRC_FILE}, TRG_ROW_COUNT ={$Count_TARGET_TABLE} where JOB_ID = {$ID} and JOB_RUN_ID = {$RUN_ID}') is NULL
this script is not updating the fields.
but when i hard code values of variable in where clause and execute it, this updates the table. [below script]
sql('TEST_TGT_TABLE','update USER_1.CUSTOM_TABLE_NAME set SRC_ROW_COUNT = {$Count_SRC_FILE}, TRG_ROW_COUNT ={$Count_TARGET_TABLE} where JOB_ID = \'10\' and JOB_RUN_ID = \'85\'') is NULL
so looks like the value of variables is not getting substitued here.
but when i print the variable values of job_id and job_run_id , they provide the desired results.
but i cannot hard code values coz some column values get generated during the job execution time.
can anyone please suggest?
hi all,
thanks for your replies,
i wrote a code in audit rule to update an intermediate table, and after the data flow, i did read data from this temp table and updated the final custom table.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi
Are these variables global variables or local variables? If these are local then you might have an issue.
Raghu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hey Swentha,
it looks like, the quotes are missing. So in the second example you write two single quotes, but you do not do this in the first one.
Maybe the variable types are wrong to.
Regards
Severin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.