sum of column with dates selected from other table against dates give me wrong answer when compare dates

user_15377265 0
Viewed 36

I write a code which gives me wrong answer what it do it compare dates from two tables and checks Sunday from it and return me sum of item column it checks Sunday its work fine but when it checks other table date and compare with its own dates and then give sum is wrong please help on this thanks You can also checks on dbfiddle here.

DECLARE
a number;
B DATE;
CURSOR GZDT IS
SELECT GAZZETED_DATE  FROM GAZZETED_DAYS
WHERE GAZZETED_DATE between date '2021-01-01' and date '2021-01-20';

begin
    OPEN GZDT;
    LOOP 
        FETCH GZDT INTO B;      
         EXIT WHEN GZDT%notfound;
    END LOOP;
    CLOSE GZDT;
SELECT  SUM(ITEM) into a from pay_in_out
  where TO_CHAR(att_date,'DY', 'NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SUN')
   AND ATT_DATE != B
  AND att_date between date '2021-01-01' and date '2021-01-20'  
  AND EMP_CODE = 100;
  dbms_output.put_line('the sum of item is :'||a) ;
  
end;
/
oracle11g plsql oracle
1 Answers

From the example data in your dbfiddle, it looks like you want to count the values for all the days which do not appear in the GAZZETED_DAYS table.

Unfortunately, your B variable only holds a single date value, and your table has 5 example values. As a result, your cursor LOOP overwrites B 5 times, ending up with only 26-JAN-21.

If you want to check against all the values in the table, a NOT IN clause is probably an easier solution than a cursor and a variable.

DECLARE
  a number;

begin

SELECT  SUM(ITEM) into a from pay_in_out
  where 
   ATT_DATE NOT IN 
     (SELECT GAZZETED_DATE FROM GAZZETED_DAYS
      WHERE GAZZETED_DATE between date '2021-01-01' and date '2021-01-20')
  AND att_date between date '2021-01-01' and date '2021-01-20'  
  AND EMP_CODE = 100;
  dbms_output.put_line('the sum of item is :'||a) ;
  
end;
/
user_30618520
Related