Talend community forum / Delete with range

martinf · 2012-01-15 20:56:33

Hi

I want to delete row in a database from csv where columnA + or - 0.015.

Easy if the number is exactly the same (tmap, output, delete in database).
But, how and where I do this range (value in csv: everything row database that, for a specified field: value from csv + or - 0.015 = delete theses data...)?

Thank

MartinF

pedro · 2012-01-16 03:05:15

Hi MartinF

You can use tXXXDBRow to execute sql statement.
tFileinputDelimited--main->tXXXDBRow

Code:

"delete from TableName where FieldName <"+(row1.columnA+0.015)+" and FieldName >"+(row1.columnA-0.015)

Regards,
Pedro

karthikj18 · 2012-01-16 08:14:14

pedro wrote:
Hi MartinF

You can use tXXXDBRow to execute sql statement.
tFileinputDelimited--main->tXXXDBRow
Code:
"delete from TableName where FieldName <"+(row1.columnA+0.015)+" and FieldName >"+(row1.columnA-0.015)
Regards,
Pedro

Hi,
I think the condition should be 'or'.
like - > "delete from TableName where FieldName <"+(row1.columnA+0.015)+" or FieldName >"+(row1.columnA-0.015)"
please correct if I am wrong..

pedro · 2012-01-16 08:19:33

Hi

If you put 'or' here, you will delete all records.
It must be a closure range.

Regards,
Pedro

karthikj18 · 2012-01-16 08:22:56

hi,
yes that's correct. thanks for the correction.

martinf · 2012-01-16 20:03:53

Thanks
But I still have an error:
"ERROR: missing FROM-clause entry for table "row1"ERROR: current transaction is aborted"

Row1 is name of Main
(print screen attached)

MartinF

Uploaded Images

pedro · 2012-01-17 02:46:29

Hi MartinF

The first error is "missing FROM-clause entry".
So please show me the sql of tPostgresqlRow.
There must be something wrong with it.

Regards,
Pedro

martinf · 2012-01-17 12:02:50

Hi

The query is

"delete from nbfsc.groomers_data where
Longitude < "+(row1.Longitude+0.015)+" and Longitude > "+(row1.Longitude-0.015)+" and
Latitude < "+(row1.Latitude+0.015)+" and Latitude > +(row1.Latitude-0.015)"

MartinF

martinf · 2012-01-20 14:02:31

Hi

The query is

"delete from nbfsc.groomers_data where
Longitude < "+(row1.Longitude+0.015)+" and Longitude > "+(row1.Longitude-0.015)+" and
Latitude < "+(row1.Latitude+0.015)+" and Latitude > +(row1.Latitude-0.015)"

MartinF

Announcement

Unanswered posts

#1 2012-01-15 20:56:33

Delete with range

#2 2012-01-16 03:05:15

Re: Delete with range

Code:

#3 2012-01-16 08:14:14

Re: Delete with range

pedro wrote:

Code:

#4 2012-01-16 08:19:33

Re: Delete with range

#5 2012-01-16 08:22:56

Re: Delete with range

#6 2012-01-16 20:03:53

Re: Delete with range

#7 2012-01-17 02:46:29

Re: Delete with range

#8 2012-01-17 12:02:50

Re: Delete with range

#9 2012-01-20 14:02:31

Re: Delete with range

Board footer