Problem with query for Oracle

[Former Member]

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>

<SOAP-ENV:Envelope xmlns:SOAP-ENV="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:Body><SOAP-ENV:Fault><faultcode>SOAP-ENV:Client</faultcode><faultstring>java.sql.SQLException: com.sap.sql.log.OpenSQLException: The SQL statement <b>"SELECT NOMESETTORE, MIN(? - "GIORNO") AS GIORNI FROM SRS_DATEINFORTUNI WHERE NOMEDITTA = ? AND NOMEAREA= ? GROUP BY NOMESETTORE ORDER BY NOMESETTORE"</b> <u>contains the syntax error[s]: - 1:25 - the arithmetic expression >>? - "GIORNO"<< contains a host variable (parameter marker)</u></faultstring><detail><ns1:getGiorniSettori_com.akhela.giorniSenzaInfortuni.ejb.exception.GiorniSenzaInfortuniException xmlns:ns1='urn:GiorniSenzaInfortuniWSWsd/GiorniSenzaInfortuniWSVi'></ns1:getGiorniSettori_com.akhela.giorniSenzaInfortuni.ejb.exception.GiorniSenzaInfortuniException></detail></SOAP-ENV:Fault></SOAP-ENV:Body></SOAP-ENV:Envelope></i>

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

[Former Member]

I found in the note <a href="https://websmp230.sap-ag.de/sap(bD1pdCZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=649669&nlang=EN&smpsrv=https%3a%2f%2fwebsmp210%2esap-ag%2ede">649669</a> that they use DATEDIFF( a , b , getdate() ), where 'a' and 'b' are two dates...

I tried it in my query but the error was still the same:

<i>SQL syntax error: the token "(" was not expected here</i>

[Former Member]

Hi,

As I can see you're using native JDBC. The problem is, that if you use the default SAP datasource, you've to use Open SQL. If you want to connect to the DB using native JDBC, you've to create a own datasource (type native JDBC) and than you want have problems with your SQL statement. If you want to use the default datasource use OpenSQL (see NWDS help howto work with OpenSQL).

If you need some example, I will post one.

Regards,

Daniel

[Former Member]

Hi Daniel,

what do you suggest me?

Is better to use Native or Open SQL??

How do you know that the function DATEDIFF si Native SQL and not Open SQL??

Anyway will I try to use it with a new datasource using Native SQL...

Thanks and Best Regareds

[Former Member]

Have you tried SYSDATE instead of SYSDATA?

Regards,

Jens

[Former Member]

Thank you for your post Jens!

I tried with <b>MIN(SYSDATE - GIORNO)</b>, and the error message was:

<i>contains the semantics error[s]: - 1:25 - the column >>SYSDATE<< is undefined in the current scope</i>

The same with SYSDATA...

Moreover I asked to my colleague who admin the DataBase to try the query directly to the db, an <b>MIN(<u>SYSDATA</u> - GIORNO)</b> was accepted....

<u>I need just to do a subtraction between two dates</u>!!

I found in hel.sap.com (http://help.sap.com/saphelp_nw70/helpdata/en/d2/f61996bb5e11d2a97100a0c9449261/content.htm) a function that makes a difference between two dates..

So I tried with <b>DATEDIFF(MAX(GIORNO), ?)</b> (where '?' is a date variable).

The error showed was:

<i>SQL syntax error: the token "(" was not expected here</i>

The '(' that gives error is <b>DATEDIFF<u>(</u>MAX(GIORNO), ?)</b>

It is very stange!

[Former Member]

Hi,

How does your Java Source looks like for the SQL Statement.

Regards,

Daniel

[Former Member]

I'm using a PreparedStatement instance.

PreparedStatement pstm = null;

Connection con = null;

ResultSet rSet = null;

...

pstm = con.prepareStatement(...<i><here I put the query></i>...);

pstm.setString(1, <i><here I set a parameter passed into the query></i>);

pstm.setString(2, <i><here I set a parameter passed into the query></i>);

rSet = pstm.executeQuery();

etc etc

[Former Member]

Hi,

How do you want to access you DB (OpenSQL, Native JDBC)?

As the error indicates you use OpenSQL and have syntax errors in your SQL Statement. Have a look at the NWDS Help on the topic SQLJ, there you will find possibilities to work with dynamic paramters (=host expressions in SQLJ).

If you want to work with the SAP DB Schema, you always have to use OpenSQL (and if you use Dictionary Projects, you have to work with the SAP Schema).

Hope that helps a little bit.

Regards,

Daniel

[Former Member]

Hi, I found something about the Host Variable (http://help.sap.com/saphelp_nw70/helpdata/en/ed/dbf8b7823b084f80a6eb7ad43bdbb9/content.htm), there explain that if you want to use an host variable you have to put ':' as prefix..

My problem is that <u>I need to extract the minimum of the subtraction between two dates:</u>

Query <b>MIN(? - GIORNO)</b> --> <i>Error: the arithmetic expression >>? - "GIORNO"<< contains a host variable (parameter marker)</i>

So I tried to use the ':' as indicated in the manual..

<b>MIN:(? - GIORNO)</b> --> - <i>SQL syntax error: the token ":" was not expected here

- expecting LPAREN, found ':'</i>

<b>MIN(:(? - GIORNO))</b> --> <i>- 1:25 - Open SQL syntax error: :PARAMETER not allowed

- 1:26 - SQL syntax error: the token "(" was not expected here

- 1:26 - expecting ID, found '('</i>

Then I tried to avoid the MIN() function and I tried to do just the subtraction:

<b>? - GIORNO</b> --><i> - 1:21 - the arithmetic expression >>? - "GIORNO"<< contains a host variable (parameter marker)</i>

<b>:(? - GIORNO)</b> --> <i>- 1:21 - Open SQL syntax error: :PARAMETER not allowed

- 1:22 - SQL syntax error: the token "(" was not expected here

- 1:22 - expecting ID, found '('</i>

<b>'2007-09-24' - GIORNO</b> --> <i>- 1:34 - SQL syntax error: first argument of operator "-" must be a number, date/time or interval

- 1:43 - SQL syntax error: arguments of operator "-" do not have correct types

- 1:43 - SQL syntax error: derived columns in SELECT list with AS must be values</i>

<b>GIORNO - GIORNO</b> --> <i>- 1:21 - the group by list and the select list are inconsistent: the column >>"GIORNO"<< is neither grouped nor aggregated

- 1:30 - the group by list and the select list are inconsistent: the column >>"GIORNO"<< is neither grouped nor aggregated</i>

Why these parts of query are not accepted???

I don't understand why... I hope you can help me.

Best Regards

Alessandro