SQL Function while fetching data from SAP

[mohsinqlik]

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

[marcus_sommer]

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

[Anonymous]

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]

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.

[marcus_sommer]

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

[Anonymous]

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]

Thanks markus and matthew for giving your inputs.