Skip to Content
author's profile photo Former Member
Former Member

Standard database function TO_CHAR() usage

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 Channel 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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Feb 12, 2016 at 09:30 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Vadim Klimov

      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

  • Posted on Feb 06, 2016 at 05:20 PM

    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?

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Oct 31, 2019 at 03:18 PM

    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;

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.