ORACLE ALIAS in WHERE Clause Subquery

Question

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.

You don't need these nested subqueries. Your query could just be a join between the tables. — David Aldridge, Jun 03 '13 at 07:37

Oscar Pérez · Accepted Answer · 2013-06-03T07:03:28.643

3

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

edited Jun 03 '13 at 07:03

answered Jun 03 '13 at 06:56

Oscar Pérez

4,377
1
17
36

+1 Faster than me and better aliased. You may want to do `denomination` => `SD.denomination` in the group by too to be safe. – Joachim Isaksson Jun 03 '13 at 07:00
Edited response to add comment by Joachim Isaksson – Oscar Pérez Jun 03 '13 at 07:03

score 1 · Answer 2 · answered Jun 03 '13 at 06:59

select * from table1, table2 where table1.userid=x and table2.sdsd=x

also

select * from table1 t1, table2 t2 where t1.userid=x and t2.sdsd=x

same output these 2 above...

try this

SELECT   TSR.Merchant_id, denomination, SUM (no_of_cards)
    FROM sales_details,TSR
   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

ORACLE ALIAS in WHERE Clause Subquery

2 Answers2