Orale is returning an error in the group by using alias column.
SELECT
CONCAT( CONCAT( a.air_location, ' ,' ), a.air_code ) AS "Departs From",
CONCAT( CONCAT( ad.air_location, ' ,' ), ad.air_code ) AS "Arrives At",
MIN(f.fl_fare) AS "Minimun Fare"
FROM flight f
INNER JOIN airport a ON a.air_code = f.fl_dest
INNER JOIN airport ad ON ad.air_code = f.fl_orig
GROUP BY "Departs From", "Arrives At";
It is returning:
ORA-00904: "Arrives At": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 10 Column: 26
GMB :
You can't use a column alias in the GROUP BY clause in Oracle - and positional parameters are not supported either. You need to repeat the expression, or use a subquery or cte. So:\nselect \n ao.air_location || ' ,' || ao.air_code as departs_from,\n ad.air_location || ' ,' || ad.air_code as arrives_at,\n min(f.fl_fare) as minimun_fare\nfrom flight f\ninner join airport ad on ad.air_code = f.fl_dest\ninner join airport ao on ao.air_code = f.fl_orig\ngroup by \n ao.air_location || ' ,' || ao.air_code\n ad.air_location || ' ,' || ad.air_code\n\nOther changes to your query:\n\nchanged table aliases to names that seem more meaningful to me (and I think you had the origin and destination inverted in the result)\n\nused standard ANSI string concatenation operator || instead of CONCAT(), which Oracle supports\n\nused column aliases that do not require quoting (without embedded spaces), because it makes the query easier to write - you can revert that change if you really want otherwise.\n\n\nAs a final thought: here is an alternative that uses a lateral join to generate the aliases:\nselect x.departs_from, x.arrives_at, min(f.fl_fare) as minimun_fare\nfrom flight f\ninner join airport ad on ad.air_code = f.fl_dest\ninner join airport ao on ao.air_code = f.fl_orig\ncross apply (\n select \n ao.air_location || ' ,' || ao.air_code as departs_from,\n ad.air_location || ' ,' || ad.air_code as arrives_at\n from dual\n) x\ngroup by x.departs_from, x.arrives_at\n",
2020-11-21T21:31:53