Message Mapping

[Former Member]

Hi guys,

I would like to get a date(on the right) which is between 2 dates(Left)

Like that :

DATE between DATE1 and DATE2 .

I would like to do it in the graphical mapping.

[Former Member]

Hi

The best thing is use the UDF and send the actual greater or less date to output in query

OR the best way is write a stored procedure use it in in JDBC adapter

Regards

Chilla

[Former Member]

It's ok now I just needed to change the occurence of DATE (1..2) !

[Former Member]

Hi,

this type of problem can be solved in stored procedure

Regards

Chilla

[Former Member]

Ok thanks Chandra for the tips.

Regards,

Muraiii

[MichalKrawczyk]

hi,

you can do something like this:

create a user defined function and subscract the dates (to get the number of days)

1. data2 - date1 = numer of days

2. then numberofdays/2 = middledays

3. add middledays to date1

there are many ways to get the dates in days

jut check out google

http://www.jguru.com/forums/view.jsp?EID=489372

Regards,

michal

[Former Member]

No actualy what I want is that the date is Higher than the first one and lower than the second one.

I am using a JDBC adapter for AS400. So what I want in mapping is that I get 2 dates(parameters) from R/3 = datestart and dateend and in the mapping I would have

as result all the record from as400 where date > datestart and date <dateend.

[MichalKrawczyk]

hi,

so you don't need any other dates...

why don't you jut create the condition like:

date > datestart and date < dateend ?

use keyelements of compareOperation like LT

from:

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

something like:

<key1>

<col2 compareOperation=”LT”>date1</col2>

<col4 compareOperation=”GT”>date2</col4>

</key1>

Regards,

michal

[Former Member]

Actually I have this

KEY

ClientNumber

Date

CompareOperation

And I would that the date is > date1 and date is < Date2

But I should get it within the same key otherwise it will be a 'OR' and the problem is I can't have twice the same fieldname in a same key....

Like that :

<key1>

<col2 compareOperation=”LT”>date1</col2>

<col2 compareOperation=”GT”>date2</col2>

</key1>

same col...

thanks.

[prabhu_s2]

what exactly u r looking for....req is not much clear. do u want to use it in the graphical mapping? if u want to compare then u might turn up in comparing with a constant or u might need another date for comaprssion. maybe if u can post u exact req we there will be much response

[Former Member]

how would you do this in graphical mapping

Select * from table XX where DATE > start and DATE <END

The field DATE is the same in both comparaison.

What I wanted to do is to put DATE two times in the key but it is not possible ...

[prabhu_s2]

are u looking for jdbc response mapping?...check:

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step where comparrsion opr is used.

[Former Member]

Hi,

From SAP Note 831162 ,see below

The actual implementation depends on the DBMS you are using. This answer will first describe a generic approach to implement this functionality and then provide examples for popular DBMS.

All DBMS offer some kind of functionality to convert one data type into another data type. As the XML document used by the JDBC Receiver is based on character strings, we need to use a conversion function to convert a string into a DBMS-specific date type. This conversion function will be embedded by the JDBC Receiver into the SQL statement sent to the DBMS. In order that the DBMS actually executes the conversion function and does not treat it as a string, we need to make sure that the JDBC Adapter does not quote the date parameter. This can be achieved by setting the hasQuot attribute of the respective date field's XML element to "No".

An example for the Oracle DBMS, where the date conversion function is named TO_DATE:

<DateField hasQuot="No">TO_DATE(&apos;2004-07-20 08:00:00&apos;, &apos;yyyy-mm-dd hh:mi:ss&apos;)</DateField>

As you can see, any occurrence of an apostrophe within the element data needs to be written as "&apos;" in order to yield valid XML.

For the Microsoft SQL Server DBMS, the statement looks as follows:

<DateField hasQuot="No">CONVERT(DATETIME, &apos;2005-01-01 01:23:45&apos;, 120)</DateField>

The third parameter specifies the date format expected by the CONVERT function. For details, refer to the MS SQL Server documentation (Books Online -> Contents -> Transact-SQL Reference -> CAST and CONVERT).

<b>With above example try to do in your business logic</b>

Regards

Chilla

[Former Member]

In this example is comparing the Field to only ONE other Field what I want is to compare the field to TWO different field!

I Want this :

<key1>

<col2 compareOperation=”LT”>date1</col2>

<col2 compareOperation=”GT”>date2</col2>

</key1>

same col but TWO comparaison in the SAME key....

Thanks,

Murai Kun

[prabhu_s2]

follow the below link to understand on how it can be done:

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

[former_member335553]

Hi

Concatanate Date1 + Date +Date2.