8 Replies Latest reply on Aug 4, 2016 8:03 AM by ianmanning

    How can I get the result of an SQL function built into an external MS SQL database

    ianmanning

      I need to retrieve the Account balance from an external SQL database.

      The SQL database is linked to my FM solution and I can see the data fields etc and I can grab the data from those fields but the account balance is not in a single field. The database has an internal function

      <Company_Code>.EntCustBalance

      Declaration:

      EntCustBalance ( Year, Period, CustomerCode)

      Category:

      Customer

      Description:

      This function returns the Balance from the Customer’s
      account history for the specified Year and Period.

      Example:

      SELECT ZZZZ01.EntCustBalance
      (2006, 0, 'ABAP01')

      I have tried adding a calculation to the linked table  with the calculation set as

      ExecuteSQL ( "SELECT Turc06.EntCustBalance (2016, 0, 'C00618')"; ",";"," )

      but I just get a ? in the result

       

      another example they give is

       

      Example - Customer Balances

      The following example lists customers and their current balances.

      Customers are held in the CUSTSUPP table.

      To access only the customers within this table you will need to ensure you
      apply a filter of 'C' on the acCustSupp field. The customer balances are
      returned by using the function EntCustBalance.

       

      SELECT acCode AS 'Ac Code', acCompany AS 'Company Name'
      ,
      ZZZZ01.EntCustBalance(2020, 0, acCode) AS 'Balance'
      FROM
      ZZZZ01.CUSTSUPP
      WHERE acCustSupp = 'C'
      ORDER BY acCode

       

      Example Query Results

      Ac Code

      Company Name

      Balance

      ABAP01

      A B Applications Limited

      22069.66

      ACEE01

      Ace Electrical Supplies

      106.61

      AFEL01

      A F Electrical Limited

      7334.75

      ALLB01

      Allbright Electrics (Paris)

      11245.73

       

       

      Any help is gratefully received.

      Best regards

      Ian