function not giving any responce-CodePudding

So i have created a database for an airline that currently has 3 tables. Table1: FLIGHTS, Table2: Clients, Table3: Reservation and i want to create a function that returns the time left in month from the reservation made till the flights date inside a function. After i runn it unfortunately nothing is returning. Any tips?

DROP DATABASE if exists Chartered_Airlines;
CREATE DATABASE Chartered_Airlines;
USE Chartered_Airlines;

CREATE TABLE FLIGHTS(FLIGHT_NO INT(5)   NOT NULL, DEPARTURE VARCHAR(15), ARRIVAL    VARCHAR(15), TYPEFL VARCHAR(15), SEATS INT(4) NOT NULL, FREE_SEATS INT(4), FLIGHT_DATE DATE,
    PRIMARY KEY(FLIGHT_NO));
CREATE TABLE CUSTOMERS(CL_NO    INT(5)  NOT NULL, LAST_NAME VARCHAR(15) NOT NULL, FIRST_NAME VARCHAR(15) NOT NULL, CITIZENSHIP VARCHAR(15) NOT NULL, B_DATE DATE,
    PRIMARY KEY(CL_NO));
CREATE TABLE RESERVATIONS(RES_NO INT(5) NOT NULL, CL_NO INT(5)  NOT NULL, FLIGHT_NO INT(5), COST FLOAT(15), RES_DATE DATE,
    PRIMARY KEY(RES_NO),
    FOREIGN KEY(FLIGHT_NO) REFERENCES FLIGHTS(FLIGHT_NO),
    FOREIGN KEY(CL_NO) REFERENCES CUSTOMERS(CL_NO));

SHOW TABLES;

INSERT INTO FLIGHTS VALUES(10,'ATHENS','LONDON','EXTERNAL',310,34, '2023/10/04');
INSERT INTO FLIGHTS VALUES(20,'ATHENS','MYKONOS','INTERNAL',100,5, '2022/12/12');
INSERT INTO FLIGHTS VALUES(30,'ATHENS','PARIS','EXTERNAL',256,16, '2022/09/07');
 
INSERT INTO CUSTOMERS VALUES(5472,'ALEKSANDRAKIS','GEORGIOS','GREECE','1989/01/01');
INSERT INTO CUSTOMERS VALUES(1354,'STAVROU','ANTREAS','GREECE','1977/07/07');
INSERT INTO CUSTOMERS VALUES(6598,'AMALFIO','ROBERTO','ITALY','1995/05/02');
INSERT INTO CUSTOMERS VALUES(1953,'EUSTATHIOU','NIKOS','GREECE','2001/06/03');
INSERT INTO CUSTOMERS VALUES(1387,'STAKAS','VASILLIS','GREECE','1990/01/07');

INSERT INTO RESERVATIONS VALUES(3174,5472,10,6482, '2020/03/02');
INSERT INTO RESERVATIONS VALUES(3143,1354,10,6482, '2021/05/09');
INSERT INTO RESERVATIONS VALUES(1286,6598,20,662, '2022/10/12');
INSERT INTO RESERVATIONS VALUES(3275,1953,20,662, '2022/09/08');
INSERT INTO RESERVATIONS VALUES(7654,1387,30,264, '2022/09/06');


SELECT * FROM FLIGHTS;
SELECT * FROM CUSTOMERS;
SELECT * FROM RESERVATIONS;

DESC FLIGHTS;
DESC RESERVATIONS;
DESC CUSTOMERS;


DELIMITER //
CREATE FUNCTION TIME_LEFT(RES_NO INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE TIME_LEFT INT DEFAULT 0;
    SELECT TIMESTAMPDIFF(MONTH,RESERVATIONS.RES_DATE,FLIGHTS.FLIGHT_DATE) INTO TIME_LEFT FROM RESERVATIONS 
    WHERE RES_NO = RES_NO AND RESERVATIONS.FLIGHT_NO = FLIGHTS.FLIGHT_NO ;
    RETURN TIME_LEFT;

END;
//

SELECT TIME_LEFT(3143);

I was expecting that it returns the datediff between reservation made date amd flight date

CodePudding user response：

Your forgot to use join and also use a variable name for holding the value passed in function:

DELIMITER //

CREATE FUNCTION TIME_LEFT(VAR_RES_NO INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE TIME_LEFT INT DEFAULT 0;
    SELECT TIMESTAMPDIFF(MONTH,RESERVATIONS.RES_DATE,FLIGHTS.FLIGHT_DATE) INTO TIME_LEFT FROM RESERVATIONS join FLIGHTS
    WHERE RES_NO = VAR_RES_NO AND RESERVATIONS.FLIGHT_NO = FLIGHTS.FLIGHT_NO ;
    RETURN TIME_LEFT;

END;
//

CodePudding user response：

I think you need to include the table Flights in the SELECT statement, as well as specify the difference between RES_NO in the input vs reservations e.g.,

CREATE FUNCTION TIME_LEFT(RES_NO_in INT) RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE TIME_LEFT INT DEFAULT 0;
  SELECT TIMESTAMPDIFF(MONTH,RESERVATIONS.RES_DATE,FLIGHTS.FLIGHT_DATE) INTO TIME_LEFT
  FROM RESERVATIONS, FLIGHTS
  WHERE RESERVATIONS.RES_NO = RES_NO_in AND RESERVATIONS.FLIGHT_NO = FLIGHTS.FLIGHT_NO;
  RETURN TIME_LEFT;
END;

CREATE FUNCTION TIME_LEFT(RES_NO_in INT) RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE TIME_LEFT INT DEFAULT 0;
  SELECT TIMESTAMPDIFF(MONTH,RESERVATIONS.RES_DATE,FLIGHTS.FLIGHT_DATE) INTO TIME_LEFT
  FROM RESERVATIONS
    INNER JOIN FLIGHTS ON RESERVATIONS.FLIGHT_NO = FLIGHTS.FLIGHT_NO
  WHERE RESERVATIONS.RES_NO = RES_NO_in;
  RETURN TIME_LEFT;
END;