Optimize SQL query and process result

Question

I am looking for advice on optimizing the following sample query and processing the result. The SQL variant in use is the internal FileMaker ExecuteSQL engine which is limited to the SELECT statement with the following syntax:

SELECT [DISTINCT] {* | column_expression [[AS] column_alias],...}  
FROM table_name [table_alias], ...  
[ WHERE expr1 rel_operator expr2 ]  
[ GROUP BY {column_expression, ...} ]  
[ HAVING expr1 rel_operator expr2 ]  
[ UNION [ALL] (SELECT...) ]  
[ ORDER BY {sort_expression [DESC | ASC]}, ... ]  
[ OFFSET n {ROWS | ROW} ]  
[ FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ]
[ FOR  UPDATE [OF {column_expression, ...}] ]

The query:

SELECT item1 as val ,interval, interval_next FROM meddata
    WHERE fk = 12 AND active1 = 1 UNION
SELECT item2 as val ,interval, interval_next FROM meddata
    WHERE fk = 12 AND active2 = 1 UNION
SELECT item3 as val ,interval, interval_next FROM meddata
    WHERE fk = 12 AND active3 = 1 UNION
SELECT item4 as val ,interval, interval_next FROM meddata
    WHERE fk = 12 AND active4 = 1 ORDER BY val

This may give the following result as a sample:

val,interval,interval_next
Artelac,0,1
Artelac,3,6
Celluvisc,1,3
Celluvisc,12,24

What I am looking to achieve (in addition to suggestions for optimization) is a result formatted like this:

val,interval,interval_next,interval,interval_next,interval,interval_next,interval,interval_next ->etc
Artelac,0,1,3,6
Celluvisc,1,3,12,24

Preferably I would like this processed result to be produced by the SQL engine.

Possible?

Thank you.

EDIT: I included the column names in the result for clarity, though they are not part of the result. I wish to illustrate that there may be an arbitrary number of 'interval' and 'interval_next' columns in the result.

Can a distinct val (e.g. Artelac) be found in only one row? Can it be found in more than 2 rows? For example, could you have: {Artelac,0,1}, {Artelac,3,6} and {Artelac,4,8}? In those cases, what would be the desired results? — David Aman
– David Aman, Commented Mar 22, 2016 at 2:09
@DavidAman Thanks for your comment. As I have stated in example result the item is returned on multiple rows and it may have an arbitrary number of rows with the same item (medication) with different interval periods for each. The question is how to concatenate the corresponding intervals to a single row for each item, as I have described in the desired result. Sorry if it was not clear. — AndreasT
– AndreasT, Commented Mar 22, 2016 at 11:10

Nicolai Kant · Accepted Answer · 2016-03-22 11:16:46Z

1

I do not think you need to optimise you query, looks fine to me.

You are looking for something like PIVOT in TSQL, which is not supported in FQL. You biggest issue is going to be a variable number of columns returned.

I think the best approach is to get your intermediate result and use a FileMaker script or Custom Function to pivot it.

An alternative is to get the list of distinct val and loop through them (with CF or script) with FQL Statement for each row. You will not be able to combine them with union as it needs the same number of columns.

answered Mar 22, 2016 at 11:16

Nicolai Kant

1,4011 gold badge11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AndreasT Over a year ago

Thank you for your input. It is good to know that I am on the right track with the query itself. I will look into a recursive CF or FM script for the concatenation job.

Collectives™ on Stack Overflow

Optimize SQL query and process result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related