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

Help Activities
, , , ,
1

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:

image
image1144×352 12.1 KB

EXPECTED OUTPUT:

image
image1142×164 7.21 KB

Any kind of help would be much appreciated.

Best Regards,
Anthony Jr.

2

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
3

Hello sir @mshahidakhtar

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

Regards,
Anthony Jr.

4

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’

image
image1704×787 83.7 KB

1 Like
5

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.

image
image1088×378 22.3 KB

Do you have any idea how can I fix this?

Regards,
Anthony Jr.

6

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?
7

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
image1559×835 86.8 KB

8

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')
);
9

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