AnsweredAssumed Answered

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

Question asked by ianmanning on Aug 2, 2016
Latest reply on Aug 4, 2016 by 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

Outcomes