Why the SELECT query not get the data into application item?

I want to SELECT the beds into POPUP LOV depends on the ward selected on another POPUP LOV

I used this in list of value SQL query to SELECT the beds :

select b.bed_no
from hospital_beds b , hospital_wards w
where bed_status = 1
and b.ward_no = w.ward_no
and w.ward_foriegn_name like '%:P22_WARD_NO%'
and b.hospital_no = :P0_HOSPITAL_NO;

but when I select the ward name then go to beds POPUP its not get the beds

when I run the QUERY in SQL command its returning data

this is the SELECT I used in SQL to test its working or not and its working :

select b.bed_no
from hospital_beds b , hospital_wards w
where bed_status = 1
and b.ward_no = w.ward_no
and w.ward_foriegn_name like '%male medical%'
and b.hospital_no = 720022;

whats the wrong with my query ?

EDIT :
I changed the SQL QUERY :

select b.bed_no as display , bed_no as return 
from hospital_beds b , hospital_wards w
where bed_status = 1
and b.ward_no = w.ward_no
and w.ward_foriegn_name like '%' || :P22_WARD_NO || '%'
and b.hospital_no = :P0_HOSPITAL_NO;

then when run the form and select the ward from the list and try to select the beds no bed appear what is the missing please ?

I tried in Parent items the following :

1- P22_WARD_NO - no beds show
2- P22_WARD_NO , P0_HOSPITAL_NO - no beds show 
3- P22_WARD_NO  , P22_BED_NO - no beds show 

in items to submit I tried :

1- P22_BED_NO  - no beds show 
2- empty - no beds show 

where is the missing ?

I want to create a hospital system

the tables DDL :

     CREATE TABLE "HOSPITAL_WARDS" 
       (	"HOSPITAL_NO" VARCHAR2(10), 
    	"WARD_NO" VARCHAR2(6), 
    	"WARD_NATIVE_NAME" VARCHAR2(40) NOT NULL ENABLE, 
    	"WARD_FORIEGN_NAME" VARCHAR2(40) NOT NULL ENABLE, 
     CONSTRAINT "HOSPITAL_WARDS_PK" PRIMARY KEY ("WARD_NO")
  USING INDEX  ENABLE
       ) ;
    
     CREATE TABLE "HOSPITAL_BEDS" 
       (	"HOSPITAL_NO" VARCHAR2(10), 
    	"WARD_NO" VARCHAR2(6), 
    	"BED_NO" VARCHAR2(8), 
    	"BED_STATUS" NUMBER(6,0) NOT NULL ENABLE, 
    	"BED_TYPE" NUMBER(6,0), 
    	CONSTRAINT "HOSPITAL_BEDS_PK" PRIMARY KEY ("BED_NO")
      USING INDEX  ENABLE
       ) ;
    
      ALTER TABLE "HOSPITAL_BEDS" ADD CONSTRAINT "HOSPITAL_BEDS_FK" FOREIGN KEY ("WARD_NO")
    	  REFERENCES "HOSPITAL_WARDS" ("WARD_NO") ENABLE;

P0_HOSPITAL_NO the global variable login hospital because the system will be more than one hospital so I keep the hospital number which logged in like multi branch company the system multi hospitals .

this is sample of the data

table HOSPITAL_WARDS
hospital no    ward no       ward f.name              ward n.name 
100              1            male medical ward           جناح باطنة رجال
100               2           male surgical ward           جناح جراحة رجال 

table HOSPITAL_BEDS 
hospital no    ward no   bed no    bed status  
  100            1         1       (1)  Available 
  100            1         2       (1) Available 
  100            1         3       (2) Not available

  100            2         1        (1) available
  100            2         2        (1) available
  100            2         3        (1) available 

Then I have 2 list of values SQL QUERY the first one for hospital admission wards

select  CASE WHEN APEX_UTIL.GET_SESSION_LANG = 'ar-ae'  
THEN WARD_NATIVE_NAME  
ELSE WARD_FORIEGN_NAME END as ward_DESC,
    ward_NO as ward_no 
 from HOSPITAL_WARDS
 where hospital_no = :P0_HOSPITAL_NO

the second list of values to read the available beds for selected admission wards in the item ward

select b.bed_no as display , b.bed_no as return 
from hospital_beds b , hospital_wards w
where bed_status = 1
and b.ward_no = w.ward_no
and w.ward_foriegn_name like '%' || :P22_WARD_NO || '%'
and b.hospital_no = :P0_HOSPITAL_NO;

I removed P22_BED_NO from cascading list of values and kept only P22_WARD_NO on parent item for P22_BED_NO page item ,
this is the full cycle thank you

Three objections:

• did you set Parent LoV property to both items you're using in this WHERE clause? If not, do so (image illustrates it, as you commented that you don't know where that property is)

  

• did you try to concatenate the first one with wildcards, i.e.

  and w.ward_foriegn_name like '%' || :P22_WARD_NO || '%'
  and b.hospital_no = :P0_HOSPITAL_NO;
  

• query you posted is invalid if we speak of Apex LoV queries; they are supposed to return two values: display and return value. The first one is usually some name, and the second one is usually some id

[EDIT, based on additional info you posted]

The second LoV query seems to be invalid. You shouldn't match ward_no to their name. Try this instead:

select b.bed_no d, b.bed_No r
from hospital_beds b join hospital_wards w on w.ward_no = b.ward_no
where b.bed_status = 1
  and w.ward_no = :P22_WARD_NO
  and b.hospital_no = :P0_HOSPITAL_NO;

Of course, put both P22_WARD_NO and P0_HOSPITAL_NO into "Parent items" property.