Unable to resolve column when I use SUM of another table

Question

When I want to use Where clause to total_debtor_price and total_debtor_price I got this error message:

Unable to resolve column total_debtor_price (or total_creditor_price);

my query works fine without where clause.

DROP TEMPORARY TABLE IF EXISTS tmp_AccountingDocument_datatable;

CREATE TEMPORARY TABLE tmp_AccountingDocument_datatable
SELECT 
  TAD.*,
  (
    SELECT SUM(TADD.debtor_price) AS total_debtor_price 
    FROM Vw_AccountingDocumentDetail TADD 
    WHERE TADD.accounting_document_id = TAD.id
  ) total_debtor_price,
  (
    SELECT SUM(TADD.creditor_price) AS total_creditor_price 
    FROM Vw_AccountingDocumentDetail TADD 
    WHERE TADD.accounting_document_id = TAD.id
  ) total_creditor_price
FROM Tb_Accounting_Documents TAD
WHERE IF(
  NOT ISNULL(_Filter_Price_Status),
  CASE
    WHEN _Filter_Price_Status = 'smaller' 
      THEN (
        total_debtor_price <= _Filter_Price OR 
        total_creditor_price <= _Filter_Price
      )
    WHEN _Filter_Price_Status = 'equal' 
      THEN (
        total_debtor_price = _Filter_Price OR 
        total_creditor_price = _Filter_Price
      )
    WHEN _Filter_Price_Status = 'bigger' 
      THEN (
        total_debtor_price >= _Filter_Price OR 
        total_creditor_price >= _Filter_Price
      )
  END,
  TRUE
)

The syntax is `CREATE TEMPORARY TABLE tbl AS` ... you are missing `AS`, [see here](https://stackoverflow.com/questions/9715580/mysql-create-table-as-select). — Tim Biegeleisen, Sep 18 '19 at 06:10
total_debtor_price and total_creditor_price are declared in your select statement, you cannot use them on the where clause. You can use the expression though. — hazimdikenli, Sep 18 '19 at 06:12
If the version you are using supports `with` statement try that, otherwise use nested selects, an inner select that declares the total columns, and then an outer select to filter on that select and to populate the tm table. — hazimdikenli, Sep 18 '19 at 06:15

score 1 · Accepted Answer · answered Sep 18 '19 at 06:11

you cannot use column allias on where clause, but you can put main query into subquery in order to use where clause with column aliases

 SELECT * FROM (
    SELECT TAD.*,(SELECT SUM(TADD.debtor_price) as total_debtor_price FROM Vw_AccountingDocumentDetail TADD WHERE TADD.accounting_document_id = TAD.id) total_debtor_price,
           (SELECT SUM(TADD.creditor_price) as total_creditor_price FROM Vw_AccountingDocumentDetail TADD WHERE TADD.accounting_document_id = TAD.id) total_creditor_price
        FROM Tb_Accounting_Documents TAD
  ) TMP
    WHERE
    IF(NOT isnull(_Filter_Price_Status),
             CASE
                 WHEN _Filter_Price_Status = 'smaller' THEN (total_debtor_price <= _Filter_Price OR total_creditor_price <= _Filter_Price)
                 WHEN _Filter_Price_Status = 'equal' THEN (total_debtor_price = _Filter_Price OR total_creditor_price = _Filter_Price)
                 WHEN _Filter_Price_Status = 'bigger' THEN (total_debtor_price >= _Filter_Price OR total_creditor_price >= _Filter_Price)
                 END, TRUE)

Unable to resolve column when I use SUM of another table

1 Answers1