Skip to Content

Doubt in query statement in receiver jdbc

HI

Again a doubt in XML format for receiver jdbc.

I want to execute a query

select sum(col1) sum(col2) from tablename where startdate <= tdate.

My payload message looks like this

<AggregateFn action="SQL_QUERY">

<access>SELECT sum(Weight) FROM KmFuelTrace WHERE resourceno = '$resno$' AND startdate = '$sdate1$'</access>

- <key>

<resno compareOperation="EQ">CHILLED_02</resno>

<sdate1 compareOperation="LT">06/08/2007</sdate1>

</key>

</AggregateFn>

I am getting a error "Data type mismatch in criteria expression" .

I have defined the datatypes correctly with DateTrans function . I think its the problem with access statement.

How to give the selection criteria for non-text fields. (can i use same quotes and dollar combination for date field too) . Or is there any other mistake in the xml structure

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Nov 14, 2007 at 05:34 AM

    Hi,

    Hope you have gone thru these structures:

    http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

    Your jdbc receiver structure should be exactly similar in format.

    Rgds,

    Moorthy

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      > <AggregateFn action="SQL_QUERY">

      > <access><b>SELECT sum(Weight) FROM KmFuelTrace WHERE

      > resourceno = '$resno$' AND startdate =

      > '$sdate1$'</b></access>

      > <key>

      > <resno compareOperation="EQ">CHILLED_02</resno>

      > <sdate1 compareOperation="LT">06/08/2007</sdate1>

      > </key>

      > </AggregateFn>

      Did you define coloumn Weight in your structure?? from where this parameter is coming?

      Also check the JDBC receiver message format(from the link sent by Krishna)

  • Posted on Nov 14, 2007 at 05:53 AM

    Yes. I checked with the xml structure given in the link too.

    But its not given how to specify conditions based on non-text fields in the where clause.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 14, 2007 at 08:13 AM

    Aarthi,

    Try the following

    <AggregateFn action="SQL_QUERY">

    <access>SELECT sum(Weight) FROM KmFuelTrace WHERE resourceno = '$resno$' AND startdate = $sdate1$</access>

    - <key>

    <resno compareOperation="EQ">CHILLED_02</resno>

    <sdate1 compareOperation="LT">06/08/2007</sdate1>

    </key>

    </AggregateFn>

    Also check what us the date format that database uses and give the similar format.

    Regards,

    Sudharshan N A

    Message was edited by:

    Sudharshan Aravamudan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 21, 2007 at 12:12 PM

    Hi all

    The problem is solved now. its the error in sql statement. Date should be given within a pair of # symbol.

    ie

    <access>SELECT sum(Weight) FROM KmFuelTrace WHERE resourceno = '$resno$' AND startdate =#$sdate1$#</access>

    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.