cancel
Showing results for 
Search instead for 
Did you mean: 

Standard database function TO_CHAR() usage

Former Member
0 Kudos

Dear Experts,

I would like to use standard database function TO_CHAR() in order to format TIMESTAMP(6) field in date format only. I want to execute the query below:

SELECT WAREHOUSE, SSCC, MATERIAL, QUANTITY, TO_CHAR( EXPIRYDATE ,'YYYY-MM-DD'), STOCK_STA FROM SAPSTOCKREPORTITEM;

After research I found the note 2072891 2072891 - How-to use database standard functions in query to database using Receiver Communication C... where are mentioned two attributes hasQuot and metadata. I followed the steps but I did't succeed.

My systems are PO 7.4 Java stack only and Oracle 11g.

Has anyone succeed to use the function TO_CHAR?

Best regards,

Rossitsa

Accepted Solutions (0)

Answers (3)

Answers (3)

Matija_Liber
Explorer
0 Kudos

Hi,

You need at the part "TO_CHAR( EXPIRYDATE ,'YYYY-MM-DD')" add the attribute in double quotation marks "EXPIRYDATE". So You dont need change the response and You will not have problem with "<TO_CHAR(TRUNC(EXPIRYDATE))>13-SEP-16</TO_CHAR(TRUNC(EXPIRYDATE))>".

So Your select should look like:

SELECT WAREHOUSE, SSCC, MATERIAL, QUANTITY, TO_CHAR( EXPIRYDATE ,'YYYY-MM-DD') "EXPIRYDATE", STOCK_STA FROM SAPSTOCKREPORTITEM;

Former Member
0 Kudos

Thank you, Vadim, for your reply.

I tried Native SQL. Unfortunately the documentation is not detailed.

When I run it I got an error:

Error processing request, rollback: Error 'java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
' executing service raw2sql with SQL statement '<?xml version="1.0" encoding="UTF-8"?>
<ns1:MT_AWSIR_JDBC_Request_NativeSQL xmlns:ns1="urn://cch/w2/xi/awsir/stock_report_items">SELECT WAREHOUSE, SSCC, MATERIAL, QUANTITY, TO_CHAR(EXPIRYDATE,&apos;YYYY-MM-DD&apos;), STOCK_STA FROM SAPSTOCKREPORTITEM;</ns1:MT_AWSIR_JDBC_Request_NativeSQL>'

Do you have experience with Native SQL to show me a simple working example?

Best regards,

Rossitsa

vadimklimov
Active Contributor
0 Kudos

Hi Rossitsa,

As far as I can see from an error message, the target message which was produced by a mapping, is:


<?xml version="1.0" encoding="UTF-8"?>

<ns1:MT_AWSIR_JDBC_Request_NativeSQL xmlns:ns1="urn://cch/w2/xi/awsir/stock_report_items">SELECT WAREHOUSE, SSCC, MATERIAL, QUANTITY, TO_CHAR(EXPIRYDATE,'YYYY-MM-DD'), STOCK_STA FROM SAPSTOCKREPORTITEM;</ns1:MT_AWSIR_JDBC_Request_NativeSQL>

When you use JDBC adapter with Native SQL String message protocol, the adapter will pass everything that it gets as an input, to the remote database as an SQL statement. Thus, you have to ensure that your mapping does not produce XML message, but generates a String - in other words, the complete outcome of the mapping shall be a String with an SQL statement. In your scenario, you have XML elements like XML document declaration and root element, which cause a problem.

I personally use Java mapping when I need to generate such a raw String and avoid XML declarations and elements generation (I couldn't achieve this with graphical mapping). In the mapping program, you will need to parse the source message in order to retrieve necessary data for an SQL statement construction, and then pass the constructed SQL statement string to an output stream of a target message payload.


TransformationOutput.getOutputPayload().getOutputStream().write(stmt.getBytes());

where "stmt" is the variable (for example, String) that only contains an SQL statement which you want to pass to a remote database.

Regards,

Vadim

Former Member
0 Kudos

Hi Vadim,

I raised incident to SAP unfortunately no positive feedback till now.

Meanwhile i tried the NativeSQL solution with Java.  There is an issue with JDBC driver and apostrophe. At the moment I use work around with TRUNC - SELECT WAREHOUSE, SSCC, MATERIAL, QUANTITY, TO_CHAR(TRUNC(EXPIRYDATE)), STOCK_STA FROM SAPSTOCKREPORTITEM. Now I need to redesign the reponse because it is returning in this way:

Exiting method with <?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE resultset
[
<!ELEMENT resultset (row*)>
<!ELEMENT row (WAREHOUSE, SSCC, MATERIAL, QUANTITY, TO_CHAR(TRUNC(EXPIRYDATE)), STOCK_STA)*>
<!ELEMENT WAREHOUSE (#PCDATA)>
<!ELEMENT SSCC (#PCDATA)>
<!ELEMENT MATERIAL (#PCDATA)>
<!ELEMENT QUANTITY (#PCDATA)>
<!ELEMENT TO_CHAR(TRUNC(EXPIRYDATE)) (#PCDATA)>
<!ELEMENT STOCK_STA (#PCDATA)>
]>
<resultset>
<row>
<WAREHOUSE>193</WAREHOUSE>
<SSCC>250267915750863398</SSCC>
<MATERIAL>1606901</MATERIAL>
<QUANTITY>60</QUANTITY>
<TO_CHAR(TRUNC(EXPIRYDATE))>13-SEP-16</TO_CHAR(TRUNC(EXPIRYDATE))>
<STOCK_STA>UN</STOCK_STA>
</row>


Best regards,

Rossy

vadimklimov
Active Contributor
0 Kudos

Hi Rossitsa,

We used this functionality (system - PO 7.4 SP09) and it worked well.

Can you please provide information regarding:

  • If you execute the SQL query in question directly in the remote database, does it get completed successfully or do you get any error?
  • What is the error you get in PO?

Can you please provide screenshots of a message mapping (mapping to a target field representing the column accessed using TO_CHAR() function) and configuration of a JDBC communication channel?

It would also be helpful if you temporarily enable logging of SQL statements in your JDBC channel and capture exact SQL queries which are sent to the remote database (refer to usage of the additional parameter "logSQLStatement" described in a SAP Note 801367) to ensure that the SQL query is passed to a database as expected and is not corrupted.

Regards,

Vadim

P.S. Threads and raised by you recently seem to address exactly the same issue. Would you mind keeping just one of these threads open and closing others so that SCN members can help you with their answers consistently in one single discussion?

Former Member
0 Kudos

Hi Vadim,

I am executing the query successfully in Oracle. I don't get any error in PO but the log("logSQLStatement") shows SELECT WAREHOUSE, SSCC, MATERIAL, QUANTITY, EXPIRYDATE, STOCK_STA FROM SAPSTOCKREPORTITEM.

My channel is JDBC receiver.

Message Mapping:

- Constant is mapped to the field EXPIRYDATE. Its value is "TO_CHAR(EXPIRYDATE,'YYYY-MM-DD')"

- Constant is mapped to the attribute hasQuot with value "No"

- Constant is mapped to the attribute metadata with value "TO_CHAR~~NVARCHAR~~CHAR".

Regards,

Rossitsa

Mapping

Channel

SQL Log

vadimklimov
Active Contributor
0 Kudos

Hello Rossitsa,

Configuration provided on screenshots seems to be fine. I'd suggest raising an incident to SAP for this. Meanwhile, as a workaround, you may want to switch from XML SQL format to Native SQL format and construct the required SQL query string manually. Even though XML SQL is generally a preferred format when configuring JDBC scenarios, in this particular case Native SQL looks like a reasonable temporary workaround.

Regards,

Vadim