Geek Answers Handbook

Is it possible to use GROUP BY with bind variables?

I want to issue a request as follows

select max(col1), f(:1, col2) from t group by f(:1, col2)

where :1is the binding variable. Using PreparedStatementif I say

connection.prepareStatement
  ("select max(col1), f(?, col2) from t group by f(?, col2)")

I get an error from a DBMS complaining that f(?, col2)it is not a GROUP BY clause.

How is this usually resolved in JDBC?

+5
source share
4 answers

I suggest rewriting the statement so that there is only one binding argument. This approach is pretty ugly, but returns the result:

select max(col1) 
     , f_col2
  from (
         select col1
              , f(? ,col2) as f_col2 
           from t
       )
 group
    by f_col2

This rewritten statement refers to only one binding argument, so the DBMS now sees the expressions in the GROUP BY clause and the SELECT list is identical.

NTN

[EDIT]

( , , named bind, Oracle. Perl DBI , Oracle.)

, . (-, .) , , .

, : () ( ), () bind.

GROUP BY SELECT. , , , . ( , , , , , .)

, . , ( ), .

[/EDIT]

, , , . , ( , , . , , , ..)

EDIT ( )

. , , , . , t. , ; , Oracle. ( ) , . TO_NUMBER(?) AS param, TO_DATE(?,'...') AS param, TO_CHAR(?) AS param, , .)

MySQL. (MySQL- ). , hughjass, . , MySQL, , , , sql_mode ONLY_FULL_GROUP_BY. MySQL FROM DUAL)

  SELECT MAX(t.col1)
       , f( v.param ,t.col2)
    FROM t
   CROSS
    JOIN ( SELECT ? AS param FROM DUAL) v
   GROUP
      BY f( v.param ,t.col2)

MadusankaD Oracle JDBC . ( , , .)

+8

, JDBC ( PreparedStatement), :

select max(col1), f(:1, col2) from t group by f(:1, col2)

, JDBC- , , , .

select max(col1), f(*:1*, col2) from t group by f(*:2*, col2)

oracle group by. JDBC bind.

OraclePreparedStatement . , JDBC. bind. .

JDBC Oracle Database 10g, setXXXAtName.

http://docs.oracle.com/cd/E24693_01/java.11203/e16548/apxref.htm#autoId20

+1

?, bind? , ? , :

PreparedStatement ps = con.prepareStatement("SELECT COUNT(*), TO_CHAR(SYSDATE, ?) FROM DUAL GROUP BY TO_CHAR(SYSDATE, ?)");
ps.setString(1, "YYYY");
ps.setString(2, "YYYY");
ps.executeQuery();
0

In the second case, there are actually two variables - you will need to send them both with the same value.

0
source

More articles:


All Articles