In MySQL, how can you replace variables by using WITH?

Question

I have a query for comparing statement balances with expense records.

I would like to make it a View, but it is using two variables, which you can't use in a View.

It occurs to me that it should be possible to use a Common Table Expressions (CTEs, or "WITH" queries) in order to replace the variables, so I can then package it up as a View.

I've read that CTEs can be used as a sort of named subquery, which can then be reused in the same query. I've played around, trying to get this to work, but can't get away from syntax errors.

Following is the code with variable that I'm using, but it would be lovely to have a conceptual toolbox for doing this in a general manner, rather than simply solving my problem! :-)

SELECT
  sb.id,
  Account,
  acct_name(Account) AS `Name`,
--    acct_name() is a recursive CTE that I wrote to display the name of a
--    hierarchical account name, such as "Assets, Current Assets, Chequing".
  `Begin`,
  Begin_balance,
  `End`,
  End_balance,
  sb.amount `Change`,
  @sss := COALESCE(
    (SELECT SUM(Amount)
        FROM sa_general_journal jg1
        WHERE (sb.id IN(jg1.Statement_s, jg1.Statement_d))
            AND sb.account = jg1.Source),
    0) AS `Src`,
  @ddd := COALESCE(
    (SELECT SUM(Amount)
        FROM sa_general_journal jg2
        WHERE (sb.id IN(jg2.Statement_s, jg2.Statement_d))
            AND sb.account = jg2.Destination),
    0) AS `Dst`,
  CONVERT(@ddd - @sss, DECIMAL (8,2)) AS `Dest-Src`,
  IF(CONVERT(@ddd - @sss - sb.amount, DECIMAL (8,2)) = 0,
    NULL,
    CONVERT(@ddd - @sss - sb.amount, DECIMAL (8,2))) AS `Error`
FROM sa_statement_balances sb
    LEFT JOIN sa_accounts a ON Account = a.ID
# WHERE `Begin` >= '2020-07-01'
GROUP BY sb.id DESC
ORDER BY BEGIN

I tried replacing

  @sss := COALESCE(…, 0) AS `Src`,

with

  COALESCE(WITH sss AS (
    SELECT SUM(Amount)
      FROM sa_general_journal jg1
      WHERE (sb.id IN(jg1.Statement_s, jg1.Statement_d))
        AND sb.account = jg1.Source) SELECT * from sss), 0) AS `Src`,

But all I got was a discouraging syntax error.

Any ideas for a generalized method of replacing variables with CTEs, especially in a way that would solve my specific problem?

Thanks!

There's no point in using a CTE if you're just using it once in the SUM() function. — Barmar
– Barmar, Commented Sep 16, 2021 at 3:43
Why not make it a stored procedure? Don't get fixated by a solution before you've solved the problem. — user207421
– user207421, Commented Sep 16, 2021 at 3:55
I think that you must rewrite your query and do not use user-defined variables at all. — Akina
– Akina, Commented Sep 16, 2021 at 5:16

Akina · Accepted Answer · 2021-09-16 05:21:44Z

1

Test

WITH cte AS (
SELECT sb.id,
       Account,
       acct_name(Account) AS `Name`,
       `Begin`,
       Begin_balance,
       `End`,
       End_balance,
       sb.amount `Change`,
       COALESCE((SELECT SUM(Amount)
                 FROM sa_general_journal jg1
                 WHERE (sb.id IN(jg1.Statement_s, jg1.Statement_d))
                   AND sb.account = jg1.Source),0) AS `Src`,
       COALESCE((SELECT SUM(Amount)
                 FROM sa_general_journal jg2
                 WHERE (sb.id IN(jg2.Statement_s, jg2.Statement_d))
                   AND sb.account = jg2.Destination),0) AS `Dst`,
       sb.amount
FROM sa_statement_balances sb
LEFT JOIN sa_accounts a ON Account = a.ID
# WHERE `Begin` >= '2020-07-01'
GROUP BY sb.id DESC
)
SELECT id,
       Account,
       `Name`,
       `Begin`,
       Begin_balance,
       `End`,
       End_balance,
       `Change`,
       `Src`,
       `Dst`,
       CONVERT(`Dst` - `Src`, DECIMAL (8,2)) AS `Dest-Src`,
       IF(CONVERT(`Dst` - `Src` - amount, DECIMAL (8,2)) = 0,
          NULL,
          CONVERT(`Dst` - `Src` - amount, DECIMAL (8,2))) AS `Error`
FROM cte
ORDER BY `BEGIN`

answered Sep 16, 2021 at 5:21

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

1 Comment

Jan Steinman Over a year ago

Wow. I think what I'm seeing is that I was fixated on using two CTEs to replace the two subqueries I was stuffing into variables, but you elegantly made a CTE that allowed the dependent select use column names, instead of variables. I've done lots of procedural programming, but I'm really struggling with learning how to "think" in CTEs. I believe you've helped me before, and I very much appreciate your help!

Collectives™ on Stack Overflow

In MySQL, how can you replace variables by using WITH?

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