Skip to Content
0
Former Member
Sep 21, 2007 at 02:54 PM

Problem with query for Oracle

43 Views

Hello Experts,

I'm tryng to develop my first application for EP (v7 SP12) with NWDS (without NWDI).

This application has to read and write data in the EP DB (oracle v10).

I'm using:

<u>a Dictionary Project</u> (define the DB Tables)

<u>a Java Project</u> (define class as DAO, DBManager etc)

<u>a Library Project</u>

<u>an EJB Project</u>

<u>an EAR Project</u>

With these projects I can deploy a <u>webService</u> in my EP server.

BUT I have some problem with a query that I'm tryng to sent to my DB through a DAO Class called by my WebService.

The query is simple and correct but it does not work...

This is the error message returned (the query id in bold)

(column names: GIORNO, NOMEDITTA, NOMEAREA, NOMESETTORE)

<i>HTTP/1.1 500 Internal Server Error

Connection: close

Server: SAP J2EE Engine/7.00

Content-Type: text/xml; charset=UTF-8

Date: Fri, 21 Sep 2007 14:29:57 GMT

Set-Cookie: <value is hidden>

http://schemas.xmlsoap.org/soap/envelope/" xmlns:xs=" http://www.w3.org/2001/XMLSchema" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" > SOAP-ENV:Client java.sql.SQLException: com.sap.sql.log.OpenSQLException: The SQL statement "SELECT NOMESETTORE, MIN(? - "GIORNO") AS GIORNI FROM SRS_DATEINFORTUNI WHERE NOMEDITTA = ? AND NOMEAREA= ? GROUP BY NOMESETTORE ORDER BY NOMESETTORE" contains the syntax error[s]: - 1:25 - the arithmetic expression >>? - "GIORNO"<< contains a host variable (parameter marker)

The variable '?' is the today date, the difference <b>"(?-GIORNO)"</b> is an int..

Moreover in my DAO class the query is <b>"SELECT NOMESETTORE, MIN(? - GIORNO) AS GIORNI FROM SRS_DATEINFORTUNI WHERE NOMEDITTA = ? AND NOMEAREA= ? GROUP BY NOMESETTORE ORDER BY NOMESETTORE</b>", instead in the error message is reported <b>MIN(? - "GIORNO")</b>...

We have tryed also with alternative query, for example we used <b>"MIN(SYSDATA - GIORNO)"</b> but <b>SYSDATA</b> was interpreted as column name and not found....

Any help???

Best Regards