Your Location is: Home > C++

Query breaks whenever I add MAX() functions or grouping statements

From: Mongolia View: 2512 Sam 

Question

I have some tables laid out like so:

Airplane  
(airplaneID number(2) primary key, airplaneName char(20),  cruisingRange number(5));

Flights   
(airplaneID number (2), flightNo number(4) primary key, 
fromAirport char(20), toAirport char(20), distance number(4), depart timestamp, 
arrives timestamp, foreign key (airplaneID) references Airplane);

Employees 
(employeeID number(10) primary key, employeeName char(18), salary number(7));

Certified 
(employeeID number(10), airplaneID number(2),
foreign key (airplaneID) references Airplane, 
foreign key (employeeID) references Employees );

And I need to write a query to get the following information:

For each pilot who is certified for at least 4 airplanes, find the employeeName and the maximum cruisingRange of the airplanes for which that pilot is certified.

The query I have written is this:

SELECT Employees.employeeName, MAX(Airplane.cruisingRange)
FROM Employees
JOIN Certified ON Employees.employeeID = Certified.employeeID
JOIN Airplane ON Airplane.airplaneID = Certified.airplaneID
GROUP BY Employees.employeeName
HAVING COUNT(*) > 3

Lastly, this is the function that executes and reads in the query information:

void  prepareAndExecuteIt() {

//  Prepare the query

    //sqlQueryToRun.len = strlen((char *) sqlQueryToRun.arr);
    exec sql PREPARE dbVariableToHoldQuery FROM :sqlQueryToRun;

/* The declare statement, below, associates a cursor with a
 * PREPAREd statement.  
 * The cursor name, like the statement
 * name, does not appear in the Declare Section.
 * A single cursor name can not be declared more than once.
*/

    exec sql DECLARE cursorToHoldResultTuples cursor FOR dbVariableToHoldQuery;

    exec sql OPEN cursorToHoldResultTuples;
    int i = 0;
    exec sql WHENEVER NOT FOUND DO break;
    while(1){
        exec sql FETCH cursorToHoldResultTuples INTO empName, cruiseRange;
        printf("%s\t", empName);
        printf("%s\n", cruiseRange);
        i++;

        // This is temporary while I debug so it doesn't just loop on forever when the query breaks.
        if (i > 500){
            printf("Entered break statement\n");
            break;
        }
    }

    exec sql CLOSE cursorToHoldResultTuples;
}

The query works until I add the MAX(), GROUP BY, and HAVING statements. Then it just reads in nothing infinitely. I don't know if this is an issue with the way I've written my query or if it's an issue with the C++ code that executes it. I'm using the ProC interface to access an Oracle database. Any ideas as to what's going wrong?

Best answer

You can't mix implicit and explicit joins. I suggest

SELECT Employees.employeeName, MAX(Airplane.cruisingRange)
FROM Employees
JOIN Certified ON Employees.employeeID = Certified.employeeID
JOIN Airplane ON Airplane.airplaneID = Certified.airplaneID
GROUP BY Employees.employeeName
HAVING COUNT(*) > 3

which works fine.

db<>fiddle here