Get First Value on New Columns using Expression Builder

[rendyajisoko]

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 .  

[Madhavi_Konda]

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

[rendyajisoko]

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)

[rendyajisoko]

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

[Madhavi_Konda]

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

[rendyajisoko · Accepted solution]

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. 

[alex_thornbury · Accepted solution]

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

• a create_date column for when the row was created
• a modify_date for when the row was last updated

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:

• If the columns are scoped with a NOT NULL constraint:
  • On INSERT, Attunity will log an exception because both columns are NULL
  • On UPDATE, Attunity will also log an exception because one column is NULL
• If the columns are not scoped with a NOT NULL constraint:
  • On INSERT, Attunity will insert the row with both columns as NULL
  • On UPDATE, Attunity will insert the row with one column as NULL

Looks like our options are:

• Complex Global Transformation proposed here
• Attempt to switch to Transactional Apply
• Some other solution