How to pass comma separated values in select statement?

[PraveenAnekalmat]

Hi everyone


String totalDbString = "ABC,DEF,GHI";
String queryString = "SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS WHERE USER_ID IN ( ? )";
PreparedStatement ps = oracon.prepareStatement( queryString );
ps.setString(1, totalDbString);
ResultSet rs = null;
rs = ps.executeQuery();
while (rs.next()) {
id = rs.getInt(1);
}

The count is coming 0 even it is greater than 0.
I need to perform this,
SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS WHERE USER_ID IN ( 'ABC','DEF','GHI')";
And above statement works fine with giving count greater than 0 .

How can i pass 'ABC','DEF','GHI' in ? as parameter. Please Help.

[sumittyagi]

Hi everyone


String totalDbString = "ABC,DEF,GHI";
String queryString = "SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS WHERE USER_ID IN ( ? )";
PreparedStatement ps = oracon.prepareStatement( queryString );
ps.setString(1, totalDbString);
ResultSet rs = null;
rs = ps.executeQuery();
while (rs.next()) {
id = rs.getInt(1);
}

The count is coming 0 even it is greater than 0.
I need to perform this,
SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS WHERE USER_ID IN ( 'ABC','DEF','GHI')";
And above statement works fine with giving count greater than 0 .

How can i pass 'ABC','DEF','GHI' in ? as parameter. Please Help.

what is the problem in doing it like this.
String totalDbString = "'ABC', 'DEF', 'GHI'";
String queryString = "SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS WHERE USER_ID IN (" + totalDbString + " )";

Actually ? must be replaced by only one value.
actually what it will be doing internally is (if you do it your way):
"SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS WHERE USER_ID IN ( 'ABC,DEF,GHI' )";
because you have passed a single string, and it puts quotes when you call setString, which is why you are not getting the desired result.

[PraveenAnekalmat]

Thanks a lot sumittyagi....It works