How to convert a type in where clause using SAP SQLConnector?

[#1]

Hello,

I want to load some data from the LIKP table in SAP using the SAP SQLConnector. Furthermore I want to select the data for a specific period, but the date field in the table is not declared as a integer. If you look in the Oracle DBMS you will see, the field is declared as varchar.

My Question:

How can I convert the field in the WHERE clause that I can do a comparison to my selection criterion?

Here an example (LFDAT still has to be converted in a way I don't know):

LIKP:
LOAD
 VBELN as delivery_number,
 LIFNR as supplier_number;
SQL SELECT
 VBELN,
 LIFNR
FROM LIKP
WHERE LFDAT >= 20101013;

Regards,
Jonas

[#2]

Hi Jonas
I use this method because as you say the dates in SAP are usually kept as varchar. Don't forget to put the date in ' ' .
Load * ;
SQL Select VBELN, LIFNR
from LIKP
WHERE ( LFDAT => '20000101' ) ;

and if you want other clauses you can use the like method on characteristic values.

AND (MATERIAL LIKE 'ABCD%' OR MATERIAL LIKE 'WXYZ%') ;

If you want to convert the date format you can use this.
DATE([LFDAT],'DD/MM/YYYY') as Date // This will now be in correct date format when loaded into an App.
or
LEFT([LFDAT],4) AS Date_Year // This will now be in a number format when loaded into an App e.g. 2000

Best Regards
Greenee

[#3]

Hi Greenee,

I know that I have to put the date in ' '. But I'm looking for a solution to convert the date to number in the where clause like the oracle command to_number. Using this I could compare the field to a number, e.g. '...where to_number(LFDAT) >= 20101015'.

It is a good hint to use LIKE to compare a part of the string. It is useful if you need a whole year or month but it is not the best solution to select several months or a special date range.

Thank you for your help but maybe someone still has another idea to solve this problem, maybe you.

Best Regards,
Jonas

[#4]

Hi Jonas,

I tried converting the date field in char data type as well. However, it appears that Connector does'nt provide that flexibility.

hope if someone has an answer...

[suniljain]

During extraction you have to follow the syntax of sap data stored in sap table.

[#6]

But what does it mean? Is there a SAP command I could use to convert the varchar into a number? Or does it mean that it is not possible to convert any data from SAP tables during extraction?

[suniljain]

during extraction we have to follow actual format of data stored in sap r/3.

as for example date is like 20100101

[#8]

Hello,

You can also use a variable :

LIKP:

LOAD

VBELN as delivery_number,

LIFNR as supplier_number;

SQL SELECT

VBELN,

LIFNR

FROM LIKP

WHERE LFDAT >= '$(vStartDate)';

The variable has the value like 20120101

Regards,

Chris