ORACLE ALIAS in WHERE Clause Subquery

Here is my SQL code.

I used subquery and labeled it, but when I call column from that subquery, it shows that as invalid identifier.

SELECT   TSR.Merchant_id, denomination, SUM (no_of_cards)
    FROM sales_details
   WHERE invoice_id IN (
            SELECT invoice_id
              FROM sales_header TSR
             WHERE entered_by = (
                      (SELECT account_code
                         FROM bk_dsr_account_codes
                        WHERE user_name = 'C'
                          AND PASSWORD = 'D'
                          AND mobile_no = '8994035090213391259'))
               AND entered_date BETWEEN (TO_DATE ('2013/04/01', 'yyyy/mm/dd')
                                        )
                                    AND (TO_DATE ('2013/06/30', 'yyyy/mm/dd')
                                        ))
GROUP BY denomination

error as per below

ORA-00904: "TSR"."MERCHANT_ID": invalid identifier

Please help me to sort out this issue. I need to call subquery’s columns also in my final SQL view.

Answer

You can only use colums that are in your FROM clause. So, your query should be something like:

SELECT TSR.Merchant_id, SD.denomination, SUM (SD.no_of_cards)
  FROM sales_details SD
  JOIN sales_header TSR on SD.invoice_od = TSR.invoice_id
 WHERE entered_by = (
                  (SELECT account_code
                     FROM bk_dsr_account_codes
                    WHERE user_name = 'C'
                      AND PASSWORD = 'D'
                      AND mobile_no = '8994035090213391259'))
           AND entered_date BETWEEN (TO_DATE ('2013/04/01', 'yyyy/mm/dd')
                                    )
                                AND (TO_DATE ('2013/06/30', 'yyyy/mm/dd')
                                    )
GROUP BY SD.denomination