topic Re: SQL Function while fetching data from SAP in QlikView

SQL Function while fetching data from SAP

mohsinqlik — Mon, 26 Jan 2026 21:26:37 GMT

Hi Everyone,

Is it Possible to use sql function or Open SQL function while fetching data from SAP.

I want to load 6 digits from right side of a particular columns in SAP while fetching the data.

Eg: Suppose a row in TABKEY field have a value 1010100000123456

OUTPUT : 123456

I want achieve this in SQL Select statement as i have to write sub query based on this select statement.

[CDPOS]: // Change document items

Load

[TABNAME] as [Table_TABNAME],

[MANDANT] as [Client_MANDANT],

[OBJECTCLAS] as [Object_OBJECTCLAS],

[CHANGENR] as [Doc. no._CHANGENR],

[VALUE_OLD] as [Old value_VALUE_OLD],

[VALUE_NEW] as [New value_VALUE_NEW],

[CHNGIND] as [ChangeInd._CHNGIND],

[TEXT_CASE] as [Text flag_TEXT_CASE],

[UNIT_OLD] as [Unit_UNIT_OLD],

[UNIT_NEW] as [Unit_UNIT_NEW],

[CUKY_OLD] as [CUKY_CUKY_OLD],

[CUKY_NEW] as [CUKY_CUKY_NEW],

[_DATAAGING] as [__DATAAGING],

[FNAME] as [Field_FNAME],

[OBJECTID] as [Obj. value_OBJECTID],

[OBJECTID] as [Sales Doc._VBELN],

[TABKEY] as [Table Key_TABKEY],

POSNR

;

SQL Select FNAME TABNAME MANDANT OBJECTID OBJECTCLAS CHANGENR VALUE_OLD VALUE_NEW CHNGIND TEXT_CASE UNIT_OLD UNIT_NEW CUKY_OLD CUKY_NEW _DATAAGING TABKEY substr(TABKEY,6,6) AS POSNR from CDPOS

WHERE TABNAME = 'VBAP' AND CHNGIND = 'D';

I have tried with SAPSQL connector still not getting the desired result.

yfe‌ @Thomas

Re: SQL Function while fetching data from SAP

marcus_sommer — Fri, 12 May 2017 08:43:07 GMT

Maybe you could do it within the preceeding-part, like:

[CDPOS]: // Change document items

Load

[TABNAME] as [Table_TABNAME],

[MANDANT] as [Client_MANDANT],

[OBJECTCLAS] as [Object_OBJECTCLAS],

[CHANGENR] as [Doc. no._CHANGENR],

[VALUE_OLD] as [Old value_VALUE_OLD],

[VALUE_NEW] as [New value_VALUE_NEW],

[CHNGIND] as [ChangeInd._CHNGIND],

[TEXT_CASE] as [Text flag_TEXT_CASE],

[UNIT_OLD] as [Unit_UNIT_OLD],

[UNIT_NEW] as [Unit_UNIT_NEW],

[CUKY_OLD] as [CUKY_CUKY_OLD],

[CUKY_NEW] as [CUKY_CUKY_NEW],

[_DATAAGING] as [__DATAAGING],

[FNAME] as [Field_FNAME],

[OBJECTID] as [Obj. value_OBJECTID],

[OBJECTID] as [Sales Doc._VBELN],

[TABKEY] as [Table Key_TABKEY],

right([TABKEY], 6) as POSNR

;

SQL Select FNAME TABNAME MANDANT OBJECTID OBJECTCLAS CHANGENR VALUE_OLD VALUE_NEW CHNGIND TEXT_CASE UNIT_OLD UNIT_NEW CUKY_OLD CUKY_NEW _DATAAGING TABKEY substr(TABKEY,6,6) AS POSNR from CDPOS

WHERE TABNAME = 'VBAP' AND CHNGIND = 'D';

- Marcus

Re: SQL Function while fetching data from SAP

Anonymous — Fri, 12 May 2017 08:48:04 GMT

For fetching the a specific number of characters from any field, directly from the left or right there are two functions.

LEFT(fieldName, numCharacters)

RIGHT(fieldName, numCharacters)

In your case it would be:
RIGHT([TABKEY],6)

FieldName is TABKEY and numcharacters in from the right is 6.

Hope this helps.

Re: SQL Function while fetching data from SAP

mohsinqlik — Fri, 12 May 2017 09:34:17 GMT

Thanks for your swift response.

I am aware that right function can be used in Preceding load.

What i am trying to know is whether we can achieve it with SQL or open SQL function while parsing query to SAP server, as i need to write a sub query based on this.

Re: SQL Function while fetching data from SAP

marcus_sommer — Fri, 12 May 2017 10:31:45 GMT

I have no SAP backend-knowledge but I think there are like in other SQL system various string-functions to do this job. Beside searching here in the various SAP groups or more global per google you could also build a small dummy-load of one or two fields with a where-clause and/or a First /Top statement and trying which functions of left/right/mid ... are known.

- Marcus

Re: SQL Function while fetching data from SAP

Anonymous — Fri, 12 May 2017 11:13:18 GMT

I have two suggestions.

You can create a View at SQL level, where you apply the RIGHT function.

2. Use the RIGHT function within your Load script, and save it to a QVD. Then perform the sub query on the QVD. This option is probably the simplest and by far the most recommended.

Re: SQL Function while fetching data from SAP

mohsinqlik — Fri, 12 May 2017 11:25:58 GMT

Thanks markus and matthew for giving your inputs.