6 Replies Latest reply: May 12, 2017 7:25 AM by mohsin choudhary RSS

    SQL Function while fetching data from SAP

    mohsin choudhary

      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

          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
            Matthew Quinn

            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.