If statement malfunctioning inside procedure

Question

I need to create a procedure to give an overview of all employees in given country. The overview cannot include cities which do not have any employees.

My code works if I do not include the if statement, but it returns a city which has no employees working there.

This is the unfinished code:

create or replace procedure get_emp_overview
(p_co_id locations.country_id%TYPE)
AS
    v_emp_count NUMBER;
BEGIN
for rec in (select l.location_id,l.city,c.country_name from locations l inner join countries c on c.country_id=l.country_id where l.country_id=p_co_id order by l.location_id desc) LOOP

        DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name || ' - ' || rec.location_id || ' ' || rec.city);

    for rec2 in (select count(e.employee_id) as empCount, d.department_name from departments d inner join employees e on e.department_id=d.department_id where d.location_id = rec.location_id group by d.department_name) LOOP
        IF rec2.empCount != 0 THEN
            DBMS_OUTPUT.PUT_LINE(rec2.department_name || ': ' || rec2.empCount || ' werknemers');
        END IF;
    END LOOP;
END LOOP;
END get_emp_overview;
/

output :

SQL> exec get_emp_overview('US')
==> United States of America - 1700 Seattle
Administration: 1 werknemers
Accounting: 2 werknemers
Purchasing: 6 werknemers
Executive: 3 werknemers
Finance: 6 werknemers
==> United States of America - 1600 South Brunswick <<<<<<< problem
==> United States of America - 1500 South San Francisco
Shipping: 45 werknemers
==> United States of America - 1400 Southlake
IT: 5 werknemers

So I tried the following code:

create or replace procedure get_emp_overview
(p_co_id locations.country_id%TYPE)
AS
    v_emp_count NUMBER;
BEGIN
for rec in (select l.location_id,l.city,c.country_name from locations l inner join countries c on c.country_id=l.country_id where l.country_id=p_co_id order by l.location_id desc) LOOP
    IF (select count(e.employee_id) from departments d inner join employees e on e.department_id=d.department_id where d.location_id=rec.location_id) != 0 THEN
        DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name || ' - ' || rec.location_id || ' ' || rec.city);
    END IF;
    for rec2 in (select count(e.employee_id) as empCount, d.department_name from departments d inner join employees e on e.department_id=d.department_id where d.location_id = rec.location_id group by d.department_name) LOOP
        IF rec2.empCount != 0 THEN
            DBMS_OUTPUT.PUT_LINE(rec2.department_name || ': ' || rec2.empCount || ' werknemers');
        END IF;
    END LOOP;
END LOOP;
END get_emp_overview;
/

expected output: the same as given one,without "==> United States of America - 1600 South Brunswick"

errors:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/24     PLS-00103: Encountered the symbol "SELECT" when expecting one of
         the following:
         ( - + case mod new not null <an identifier>
         <a double-quoted delimited-identifier> <a bind variable>
         continue avg count current exists max min prior sql stddev
         sum variance execute forall merge time timestamp interval
         date <a string literal with character set specification>
         <a number> <a single-quoted SQL string> pipe
         <an alternatively-quoted string literal with character set
         specification>
         <an alternat


8/67     PLS-00103: Encountered the symbol "INNER" when expecting one of
         the following:
         , ; for group having intersect minus order start union where
         connect

Answer 1

You cannot have a query inside an if statement - the syntax just doesn't allow it. You have to query into a variable, and check that:

...
    FOR rec IN (
        select l.location_id, l.city, c.country_name
        from locations l
        inner join countries c on c.country_id = l.country_id
        where l.country_id = p_co_id
        order by l.location_id desc
    )
    LOOP
        select count(*)
        into v_emp_count
        from departments d
        inner join employees e on e.department_id = d.department_id
        where d.location_id = rec.location_id;

        IF v_emp_count = 0 THEN
            CONTINUE; -- to next iteration of cursor loop
        END IF;

        DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name
            || ' - ' || rec.location_id || ' ' || rec.city);
...

There are other ways to approach this. You could add an exists clause into your first cursor query, so you don't even see locations with no employees:

...
    FOR rec IN (
        select l.location_id, l.city, c.country_name
        from locations l
        inner join countries c on c.country_id = l.country_id
        where l.country_id = p_co_id
        and exists (
            select *
            from departments d
            inner join employees e on e.department_id = d.department_id
            where d.location_id = l.location_id
        )
        order by l.location_id desc
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name
            || ' - ' || rec.location_id || ' ' || rec.city);
...

You can also simplify your second loop by excluding locations at that point too, with a having clause:

create or replace procedure get_emp_overview
    (p_co_id locations.country_id%TYPE)
AS
BEGIN
    FOR rec IN (
        select l.location_id, l.city, c.country_name
        from locations l
        inner join countries c on c.country_id = l.country_id
        where l.country_id = p_co_id
        and exists (
            select *
            from departments d
            inner join employees e on e.department_id = d.department_id
            where d.location_id = l.location_id
        )
        order by l.location_id desc
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name
            || ' - ' || rec.location_id || ' ' || rec.city);

        FOR rec2 IN (
            select count(e.employee_id) as empCount, d.department_name
            from departments d
            inner join employees e on e.department_id = d.department_id
            where d.location_id = rec.location_id
            group by d.department_name
            having count(e.employee_id) > 0
        )
        LOOP
            DBMS_OUTPUT.PUT_LINE(rec2.department_name || ': ' || rec2.empCount || ' werknemers');
        END LOOP;
    END LOOP;
END get_emp_overview;
/

which gets output:

exec get_emp_overview('US');

==> United States of America - 1700 Seattle
Administration: 1 werknemers
Accounting: 2 werknemers
Purchasing: 6 werknemers
Executive: 3 werknemers
Finance: 6 werknemers
==> United States of America - 1500 South San Francisco
Shipping: 45 werknemers
==> United States of America - 1400 Southlake
IT: 5 werknemers


PL/SQL procedure successfully completed.

---

Your assignment may be to use nested loops; but you could also do this with a single loop, tracking whether you've seen this location before:

create or replace procedure get_emp_overview
    (p_co_id locations.country_id%TYPE)
AS
    l_last_location_id locations.location_id%TYPE;
BEGIN
    FOR rec IN (
        select l.location_id, l.city, c.country_name, d.department_name,
            count(e.employee_id) as empcount
        from locations l
        inner join countries c on c.country_id = l.country_id
        inner join departments d on d.location_id = l.location_id
        inner join employees e on e.department_id = d.department_id
        where l.country_id = p_co_id
        and exists (
            select *
            from departments d
            inner join employees e on e.department_id = d.department_id
            where d.location_id = l.location_id
        )
        group by l.location_id, l.city, c.country_name, d.department_name
        having count(e.employee_id) > 0
        order by l.location_id desc
    )
    LOOP
        IF l_last_location_id IS NULL OR rec.location_id != l_last_location_id THEN
            DBMS_OUTPUT.PUT_LINE('==> ' || rec.country_name
                || ' - ' || rec.location_id || ' ' || rec.city);
            l_last_location_id := rec.location_id;
        END IF;
        DBMS_OUTPUT.PUT_LINE(rec.department_name || ': ' || rec.empCount || ' werknemers');
    END LOOP;
END get_emp_overview;
/

which gets the same output.