To find passengers travelling more than thrice on the same route in PL/SQL

Here I am creating three tables, one for storing the Train Info. Another for holding onto the Passenger info and the other one to hold the ticket Info.

create table T_Train_Info(
route_no integer primary key,
source varchar2(50),
destination varchar2(50)
)

create table T_Pay_Info(
pax_id integer primary key,
pax_name varchar2(50),
dob date,
gender varchar2(5)
)

create table T_Tkt_Info(
pax_id integer,
route_no integer,
journey_date date,
seat_no varchar2(5),
primary key(pax_id, route_no, journey_date)
)

In the Train_Info table, I am inserting two unique routes with the same source and destination as there can be different routes for the same source and destination. And filling the other tables in the same manner. In the ticket table, I am repeating values because I aim to find the passenger travelling thrice on the same route.

insert into T_Train_Info values(1, 'Chennai', 'Pune');
insert into T_Train_Info values(2, 'Chennai', 'Pune');
insert into T_Train_Info values(3, 'Bangalore', 'Kolkata');

insert into T_Tkt_Info values(100, 1, to_date('11/03/2022', 'DD/MM/YYYY'), 22);
insert into T_Tkt_Info values(100, 1, to_date('14/08/2022', 'DD/MM/YYYY'), 23);
insert into T_Tkt_Info values(100, 1, to_date('29/08/2022', 'DD/MM/YYYY'), 24);
insert into T_Tkt_Info values(102, 3, to_date('22/08/2022', 'DD/MM/YYYY'), 24);
insert into T_Tkt_Info values(100, 1, to_date('27/08/2022', 'DD/MM/YYYY'), 24);
insert into T_Tkt_Info values(100, 2, to_date('28/08/2022', 'DD/MM/YYYY'), 24);


insert into T_Pay_Info values(100, 'A', to_date('11/03/2022', 'DD/MM/YYYY'), 'F');
insert into T_Pay_Info values(101, 'B', to_date('23/09/2023', 'DD/MM/YYYY'), 'M');
insert into T_Pay_Info values(102, 'A', to_date('11/03/2022', 'DD/MM/YYYY'), 'F');
insert into T_Pay_Info values(103, 'D', to_date('23/09/2023', 'DD/MM/YYYY'), 'M');
insert into T_Pay_Info values(104, 'A', to_date('11/03/2022', 'DD/MM/YYYY'), 'F');
insert into T_Pay_Info values(105, 'A', to_date('23/09/2023', 'DD/MM/YYYY'), 'M');

Here’s my procedure which keeps returning the error saying ‘exact fetch returns more than requested number of rows’ at the select statement. What am I doing wrong here?

create or replace procedure pr_pass_route_details(x in T_Train_Info.Source%type, y in T_Train_Info.Destination%type) is
  pr_name T_Pay_Info.Pax_Name%type;
begin
  for i in (select pax_id from t_tkt_info group by pax_id,route_no having count(*) >=3) loop
    select pax_name into pr_name from t_pay_info where pax_id = i.pax_id and T_Train_Info.Source=x and T_Train_Info.Destination=y;

    dbms_output.put_line(pr_name);

  end loop;

end pr_pass_route_details;

Answer

i’m not sure why you’ve written a SP to do this as you can achieve this with a simple query:

SELECT
pax_id,
route_no,
COUNT(journey_date)
FROM T_Tkt_Info
GROUP BY 
pax_id,
route_no
HAVING COUNT(journey_date) = 3