CODEWITHSUNDEEP
oracle-database
Jack
Jack

Reputation: 513

ORA-00904: invalid column name but I am using the correct column name

Can someone see where I am going wrong in the below query? I am getting the error message that the GROUP BY column doesn't exist, but it clearly does as I see that column name in the output when I don't use the GROUP BY.

SELECT
           (SELECT customer_address.post_code FROM customer_address WHERE customer_address.address_type = 0 AND customer_address.customer_no = orders.customer_no) postcode, SUM(orders.order_no) orders
FROM
           orders, customer_address
WHERE
           orders.delivery_date = '27-MAY-15'
GROUP BY
           postcode;

Upvotes: 1

Views: 981

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

The answer is: You cannot use an alias name in GROUP BY.

So:

GROUP BY (SELECT customer_address.post_code ...);

Or:

select postcode, sum(order_no)
from
(
  SELECT
    (SELECT customer_address.post_code FROM customer_address WHERE customer_address.address_type = 0 AND customer_address.customer_no = orders.customer_no) postcode, 
    orders.order_no
  FROM orders, customer_address
  WHERE orders.delivery_date = '27-MAY-15'
)
GROUP BY postcode;

EDIT:

However, your query seems wrong. Why do you cross-join orders and customer_address? By mistake I guess. Use explicit joins (INNER JOIN customer_address ON ...), when using joins to avoid such errors. But here I guess you'd just have to remove , customer_address.

Then why do you add order numbers? That doesn't seem to make sense.

Upvotes: 2

Related Questions