Reputation: 23

count with union query not works

I'm doing a loop to can see if exits some register in a query with union all. I've done these:

DECLARE
COUNT INTEGER;
BEGIN
FOR I IN (SELECT CALENDAR FROM DF_CALENDAR) LOOP
    DBMS_OUTPUT.PUT_LINE(I.CALENDAR);
        SELECT COUNT(CONF_CAL) into CUENTA FROM (
                SELECT CONF_CAL from JOB j
                where CONF_CAL IS NOT NULL 
                UNION
                select DAYS_CAL from  JOB j
                where  DAYS_CAL  IS NOT NULL 
                UNION
                select weeks_cal from  JOB j
                where  weeks_cal  IS NOT NULL) WHERE CONF_CAL='I.CALENDAR';

                DBMS_OUTPUT.PUT_LINE(COUNT);
                DBMS_OUTPUT.PUT_LINE('CALENDAR ...'|| I.CALENDAR || ' COUNT NUMBER ' || COUNT);
END LOOP;
END;

Allwas the count variable shows me 0 but when I do:

SELECT COUNT(CONF_CAL) FROM (
                SELECT CONF_CAL from JOB j
                where CONF_CAL IS NOT NULL 
                UNION
                select DAYS_CAL from  JOB j
                where  DAYS_CAL  IS NOT NULL 
                UNION
                select weeks_cal from  JOB j
                where  weeks_cal  IS NOT NULL) WHERE CONF_CAL='ALLDAYS'

it shows me count(conf_cal) = 1.

I can't understand why with loop dont works. Someone could help please?

Upvotes: 2

Answers (3)

Boneist

Reputation: 23588

Why not simply:

SELECT dfc.calendar,
       COUNT(cc.conf_cal) count_cal
FROM   df_calendar dfc
       LEFT OUTER JOIN (SELECT conf_cal
                        FROM   job
                        WHERE  conf_cal IS NOT NULL
                        UNION
                        SELECT days_cal
                        FROM   job
                        WHERE  days_cal IS NOT NULL
                        UNION
                        SELECT weeks_cal
                        FROM   job
                        WHERE  weeks_cal IS NOT NULL) cc
         ON (dfc.calendar = cc.conf_cal)
GROUP BY dfc.calendar;

Upvotes: 0