Skip to main content

Building a query

A query is an SQL query against the FA database. In most cases, the database is MySQL so the SQL needs to be compatible with that. Query needs to be any valid SELECT query against the database - no other types of queries are allowed.

SELECT s.name AS Name, s.isin_code as ISIN
FROM pm2_security s
SELECT * FROM pm2_security

Since the data in FA is divided into multiple tables, we recommend using joins to combine the data.

utilities_SQL_joins.png

More information on building SQL queries: SQL tutorial, MySQL Functions, MySQL Select syntax, MySQL Subqueries.

You can build your queries in the Queries view. Enable the query editor by clicking Show query, and type or copy-paste your query into the text area.The query editor is context-sensitive and supports building the query in the following way:

[CTRL + Space]

Lists all the available database tables.

<table name>[.]

Lists all the available table columns.

<table alias>[.]

List all the available table columns.

[CTRL + Space] then [$]

Lists all the available custom functions supported.

[Enter]

Inserts a line break.

[CTRL + Enter]

Executes the query.

Examples

Listing assets under managements of all the EUR based portfolios over time:

SELECT r.report_date AS Date, ROUND(SUM(r.market_value),2) AS AUM, count(p.id) AS Portfolios
FROM pm2_pf_report r
LEFT JOIN pm2_portfolio p ON p.id = r.pf_id
LEFT JOIN pm2_security cur ON cur.id = p.currency
WHERE cur.security_code='EUR' AND ( month(r.report_date) != month(ADDDATE(r.report_date, INTERVAL 1 DAY)) OR ( r.report_date = CURDATE()) )
GROUP BY Date
ORDER BY r.report_date ASC

Listing top ten biggest portfolios on the selected date:

SELECT p.name AS Portfolio, ROUND(r.market_value,2) AS AUM
FROM pm2_pf_report r
LEFT JOIN pm2_portfolio p ON p.id = r.pf_id
LEFT JOIN pm2_security cur ON cur.id = p.currency
WHERE cur.security_code='EUR' AND r.report_date = $P(endDate)
ORDER BY AUM DESC
LIMIT 10