Qlik Community

QlikView Connectors

Not applicable

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

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

7 Replies
Not applicable

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

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

Not applicable

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

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

Not applicable

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

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
Honored Contributor

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

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

Not applicable

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

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
Honored Contributor

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

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

as for example date is like 20100101

Not applicable

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

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

Community Browser