Creating calculated field with subquery in oracle

Question

so I'm new to Oracle SQL and am trying to create a calculated column using a subquery. It seems like this information shouldn't be so hard to find but I haven't been able to find it... I would really appreciate any guidance!

SQL> CREATE VIEW booking_agent_view
  2  AS
  3  SELECT sf.flight_nbr, sf.flight_date, seats_available,
  4  FROM sched_flight sf, aircraft_type at, aircraft_data ad, (SELECT count(cust_nbr) FROM reservation r GROUP BY flight_nbr, flight_date) seats_available
  5  WHERE sf.aircraft_serial_nbr = at.aircraft_serial_nbr AND at.type=ad.type;

FROM sched_flight sf, aircraft_type at, aircraft_data ad, (SELECT count(cust_nbr) FROM reservation r GROUP BY flight_nbr, flight_date) AS seats_available
*
ERROR at line 4:
ORA-00936: missing expression

Thank you!

score 0 · Answer 1 · answered Dec 02 '20 at 19:56

Should be something like this:

CREATE VIEW booking_agent_view
AS
SELECT sf.flight_nbr, 
       sf.flight_date, 
       --
      (SELECT count(cust_nbr) 
       FROM reservation r 
      ) seats_available
FROM sched_flight sf, 
     aircraft_type at, 
     aircraft_data ad    
WHERE sf.aircraft_serial_nbr = at.aircraft_serial_nbr 
  AND at.type = ad.type;

In other words: seats_available should be put into the select column list. As it must return just one value, you have to remove its group by clause as it'll probably cause problems.

If that's not what you're looking for and subquery should really be part of the FROM clause, then its count function should have an alias (e.g. count(cust_nbr) as cnt_cust) and you'll probably have to join that subquery to other table(s) - otherwise you'll produce cross join.

Basically, it depends on what you really have and need. Sample data, as well as desired output might help us help you.

score 0 · Answer 2 · answered Dec 03 '20 at 02:46

I am not sure about logic but you are facing

ERROR at line 4:
ORA-00936: missing expression

Because of the extra comma (,) at last column in the select list.

....
SELECT sf.flight_nbr, sf.flight_date, seats_available --, <-- last comma
 FROM ...
.....

Creating calculated field with subquery in oracle

2 Answers2