SQL Query - Update Value in First Data Table with Multiple Conditions

Hello Fellow UiPath RPA Developers,

Is this possible to implement an SQL Query with the following requirements?

FIRST, return value as NULL in in INTERNAL_DB!report_flag for transactions that is
INTERNAL_DB!batch_process_id = EXTERNAL_DB!batch_process_id AND
INTERNAL_DB!type = BUYLOAD AND EXTERNAL_DB!type = BUYLOAD AND
INTERNAL_DB!flag = SUCCESS AND EXTERNAL_DB!flag = SUCCESS AND
fulfill one of the conditions below:

  1. INTERNAL_DB!ref_key_1 = EXTERNAL_DB!ref_key_1
  2. INTERNAL_DB!ref_key_2 = EXTERNAL_DB!ref_key_2
  3. INTERNAL_DB!trans_date = EXTERNAL_DB!trans_date OR INTERNAL_DB!trans_date = (EXTERNAL_DB!trans_date minus 1 day)
  4. INTERNAL_DB!amount = EXTERNAL_DB!amount

SECOND, return value as “NO MATCH” in INTERNAL_DB!report_flag for transactions that is
INTERNAL_DB!batch_process_id = EXTERNAL_DB!batch_process_id AND
INTERNAL_DB!type = BUYLOAD AND EXTERNAL_DB!type = BUYLOAD AND
INTERNAL_DB!flag = SUCCESS AND EXTERNAL_DB!flag = SUCCESS AND
fulfill one of the conditions below:

  1. INTERNAL_DB!ref_key_1 <> EXTERNAL_DB!ref_key_1
  2. INTERNAL_DB!ref_key_2 <> EXTERNAL_DB!ref_key_2
  3. INTERNAL_DB!trans_date <> EXTERNAL_DB!trans_date OR
    INTERNAL_DB!trans_date <> (EXTERNAL_DB!trans_date minus 1 day)
  4. INTERNAL_DB!amount <> EXTERNAL_DB!amount

ELSE
return INTERNAL!report_flag as NULL

SAMPLE INPUT:

EXPECTED OUTPUT:

Any kind of help would be much appreciated.

Best Regards,
Anthony Jr.

Hi @anthonyjr,

There can be many combinations with below columns.
In that I am asking one of the scenario whether it will exist or not.
If yes then what should be output?

  1. INTERNAL_DB!ref_key_1 <> EXTERNAL_DB!ref_key_1
  2. INTERNAL_DB!ref_key_2 <> EXTERNAL_DB!ref_key_2
  3. INTERNAL_DB!trans_date = EXTERNAL_DB!trans_date OR
    INTERNAL_DB!trans_date = (EXTERNAL_DB!trans_date minus 1 day)
  4. INTERNAL_DB!amount <> EXTERNAL_DB!amount

Hello sir @mshahidakhtar

It will fall under “NO MATCH” return value on INTERNAL_DB!report_flag

Regards,
Anthony Jr.

Hi Anthony,

Please take backup of the table
Please find below query and accordingly update the tablename/columns

update dbo.Internal_DB set report_flag = (case
when dbo.Internal_DB.ref_key_1 <> dbo.External_DB.ref_key_1
OR dbo.Internal_DB.ref_key_2 <> dbo.External_DB.ref_key_2
OR (dbo.Internal_DB.trans_date <> dbo.External_DB.trans_date and dbo.Internal_DB.trans_date <> DATEADD(day, -1, dbo.External_DB.trans_date))
OR dbo.Internal_DB.amount <> dbo.External_DB.amount
then ‘NO MATCH’
else null end)
from dbo.External_DB
where
dbo.Internal_DB.batch_process = dbo.External_DB.batch_process and dbo.Internal_DB.ref_key_1 = dbo.External_DB.ref_key_1
and Upper(Trim(dbo.Internal_DB.type)) = ‘BUYLOAD’ AND
Upper(Trim(dbo.Internal_DB.flag)) = ‘SUCCESS’

1 Like

Hello sir @mshahidakhtar ,

Thank you so much for your solution but I encountered an error when I encoded the query you presented, see the screenshot below.

Do you have any idea how can I fix this?

Regards,
Anthony Jr.

I have couple of doubts:

  1. Is this MS SQL DB?
  2. Any column in this table which has unique value and can be used to join this two table?

Hi Anthony,
I have made a small tweak in the query, can you please check now.

update dbo.Internal_DB set report_flag = (Select case
when dbo.Internal_DB.ref_key_1 <> dbo.External_DB.ref_key_1
OR dbo.Internal_DB.ref_key_2 <> dbo.External_DB.ref_key_2
OR (dbo.Internal_DB.trans_date <> dbo.External_DB.trans_date and dbo.Internal_DB.trans_date <> DATEADD(day, -1, dbo.External_DB.trans_date))
OR dbo.Internal_DB.amount <> dbo.External_DB.amount
then ‘NO MATCH’
else null end
from dbo.External_DB
where
dbo.Internal_DB.batch_process = dbo.External_DB.batch_process and dbo.Internal_DB.ref_key_1 = dbo.External_DB.ref_key_1
and Upper(Trim(dbo.Internal_DB.type)) = ‘BUYLOAD’ AND
Upper(Trim(dbo.Internal_DB.flag)) = ‘SUCCESS’)

image

This could also be the solution:

UPDATE int_cleaned_trans SET report_flag = 'MISSING_EXTERNAL_TRANSFER' 
WHERE batch_process_id = '7701' 
AND type='BUYLOAD' 
AND flag='SUCCESS' 
AND (
ref_key_1 NOT IN 
(SELECT distinct(ref_key_1) FROM ext_cleaned_trans 
WHERE batch_process_id = '7701' 
AND type='BUYLOAD' 
AND flag='SUCCESS') OR 

CONCAT(ref_key_1,ref_key_2) NOT IN 
(SELECT distinct(CONCAT(ref_key_1,ref_key_2)) FROM ext_cleaned_trans 
WHERE batch_process_id = '7701' 
AND type='BUYLOAD' 
AND flag='SUCCESS')  OR

(CONCAT(ref_key_1,ref_key_2,DATE_FORMAT(trans_date,'%Y-%m-%d')) NOT IN 
(SELECT distinct(CONCAT(ref_key_1,ref_key_2,DATE_FORMAT(trans_date,'%Y-%m-%d'))) FROM ext_cleaned_trans 
WHERE batch_process_id = '7701' 
AND type='BUYLOAD' 
AND flag='SUCCESS') AND
CONCAT(ref_key_1,ref_key_2,DATE_FORMAT(trans_date,'%Y-%m-%d')) NOT IN 
(SELECT distinct(CONCAT(ref_key_1,ref_key_2, DATE_FORMAT(DATE_ADD(trans_date, INTERVAL -1 DAY),'%Y-%m-%d'))) FROM ext_cleaned_trans 
WHERE batch_process_id = '7701' 
AND type='BUYLOAD' 
AND flag='SUCCESS')) OR

CONCAT(ref_key_1,ref_key_2, DATE_FORMAT(trans_date,'%Y-%m-%d'), amount) NOT IN 
(SELECT distinct(CONCAT(ref_key_1,ref_key_2, DATE_FORMAT(trans_date,'%Y-%m-%d'), amount)) FROM ext_cleaned_trans 
WHERE batch_process_id = '7701' 
AND type='BUYLOAD' 
AND flag='SUCCESS')
);

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.