Removing Duplicates from JDBC Select

[former_member271795]

Hi

I'm building a soap=>PI=JDBC synchronous interdace.

The JDBC select is getting rows from a VIEW on a MSSQL database

Due to the design of the View, my request is returning 4 rows, instead of 1.

Is there a way in mapping to remove the duplicate rows?

thank you

PS

I've tried useing DISTINCT but this only works if there is an ORDER BY in the sql, and there doesn't seem to be a way of putting this into XML SQL

[former_member271795]

Thanks for all the replies

Being new to PI I'm unsure which node to attach the UDF too

My message structure is

<row> 1...Unbounded

<UPRN> 1

<Postcode> 1

do I attach the UDF to the <row> or the <UPRN>.

I've looked at changing my SQL call to include group by

but I cannot see where I could put this in the XML SQL message format

[Former Member]

source filed >sort>UDF-->Target field

in ur case, define which fuled shuld be repeated like UPRN> 1,<Postcode> 1

uprn->sort->UDF->target fild

execution type of UDF is Allvalues of a context.

public void UDF(String[] var1, ResultList result, Container container) throws StreamTransformationException{

ArrayList aList = new ArrayList();

aList.add(var1(0));

result.addValue(var1(0));

for(int i=1; i<var1.length; i++){

if(aList.contains(var1(i)))

continue;

else{

aList.add(var1(i));

result.addValue(var1(i));

}

}

}

[Former Member]

have you seen below thread ,might be it's helpful

Is there a way in mapping to remove the duplicate rows?

[baskar_gopalakrishnan2]

Better practice is to handle this issue using sql query itself. The problem is at the data side. can you please refer the below link?

http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/

[former_member184681]

Hi,

You can also try using GROUP BY instead of ORDER BY in your query.

Hope this helps,

Greg

[MichalKrawczyk]

Hi,

>>>Is there a way in mapping to remove the duplicate rows?

yes of course - just put all of them into a hashmap with one key and and get it back from there as unique values

(so a simple UDF)

>>>I've tried useing DISTINCT but this only works if there is an ORDER BY in the sql, and there doesn't seem to be a way of putting this into XML SQL

you can use native SQL where you can put any SQL statement - have you tried that ?

Regards,

Michal Krawczyk