SQL_DML Error

[Former Member]

Hi all;

I am in a problem with SQL_DML query in one of the messages

I have a query like <b>update table set field = 'Y' where date >= '$Date_min$' and date <= '$Date_max$'</b> i have taken Date_min and Date_max as key fields and both are of type datetime.

during the mapping in adpter it gives out an error like

<b>Syntax error converting datetime from character string</b>

Please help

Mudit

[Former Member]

Please Ignore my previous message

Regards,

Sudharshan

[Former Member]

Mudit,

check whether u need quotes before place holders in SAP Help.

update table set flag = 'Y' where last_datetime >= '$last_datetime_min$' and last_datetime <= '$last_datetime_max$'

Regards,

Sudharshan

[bhavesh_kantilal]

Mudit,

Can you post the target datatype you have created?

Also, the data key fields should be of type data and they should have the arrtibute hasQuot= 'NO' assigned to them

To understand the need of this better read the Last part of this link,

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

Regards,

Bhavesh

[Former Member]

Hi Bhavesh ;

Thanx for reply.

<i>the data key fields should be of type <b>data</b></i>

What does this mean??

My Target data type looks like this:

<Statement Name>

<Table action="SQL_DML">

<access></access>

<key1>

<last_datetime_min></last_datetime_min>

<last_datetime_max></last_datetime_max>

</key1>

</Table>

</Update1>

[bhavesh_kantilal]

Mudit,

As you are using SQL_DML, there is no need for hasQuot,

This is how your Datatype should be,

[code]

<StatementName6>

<anyName action= “SQL_DML”>

<access>update table set field = 'Y' where date >= $Date_min$ and date <= $Date_max$</access>

<key>

<Date_min>value1</Date_min>

<Date_max>value2</Date_max>

</key>

</anyName >

</StatementName6>[/code]

In this, <Date_min>value1</Date_min> <Date_max>value2</Date_max>

--> the value 1 and value2 should be dates in the format that your Database expects the same.

Regards,

Bhavesh

[Former Member]

Hi Bhavesh;

i tried by giving in this query

update table set flag = 'Y' where last_datetime >= $last_datetime_min$ and last_datetime <= $last_datetime_max$

it gives in an error in the adapter :

Line 1: Incorrect syntax near 'last_datetime_min$'.

[Former Member]

Hi,

The type of the element u are mapping to last_datetime_min should be "date".

similarly for the other one.

U might use DateTrans function for giving proper date format.

Regards,

P.Venkat

[bhavesh_kantilal]

Mudit,

For this , <i>update table set flag = 'Y' where last_datetime >= $last_datetime_min$ and last_datetime <= $last_datetime_max$</i> ,

Are the Elements under your KEY as,

<key>

<last_datetime_min> </last_datetime_min>

<last_datetime_max> </last_datetime_max>

</key>

Regards,

Bhavesh

[Former Member]

yeah they are same

[Former Member]

Hi Venkat;

Yeah these fields are mapped to fields of type <b>datetime</b>

[bhavesh_kantilal]

Mudit,

The best thing to do.

Go to your DB client and execute the same Update Statement manually with the same values in XI (including the date format etc) and see if the update statement executes fine.

if yes, we can check where the problem from XI is, if no, we can always conclude that the Update query itself is incorrect.

Regards,

Bhavesh

[Former Member]

Hi all;

i tried it all .Even checked in the DB Client with the same query and it works fine.

Not sure can we pass date's as processors in the key.

Mudit

[bhavesh_kantilal]

Mudit,

Sure is possible.

Just try this, make the KEY fields of type string.

Regards,

Bhavesh

[Former Member]

The 'Date_min Date_max must to datetime, are defined datetime in the data type ?

Regards,

Sandro