Using an if statment into a cursor declaration.

Question

Hi.

Im using a cursor to go over some data but I need to declare diferents sql statments according to a parameter. The problem seems to be that Im no able to use if statmets into this declaratios:

DECLARE
   CURSOR c IS SELECT * FROM TRAFICO
              IF TipoConsulta = 'mes' then
          BEGIN   
             WHERE TO_CHAR(FECHA_BUSQUEDA, 'MM-YYYY') =To_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM-YYYY')                 
          ELSE                  
              WHERE FECHA_BUSQUEDA >=  SYSDATE -7                  
          END IF;
          ORDER BY ID_TRAFICO;
begin   
  FOR r IN C LOOP
      BEGIN
          Utl_File.Put_Line(Arch, r.ID_TRAFICO );
          i:=i+1;          
      END;
  END LOOP;
END;

I just need to change the sql statment using an if.

How do I do this??

TNKS

Answer 1

Probably the CASE :EXPRESSION WHEN STATEMENT will be helpful for your situation and if not you may create a simple string and execute it through calling EXEC function

Answer 2

I think you might want to try the following:

SELECT * FROM TRAFICO
WHERE (TipoConsulta = 'mes'
         and TO_CHAR(FECHA_BUSQUEDA, 'MM-YYYY') =To_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM-YYYY'))
   or (TipoConsulta <> 'mes'
         and FECHA_BUSQUEDA >=  SYSDATE -7)
ORDER BY ID_TRAFICO;

Answer 3

try using a dynamic cursor REF CURSOR (i'm assuming TripoConsulta is a variable and not one of the predicates of the query):

declare
type some_cursor is ref cursor;
my_cursor some_cursor;
my_rec trafico%rowtype;

begin

  if TripoConsulta = 'mes' then
    open my_cursor for select * from trafico where ...;
  else 
    open my_cursor for select * from trafico where ...;
  end if;
  loop
    fetch my_cur into my_rec;
    exit when my_cur%notfound;
    Utl_File.Put_Line(my_rec.Arch, my_cur.ID_TRAFICO );
  end loop;
  close my_cursor;
end;

Clearly, i don't know what the variable all mean so i had to make a number of assumptions, but i'm thinking this is what you meant. I also don't guaranetee the syntax since i haven't actually tested it.

Answer 4

This uses implicit cursors, rather than splinter an explicit cursor with an OR clause or use dynamic SQL:

CASE TipoConsulta 
  WHEN 'mes' THEN
    FOR b IN (SELECT a.*
                FROM TRAFICO a
               WHERE TO_CHAR(a.fecha_busqueda, 'MM-YYYY') = To_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM-YYYY')
            ORDER BY a.id_trafico)
      UTL_FILE.PUT_LINE(Arch, b.id_trafico);
    END LOOP;
  ELSE
    FOR d IN (SELECT c.*
                FROM TRAFICO c
               WHERE c.fecha_busqueda >= SYSDATE - 7
            ORDER BY c.id_trafico)
      UTL_FILE.PUT_LINE(Arch, d.id_trafico);
    END LOOP;
END CASE;

This also uses a PLSQL CASE expression, rather than the ANSI CASE expression -- you can tell by the use of END CASE, when ANSI CASE just uses END.

It doesn't appear that you posted the entire query - I don't know where arch is coming from, nor the need for the incrementing variable i... Or why you've got SELECT * FROM TRAFICO but only using the id_trafico column...