cancel
Showing results for 
Search instead for 
Did you mean: 

BODS : issue in variable usage in audit rule of data flow

former_member186160
Contributor
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

former_member186160
Contributor
0 Kudos

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.

Former Member
0 Kudos

Hi Swetha can you share this job structure with script..with sample job ATL ..i would like to execute  job like this..:D

Thanks in advance

Answers (3)

Answers (3)

former_member200473
Contributor
0 Kudos

Hi Swetha N,

what is the code for $ID and $RunID ?

Regards,

Shiva Sahu

former_member211387
Contributor
0 Kudos

Hi

Are these variables global variables or local variables? If these are local then you might have an issue.

Raghu

former_member186160
Contributor
0 Kudos

hi, these are global variables.

severin_thelen
Contributor
0 Kudos

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