Qlik Community

QlikView Connector for SAP

Discussion Board for collaboration related to the QlikView Connector for SAP.

mohsinqlik
New Contributor III

SQL Function while fetching data from SAP

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

6 Replies

Re: SQL Function while fetching data from SAP

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

mattquinnterex
Contributor II

Re: SQL Function while fetching data from SAP

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.

mohsinqlik
New Contributor III

Re: SQL Function while fetching data from SAP

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

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

mattquinnterex
Contributor II

Re: SQL Function while fetching data from SAP

I have two suggestions.

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

OR

     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.

mohsinqlik
New Contributor III

Re: SQL Function while fetching data from SAP

Thanks markus and matthew for giving your inputs.