Alessandro La Martina wrote:
> I have some trouble with this query:
>
> String qs = "";
>
> qs = qs + " select adt.l_adt_id, ";
> qs = qs + " adt.codice_adt, ";
> qs = qs + " adt.prezzo, ";
> qs = qs + " adt.prezzo_fattura, ";
> qs = qs + " adt.riferimento_fattura, ";
> qs = qs + " max(l_odt.t_consegna) ";
> qs = qs + " from ";
> qs = qs + " l_stato stato, ";
> qs = qs + " l_adt adt, ";
> qs = qs + " l_valutazione_odt valodt, ";
> qs = qs + " l_odt ";
> qs = qs + " where ";
> qs = qs + " l_odt.lsm_id = ? ";
> qs = qs + " and valodt.odt_id = ";
> qs = qs + " l_odt.l_odt_id ";
> qs = qs + " and adt.valutazione_adt_id = ";
> qs = qs + " valodt.valutazione_adt_id ";
> qs = qs + " and adt.trasportatore_id = ? ";
> qs = qs + " and adt.inviato = 1 ";
> qs = qs + " and stato.l_stato_id = adt.stato_corrente_id";
> qs = qs + " and stato.nome = ? ";
> //qs = qs + and '2002-03-19 08:00:00.0' <= ( select
max(odt1.t_consegna)";
> qs = qs + " and ? <= ( select max(odt1.t_consegna)";
> qs = qs + " from ";
> qs = qs + " l_valutazione_odt valodt1, ";
> qs = qs + " l_odt odt1";
> qs = qs + " where ";
> qs = qs + " valodt1.odt_id = ";
> qs = qs + " odt1.l_odt_id ";
> qs = qs + " and adt.valutazione_adt_id = ";
> qs = qs + " valodt1.valutazione_adt_id ) ";
>
> qs = qs + " group by adt.l_adt_id, adt.codice_adt,";
> qs = qs + " adt.prezzo, adt.prezzo_fattura, ";
> qs = qs + " adt.riferimento_fattura";
> qs = qs + " order by 5";
>
>
> PreparedStatement st = conn.prepareStatement(qs);
>
> st.setLong(1, 13 );
> st.setLong(2, 31 );
> st.setString(3, "fatturato" );
> st.setTimestamp(4, new java.sql.Timestamp(new
java.util.Date().getTime()));
>
> ResultSet rs = st.executeQuery();
>
> while (rs.next())
> {
> System.out.println("" + rs.getLong(1));
>
> }
>
>
> odt1.t_consegna is a timestamp.
>
> The application throws this exception:
> com.sap.dbtech.jdbc.exceptions.DatabaseException: SAP DBTech SQL: [-7016]
(at 527) Parameter spec not allowed in this context
>
> I tried to execute the query replacing a constant instead of the fourth
parameter and the query was executed successfully.
>
>
> Can you teel me if there is an error in the jdbc driver or in my code?
The problem is the forth parameter :
and ? <= ( select max(odt1.t_consegna)
If you send just a parameter, the kernel has to have some info about its
type.
Usually, i.e. with 'easy' conditions like ? = colx or something like this
the type
of the other side of the comparison operator is taken as parameter type.
With correlated subqueries (correlated !, not the normal ones)
things are (unfortunately) different and this parameter
does not know which type of data to expect and cries for help / returns
error -7016.
Use a function to tell the kernel which data type to expect, in your case
use timestamp (?) (ok, looks crazy, but will help).
Have a nice weekend
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
