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

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!

CodePudding user response：

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`