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

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

Mon, 26 Jan 2026 21:26:37 GMT

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

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

Wed, 13 Oct 2010 17:24:14 GMT

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

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

Fri, 15 Oct 2010 09:44:30 GMT

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

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

Fri, 15 Oct 2010 11:51:12 GMT

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

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

suniljain — Sat, 16 Oct 2010 11:45:21 GMT

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

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

Sat, 16 Oct 2010 12:53:41 GMT

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?

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

suniljain — Sat, 16 Oct 2010 16:09:25 GMT

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

as for example date is like 20100101

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

Thu, 13 Sep 2012 08:45:34 GMT

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