SQL - Reference a column value in the same query context-CodePudding

I need to know if there's any way I can reference a column value in the same query context.

In this example I'm defining "amount" value on the second nested select and trying to access it on the first nested select.

I'm getting Reference 'amount' not supported (forward reference in item list)

I know if I define "amount" first on the columns list it works but it will change the order, affecting target tables.

select
    col_1,
    col_2,
    (select if(amount >= 0, 'debit', 'credit')),
    col_3,
    (select json_value((select json from table), '$.key.val')) as amount,
    col_4
from my_table;

Is there any way I can assign a value to be available on the query context no matter the order? I want to avoid duplicating code and make the query more efficient by avoiding redundant calculations.

CodePudding user response：

Do the json part in a derived table (i.e. the subquery):

select 
    col_1,
    col_2,
    case when amount >= 0 then 'debit' else 'credit' end,
    col_3,
    amount,
    col_4
from
(
    select
        col_1,
        col_2,
        col_3,
        json_value(_json, '$.key.val') as amount,
        col_4
    from my_table
) dt