Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
i want to add a new column 'CREATION_DATE' in target table that will have the very first value where task is running.
example :
at 7pm . because of new row has been added in source table, on target table the value will be '17/05/2020 19.00'
*i use datetime(now,localtime) function
at 8pm, there is an update data in previous row, then the value i want is still '17/05/2020 19.00' , not '17/05/2020 20:00'
is there any way to accomodate this logic? I've tried using ifnull(CREATION_DATE,datetime(now,localtime) ) but return with error. SqlLite error message no such column CREATION_DATE .
weird, still null..
but i found another method. which is on creation_date_dwh column , i create default value sysdate , then remove it on my transformation. case solved. i totally forgot about basic rule oracle can do.. pardon me..
thanks for your attention and support Madhavi, GBU and stay healthy.
This approach appears to have a limitation that is dependent upon the Attunity Task in question to be in Transactional Apply mode instead of Batch Optimized Apply. Per the Setup/User guide on page 51:
"In Batch Optimized Apply mode, if the target table has more columns than the
source table, any values in the extra columns will be replaced with NULL"
We tested a slight variation of this solution in Batch Optimized Apply mode. In our situation we would like to have
Including default value constraints and a BEFORE UPDATE trigger (to update the modify_date column) in the table DDL seemed like it would take care of this. However:
Looks like our options are:
Hi,
Thank you for your enquiry.
Please check the below transformation for your problem.
case $AR_H_OPERATION when 'UPDATE'
then
target_lookup('NO_CACHING','TGT_SYSTEM','TEST_O_S','CURR_DT','ID=:1',$ID)
else
datetime('now','localtime')
end
Where:
TGT_SYSTEM: target_schema_name
TEST_O_S: table_name
CURR_DT:Date column
ID: primary key column
Thanks,
Madhavi
Hi Madhavi,
thanks for you reply
but I still not get the value i want.
i modified your transformation script into this :
case $AR_H_OPERATION
WHEN 'INSERT' THEN DATETIME('NOW','LOCALTIME')
WHEN 'UPDATE' THEN
target_lookup('NO_CACHING','ELLSTG9','MSFXXX','CREATION_DATE_DWH','TABLE_TYPE=:1','TABLE_CODE=:2',$TABLE_TYPE,$TABLE_CODE)
WHEN 'DELETE' THEN
target_lookup('NO_CACHING','ELLSTG9','MSFXXX,'CREATION_DATE_DWH','TABLE_TYPE=:1','TABLE_CODE=:2',$TABLE_TYPE,$TABLE_CODE)
else
DATETIME('NOW','LOCALTIME')
end
Actually I have created a general transformation to add new column named 'CREATION_DATE_DWH' type Datetime(6) with no expression (leave blank / NULL) , this applied to all of my tables. . and also for this table MSFXXX ,i created specific transformation where on 'CREATION_DATE_DWH' column I insert my script above.
I did save and stop TASK, resume processing and reload MSFXXX .
checked my 'CREATION_DATE_DWH' , value still null
tried to insert new row from source database, I got error Trying to reconnect to target database and SQLLite error message unknown (see attachment)
The case --
when condition are
reload ---> value = date reload
insert ---> value = date insert
update/delete ---> value still date insert / date reload ... value not change
my purpose is I want to get the first date where my data has been replicate to my target DB (reload/insert)
please help, i believe Qlik Replicate have the ability to do this
Hi,
There is quote missing near 'MSFXXX in DELETE part. You don't need to keep all the conditions in your transforamtion.
Please use the one that I provided to you initially for your requirement. It will give the current timestamp for the fullload and
for new inserts and target value for updates. You don't really need to keep a condition for DELETEs
Thanks,
Madhavi
weird, still null..
but i found another method. which is on creation_date_dwh column , i create default value sysdate , then remove it on my transformation. case solved. i totally forgot about basic rule oracle can do.. pardon me..
thanks for your attention and support Madhavi, GBU and stay healthy.
This approach appears to have a limitation that is dependent upon the Attunity Task in question to be in Transactional Apply mode instead of Batch Optimized Apply. Per the Setup/User guide on page 51:
"In Batch Optimized Apply mode, if the target table has more columns than the
source table, any values in the extra columns will be replaced with NULL"
We tested a slight variation of this solution in Batch Optimized Apply mode. In our situation we would like to have
Including default value constraints and a BEFORE UPDATE trigger (to update the modify_date column) in the table DDL seemed like it would take care of this. However:
Looks like our options are: