Translate query to Slick

[Georgios Larkou]

We would like to convert the following MySQL query to Slick. Is it possible? I couldn't find an example with `SET` or `running total anywhere`.

Select some elements from OldTransactions and add a balance column with running total. From the result select a specific transaction by transaction id and show the balance at that point.

    SET @runtot =0; 

    

    SELECT balance 

    FROM   (SELECT clno, 

                   acno1, 

                   ( @runtot := @runtot + r.amount ) AS balance 

            FROM   (SELECT * 

                    FROM   oldtransactions 

                    WHERE  ( acno1 = '' 

                              OR acno2 = '' )) AS t 

            ORDER  BY trtime ASC) AS final 

    WHERE  tt_id = '123' 

[Victor Para]

A lot of stuff is missing from your query which is doing a lot of shuffling without much work. Probably you could simplify it to reduce the number of inner selects so you can better how to translate it.

My guess is that it will look something like the following using a for comprehension:

(for {
    t <- oldtransactions if t. acno1.isEmpty && acno2.isEmpty
   balance = t. runtot + t.amount
} yield (t , balance))

.toList.sortBy(_._1.trtime)

.takeWhile(t=> t.id == 123).sum(_._2)