Doubt in query statement in receiver jdbc

[former_member187437]

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

[moorthy · Accepted solution]

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

[Former Member · Accepted solution]

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

[former_member187437]

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>

[Former Member]

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

[former_member187437]

Thanks Sudharshan.

It works if i give date field without quotes.

But I am getting other problem

1. It doesnt work if i give the Comparison operator in the compareOperation attribute.i.e no rows are slected in such a case. but it works if i give theoperator directly in the access String

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

and leave the CompareOperation attribute blank.

Anyway, it doesnt matter much

2. Another thing is it takes only the first condition in the where clause. Doesnt consider the date constraint.

Any suggestions?

[prabhu_s2]

sorry of the incorrect post.

Deleted !!!

Message was edited by:

Prabhu S

Message was edited by:

Prabhu S

[former_member187437]

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.