filtering duplicates while we reading database table using sender jdbc

[Former Member]

hi all,

I am extracting the data from SQL database table using sender jdbc adapter. in the table more than one record exist for same employee number. i need to send the latest record to the receiver based on date field in the table.

the data in table is like this.

1234 2007-12-01

1234 2007-12-02

1234 2007-12-06

1234 2007-12-12

so i need to send latest record to receiver that is (1234 2007-12-12).

Can anyone please tell me what is the better way to do this.

Kind Regards,

Kiran

[Former Member · Accepted solution]

Option 1:

I recommend to create a view or stored procedure within your database, which returns exactly what you need. Within the where-clause of the view/stored-procedure you are completely free in SQL-coding...

From XI-perspective you can send a simple statement like "select * from <myView or myStoredProcedure>".

Option 2:

If you can not do any developments on the database-system (so you can not create a stored procedure or a view), then configure your JDBC-Adatper with the option: "MessageProtocoll: Native-SQL-String" and create an appropriate SQL-statement within the java-mapping or even in an adapter-module, in a way that the "where-clause" of your SQL-statement restricts the result-set to the appropriate row.

Option 3:

If you really want to do the filtering on XI, then the easiest way is probably a user-defined-function within the graphical mapping, but of course you could also use an adapter-module or java-mapping.

[Former Member]

Hi Kiran,

this is very easy.

Use "<b>select max</b>"!

Regards Mario

[Former Member]

Hi Mario,

Could u pls eloborate ur view and solution on this, i.e, how to use "select max" in the sql query and the complete solution for this.

Eagerly awaiting ur great reply.

Many Thanks in Advance

[aashish_sinha]

Hi,

I guess you can write a query..

Use a query like this to get the last record.

SELECT sortedFIELD FROM

(

SELECT rownum AS z, sortedField

FROM table1

ORDER BY sortedField

)

WHERE z > N-1

AND z < M-1

for last n records, just change the WHERE clause to

WHERE z > N-1

regards

Aashish Sinha

PS : reward points if helpful