How to delete data before loading new one (resolved)

[erwink]

Hi there

I've a fact build where the datasource query is as follow
SELECT "Id", "InternalOrder", "Value", "Date",
FROM   "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

using UPDATE/INSERT

My problem is that it may happen that in the source a entry is deleted. As I can not detect this deletion I would like to extend this build or create a new one to run

delete from "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

beforehand.

Can I include this delete statement in the Query of the data source. Or ..

Thank you for your help
erwin

[MFGF]

Hi there

I've a fact build where the datasource query is as follow
SELECT "Id", "InternalOrder", "Value", "Date",
FROM   "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

using UPDATE/INSERT

My problem is that it may happen that in the source a entry is deleted. As I can not detect this deletion I would like to extend this build or create a new one to run

delete from "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

beforehand.

Can I include this delete statement in the Query of the data source. Or ..

Thank you for your help
erwin

Hi,

I'm not quite able to understand your logic here. If you run the following SQL before the build:

delete from "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

Then wouldn't the build's query retrieve no rows? You said this is your build's query:

SELECT "Id", "InternalOrder", "Value", "Date",
FROM   "dbo"."NonLaborValues"
WHERE "Date" > _add_months(sysdate(),-1)

The SQL you want to run beforehand would effectively eliminate all rows your build would have read? Is this what you want?

MF.

[erwink]

Oups, was definitively to tired yesterday.

No, what I'd like is to delete the rows in the target database. The update/insert doesn't work because I do not get deletion from the source.
So my idea was to delete the same time window in the target and use APPEND afterwards

Hope it's clearer now

Thank you
erwin

[MFGF]

Oups, was definitively to tired yesterday.

No, what I'd like is to delete the rows in the target database. The update/insert doesn't work because I do not get deletion from the source.
So my idea was to delete the same time window in the target and use APPEND afterwards

Hope it's clearer now

Thank you
erwin

Ah ok - that makes more sense. Obviously I'd advise extreme caution in deleting anything from your data warehouse - is that really what's required, or does your client really want to destroy history like this?

Assuming it's a requirement the client is insisting on, you could run your build from a node in a jobstream, and precede the build node with a SQL node which performs the delete?

Cheers!

MF.

[erwink]

Thank you!!

Was somehow not aware of the SQL node.

The job run's every day to populate latest data. Historical data is just required on last day of every month. So I run a 2nd job separately for that with another target db

APPEND did not make sense for this reason and for the amount of data
TRUNCATE was something I wanted to avoid, in case somebody manipulated the past
UPDATE/INSERT was way to long, so i decided to go for current month -1 upwards

But then got the issue of deleted entries in the source which stayed in the target. Not nice

Thank you again for your help
erwin