using JPA how to set parameter in native query when I have PIVOT

Question

   q = createQuery( "  select * from ( ( SELECT * FROM ( SELECT dt.route_id , dt.amount , dc.card_type_id FROM DDRC_TRANS\n" +
                "dt , DDRC_CARD dc WHERE ( dt.card_id = dc.id AND dt.insert_time >= ('02-JAN-2000 04:00:00 AM')\n" + 
                "AND dt.insert_time <= ('02-FEB-2050 04:00:00 AM') AND dt.route_id IN ( '1', '3' ) ) ) PIVOT\n" + 
                "( SUM(amount ) FOR card_type_id IN ( 1,2,3 ) ) ) )"
                , clazz);

Ok Everything looks nice! I can run this query and it returns the result too!

But I have found one problem. What about If I need to set parameter? for instance like this

 q = createQuery( "  select * from ( ( SELECT * FROM ( SELECT dt.route_id , dt.amount , dc.card_type_id FROM DDRC_TRANS\n" +
                "dt , DDRC_CARD dc WHERE ( dt.card_id = dc.id AND dt.insert_time >= ('02-JAN-2000 04:00:00 AM')\n" + 
                "AND dt.insert_time <= ('02-FEB-2050 04:00:00 AM') AND dt.route_id IN ( '1', '3' ) ) ) PIVOT\n" + 
                "( SUM(amount ) FOR card_type_id IN ( :param ) ) ) )"
                , clazz);


         List<String> valueList = Arrays.asList("1,2,3");
         q.setParameter("param", valueList);

now here I have that type of error:

java.sql.SQLException: ORA-56900: bind variable is not supported inside pivot|unpivot operation

why? how can I fix this? Is this hibernate BUG?

possible duplicate of [JPQL IN clause: Java-Arrays (or Lists, Sets...)?](http://stackoverflow.com/questions/2772305/jpql-in-clause-java-arrays-or-lists-sets) — Guillermo, Aug 06 '15 at 18:40
Now it is not duplicate. It is another question. Problem is in PIOT — grep, Aug 07 '15 at 08:44

score 1 · Answer 1 · answered Aug 07 '15 at 17:43

The problems looks like ojdbc does not allow to bind variables within PIVOT clause, because it is not supported.

The first query you posted set the values explicit way. In the second, you are indirectly using preparedStatement through the JPA provider. This is why you finally get a query statement that looks like follows which is not supported (note the parametrized values of IN clause)

select * .... PIVOT ( SUM(amount ) FOR card_type_id IN ( ? ) ) ) )

Although it won't work anyway, you must be aware that you are not setting a list of values. In the query above you just set one parameter.

For you native query (assuming that you are creating with EntityManager.createNativeQuery inside your createQuery method),

q = createQuery( "select * .... card_type_id IN ( :param ) ");
List<String> valueList = Arrays.asList("1,2,3");
q.setParameter("param", valueList);

what you are finally generating depends on how the JPA provider replace the :param by the valueList but it will be just one parameter replacement not a list. For example, Hibernate will generate a preparedStatement like this

select * .... card_type_id IN ( ? )

that finally takes next form after set set the valueList parameter,

select * .... card_type_id IN ( [1,2,3] )

What you really should achieve is a preparedStatement like this (note many parameters ?)

select * .... card_type_id IN ( ?,?,? )

For do that the valueList should be as follows:

List<String> valueList = Arrays.asList("1","2","3");

Then the statement will take above prepared from and finally:

select * .... card_type_id IN ( '1','2','3' )

Neil Stockton · Answer 2 · 2015-08-07T11:08:46.347

0

You CANNOT pass a List in to JDBC as a parameter. You have to pass in the individual values and have multiple "?" in the statement. Beyond that, you are down to the rules of the specific JDBC driver you are using and where it accepts parameters to be used. All JDBC drivers restrict where parameters can be applied in one way or another ...

edited Aug 07 '15 at 11:08

answered Aug 07 '15 at 10:59

Neil Stockton

11,383
3
34
29

I can pass list of parameters using Java Persistence API. I have Native SQL which have PIVOT too. – grep Aug 07 '15 at 11:02
2

This is NOT a JPQL query (where you can use IN and a List parameter). This is a NATIVE query (you said it yourself). Native query uses JDBC rules. – Neil Stockton Aug 07 '15 at 11:03
You car use Native query's in JPA too! I have written the sample too! please read my post! – grep Aug 07 '15 at 14:31
1

Please read my answer! I said "native query", that is JPA Native query! You know ... createNativeQuery. JPQL != Native Query, and BOTH are in JPA. NativeQuery semantics are different to JPQL semantics. – Neil Stockton Aug 07 '15 at 14:33

using JPA how to set parameter in native query when I have PIVOT

2 Answers2