How to call a procedure inside another procedure and use it twice?-CodePudding

I'm trying to make a procedure from the following code: (that uses another procedure inside)

SET @p0='87';
CALL `wm_ewallet_transactions_balance`(@p0);

with recursive rcte(user_id, balance, date) as (
  (
    select user_id, balance, date
    from  wm_ewallet_user_balance
    order by date
    limit 1
  )
union all
select coalesce(t.user_id, r.user_id),
    coalesce(t.balance, r.balance),
    r.date   interval 1 day
from rcte r
left join wm_ewallet_user_balance t on t.date = r.date   interval 1 day
where r.date < (select max(date) from wm_ewallet_user_balance)
)
select r.user_id, min(r.balance) as balance, r.date
from rcte r
group by r.user_id, r.date
order by r.date;

As you can see, in the second line, I've called another procedure named wm_ewallet_transactions_balance. It creates a temporary table named wm_ewallet_user_balance (and I've used that table three times)

The issue is it throws:

#1137 - Can't reopen table: 'wm_ewallet_user_balance'

How can I fix it?

And here is the first procedure named wm_ewallet_transactions_balance:

CREATE TEMPORARY TABLE wm_ewallet_user_balance
(
    INDEX user_id_index (user_id),
    INDEX created_at_index (date)
)
SELECT
    id,
    user_id,
    type,
    amount,
    SUM(amount) OVER(PARTITION BY user_id ORDER BY id) AS `balance`,
    created_at,
    date(created_at) AS date
FROM
    wm_ewallet_transactions
WHERE user_id = user_id_param

CodePudding user response：

It isn't pretty and it doesn't use indexes, so you must check if a three times genrated temp table makes it faster

WITH  wm_ewallet_user_balance AS
(
SELECT
    id,
    user_id,
    type,
    amount,
    SUM(amount) OVER(PARTITION BY user_id ORDER BY id) AS `balance`,
    created_at,
    date(created_at) AS date
FROM
    wm_ewallet_transactions
WHERE user_id = @p0)
SELECT * FROM (
             WITH recursive rcte(user_id, balance, date) as (
              (
                select user_id, balance, date
                from  wm_ewallet_user_balance
                order by date
                limit 1
              )
            union all
            select coalesce(t.user_id, r.user_id),
                coalesce(t.balance, r.balance),
                r.date   interval 1 day
            from rcte r
            left join wm_ewallet_user_balance t on t.date = r.date   interval 1 day
            where r.date < (select max(date) from wm_ewallet_user_balance)
            )
            select r.user_id, min(r.balance) as balance, r.date
            from rcte r
            group by r.user_id, r.date
            order by r.date) t1;