ORA-00904: "": invalid identifier in Powerbuilder-CodePudding

I am getting a wired error in datawindow syntax in powerbuilder. Database Oracle 12 C.

Here is the error-

ORA-00904: "SEAT_AVLB": invalid identifier

Here is the code -

 SELECT 0 as sr,
        "TBL_CLASS_MST"."CLASS_NAME",   
         "TBL_CLASS_MST"."SECTION",   
         "TBL_CLASS_MST"."ROOM_NO",   
         "TBL_ROOMS_MST"."MAX_CAP" ,
        TBL_ROOMS_MST.max_cap - (select count(*) from tbl_student_mst) as seat_avlb
    FROM "TBL_CLASS_MST",   
         "TBL_ROOMS_MST"  
   WHERE ( "TBL_CLASS_MST"."ROOM_NO" = "TBL_ROOMS_MST"."ROOM_NO" ) and
        ((class_nr= :ra_class) and
        (seat_avlb>0))

CodePudding user response：

TRY THIS

seat_avlb alias column cannot be used in where condition , with sub query you can apply the cond seat_avlb>0

SELECT * FROM (

 SELECT 0 as sr,
        "TBL_CLASS_MST"."CLASS_NAME",   
         "TBL_CLASS_MST"."SECTION",   
         "TBL_CLASS_MST"."ROOM_NO",   
         "TBL_ROOMS_MST"."MAX_CAP" ,
        TBL_ROOMS_MST.max_cap - (select count(*) from tbl_student_mst) as seat_avlb
    FROM "TBL_CLASS_MST",   
         "TBL_ROOMS_MST"  
   WHERE ( "TBL_CLASS_MST"."ROOM_NO" = "TBL_ROOMS_MST"."ROOM_NO" ) 
   )
   WHERE ((class_nr= :ra_class) and (seat_avlb>0))

CodePudding user response：

You can't use alias that way; put the whole expression (that makes seat_avlb) into the where clause:

SELECT 0  AS sr,
       "TBL_CLASS_MST"."CLASS_NAME",
       "TBL_CLASS_MST"."SECTION",
       "TBL_CLASS_MST"."ROOM_NO",
       "TBL_ROOMS_MST"."MAX_CAP",
       tbl_rooms_mst.max_cap - (SELECT COUNT (*) FROM tbl_student_mst) AS seat_avlb
  FROM "TBL_CLASS_MST", "TBL_ROOMS_MST"
 WHERE     ("TBL_CLASS_MST"."ROOM_NO" = "TBL_ROOMS_MST"."ROOM_NO")
       AND (    (class_nr = :ra_class)
            AND (  tbl_rooms_mst.max_cap
                 - (SELECT COUNT (*) FROM tbl_student_mst) >
                 0));

CodePudding user response：

You cannot refer to a column alias defined in the SELECT clause in the WHERE clause as the WHERE clause is processed before the SELECT clause. Instead, you can define the alias in a sub-query and then refer to it in the outer query:

SELECT *
FROM   (
  SELECT 0 as sr,
         c.CLASS_NAME,
         c.SECTION,
         c.ROOM_NO,
         r.MAX_CAP,
         r.max_cap - (select count(*) from tbl_student_mst) as seat_avlb
  FROM   TBL_CLASS_MST c
         INNER JOIN TBL_ROOMS_MST r
         ON (c.ROOM_NO = r.ROOM_NO)
  WHERE  class_nr= :ra_class
)
WHERE  seat_avlb > 0;

Note: you can also use the modern ANSI join syntax rather than the legacy comma-join syntax.

Or, you can count the students in the FROM clause and not try to re-use the column alias:

SELECT 0 as sr,
       c.CLASS_NAME,
       c.SECTION,
       c.ROOM_NO,
       r.MAX_CAP,
       r.max_cap - s.num_students as seat_avlb
FROM   TBL_CLASS_MST c
       INNER JOIN TBL_ROOMS_MST r
       ON (c.ROOM_NO = r.ROOM_NO)
       CROSS JOIN (select count(*) AS num_students from tbl_student_mst) s
WHERE  class_nr= :ra_class
AND    r.max_cap > s.num_students;

db<>fiddle here