Replacing a default value using Global transformation

[desmondchew]

I am replication from Oracle 11.2.0.4 to target MySQL 8.0.30

The source table has some default values. I would like to use global transformation to replicate default values to target table. When my source table has column "STATUS" default value='OPEN', the target DB status column should be having values 'OPEN' as well.

replace($name,'OPEN','OPEN')

I hit into error as attached screenshot.

My global transformation define as

Global rules --> new rule --> replace column values

Can help please as I am not familar with the function.

Thank you.
Desmond

[OritA]

Hi Desmond, 

In general if the setting of the default value was added to the table before the task started to run then the value will be replicated properly to the target without the need to add any transformation rule. 
As for the error that you get after defining the transformation rule,  if you still need to sovle it please open a case and provide the task diagnostic package so we can see the details of the rule and the log with the error and troubleshoot the cause of the problem. 

Thansk & Regards,

Orit

[desmondchew]

Hi,

The default values are not supported in Qlik. I created a new table and reload the table. It doesn't replicate default values.

Please let me know how to workaround.

[Heinvandenheuvel · Accepted solution]

>> I created a new table and reload the table. It doesn't replicate default values.

Please clarify. 

Does it not replicate the default values ATTRIBUTE to target? That is correct, working as designed. Perhaps not working as desired by yourself but that's how it is and I happen to agree with how it works today. Replicate is NOT a DB copy tool it is a DATA replication tool.  Attributes are not data.

Or does Replicate not move data values which where the result of apply a default value constraint to the target? That would be a bug.

Now back to the original question in this topic. You obfuscated some of the picture , which does not help much, what's so special about a column name ?!?! In the example provided the transformation provided uses the value "$name". That will 'test' fine, but that surely is not a valid column name is it? Should that not be $xxxx_STATUS?  And what do you believe replacing 'OPEN' with 'OPEN' could possibly achieve?

[Heinvandenheuvel]

I tried the provided transformation rule under Replicate V2022.5.0.499 for an Oracle to Oracle task for a column with default value on the source.

It gave me a very clear, and expected error, 

>>> [TASK_MANAGER ]W: Table 'ATT_USER'.'TEST' (subtask 0 thread 1) is suspended. Failed to init column calculation expression 'replace...

and a bit further down:

>>> [TRANSFORMATION ]E: Wrong column name 'name' in expression [1022215]

Once I fixed the name to the actual column name it worked, verified by make the replace use a different search and replace fixed strings. Below is the JSON for my test. That sub_action RENAME feels wrong. 

Note, if one wanted to used a wildcard of sorts for the column match, there would be a challenge. The replace function needs the real column name. It cannot  use a variable like the meta data $AR_M_SOURCE_COLUMN_NAME as input for the value of the source string to replace in.

If you want further help,  you'll need to provide the actual Replicate version tested; the actual JSON for the global transformation attempted; and the relevant error line chunks from the reptask log. No pixels but Text! See my examples here.

Mind you, I still think this would be a pointless exercise as the input the Replicate is the column with the defaulted value already in place so there is nothing to replace is there?

Hein

	}, {
		"action":	"REPLACE_COLUMN_VALUE",
		"name":	"Replace column value 1",
		"column":	{
			"where_column_name":	"TXT",
			"new_data_type":	"kAR_DATA_TYPE_STR",
			"new_length":	0,
			"computation_expression":	"replace($TXT,'aap','noot')",
			"where_sub_data_type":	"KAR_SUB_DATA_TYPE_UNSPECIFIED",
			"new_sub_data_type":	"KAR_SUB_DATA_TYPE_UNSPECIFIED"
		},
		"sub_action":	"RENAME"
	}

[desmondchew]

Hi,

I am using version May 2022 (2022.5.0.395). Yes there is error with my replace function. My objective is to replace null values to default values in the target database MySQL.

I am using this parameter

replace('$AR_M_MODIFIED_COLUMN_DATA','null','OPEN')

Basically, my workaround now is to use Qlik to replicate from source to target. Then manually run update SQL in the target database and apply the default value through DDL.

update targetdb.table1 set col1='N' where col1 is null;  (By default col1 will be null after replicated)

alter targetdb.table1 ALTER col1 set default ='N';

This is what I intend to achieve.

Thanks
Desmond

[Heinvandenheuvel]

>> I am using this parameter:    replace('$AR_M_MODIFIED_COLUMN_DATA','null','OPEN')

Really?! 

The first argument to the replace function is a source string and you provided the STRING '$AR_M_MODIFIED_COLUMN_DATA' instead of a variable $AR_M_MODIFIED_COLUMN_DATA. So that's never going to work as shown. I did test that variable, no quotes around it, and it also works for me.

The second argument in the example is also just a string 'null'. Is that the intention.  As written it is NOT a NULL  test and regardless I would not expect a NULL to come through because if the field value is inserted as NULL then Oracle will have replaced it with the default value right.

You can get a NULL in the source column with UPDATE. For an update action Oracle does not use the default clause. Your suggestion solution, if it can be made to work at all, would break that option and the target would be different from the source.

Just use your scripts to add a default constraint on the target, if you think you really need it, and be happy?!

Hein.