Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: February 18, 2019 - 11:45 am UTC

Last updated: February 20, 2019 - 3:32 am UTC

Version: 18.3.0

Viewed 1000+ times

You Asked

Hello, Ask Tom team.

I'm using the query below to load rows to a destination database based on some conditions. After this is done I want to flag those rows in order to exclude them in the next SSIS ETL run.

select t1.invoice_sender,t1.einvoice,t1.invoice_type,t3.INV_SENT_DATE,t3.APPROVALS,t1.LOADED_606
  FROM table1 t1
  LEFT JOIN table2 t2
  ON t1.ID = t2.ID
  INNER JOIN table3 t3
    ON (t3.invoice_sender = t1.invoice_sender AND t3.einvoice = t1.einvoice)
    AND t1.LOADED_606 = 0 --look for the rows not loaded (flagged as 0) in my ETL package
    AND t3.APPROVALS = 0 --lok for accepted approvals
    AND t3.INV_SENT_DATE < TRUNC(SYSDATE)+1 AND t3.INV_SENT_DATE >=
  (CASE  
   WHEN TO_NUMBER(TO_CHAR(SYSDATE,'DD')) <= 15 --If today's date is equal or less than day 15 of current month
   THEN TRUNC(SYSDATE-20,'MM') -- then remove 20 days (which puts me *somewhere* in last month, and then truncate down the 1st of that month)
   ELSE TRUNC(SYSDATE,'MM') --otherwise just truncate down to the start of the current month. 
   END)  --end
    AND (t1.invoice_type NOT IN (32,41,43) OR substr(t2.modified_einvoice,2,2) not in (02,32)); --e-invoice types 32, 41 and 43 and modified einvoices 02 and 32 do not apply


What update statement can I use to flag to null loaded rows to the destination database? t1.LOADED_606 = 0 is the flag column

Thanks in advanced.

and Chris said...

At a complete guess, you can take the results of the query above, returning the primary key of the table you want to update.

And make this a nested subquery in your update's where clause.

e.g.:

update table1
set  processed = ...
where primary_key = ( 
  select table1.primary_key
  from   <your query in the question>
)


But it's not clear to me exactly how this should work without a complete test case. This includes:

- create tables
- insert into data
- the outcome you expect after running the update

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Review

Geraldo Peralta, February 19, 2019 - 2:05 pm UTC

Thanks for the response.

update table1
set processed = ...
where t1.primary_key IN (
select table1.primary_key
from table1 t1
LEFT JOIN table2 t2
ON t1.ID = t2.ID
INNER JOIN table3 t3
ON (t3.invoice_sender = t1.invoice_sender AND t3.einvoice = t1.einvoice)
AND t1.LOADED_606 = 0 --look for the rows not loaded (flagged as 0) in my ETL package
AND t3.APPROVALS = 0 --lok for accepted approvals
AND t3.INV_SENT_DATE < TRUNC(SYSDATE)+1 AND t3.INV_SENT_DATE >=
(CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE,'DD')) <= 15
THEN TRUNC(SYSDATE-20,'MM')
ELSE TRUNC(SYSDATE,'MM')
END)
AND (t1.invoice_type NOT IN (32,41,43) OR substr(t2.modified_einvoice,2,2) not in (02,32));
)

I changed = symbol to IN because could be more than one row to process.

The sub-query, that is used to transport the data, returns 3 rows, the update affects 3 rows.

I set the flagged ones to null once processed to boost performance (recommended by you).

Anything else to have in mind?

Thanks in advanced.
Connor McDonald
February 20, 2019 - 3:32 am UTC

Looks good

Review

Geraldo Peralta, February 28, 2019 - 11:26 pm UTC

Thanks for the help.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.