Search code examples
sqloracleplsqloracleformsoracle8

Get exactly inserted date_from and date_to

I am working in an application in which I need to filter policy by date. For example if user enter date_from:'21-jul-2017' and date_to:'21-jul-2019' it should display all policies which were created in this period.

So far what I did: 

cursor o1 is 
 select substr(tarifa,1,2), count(*)
    from   pol p, uvod u, doppov d
    WHERE (izdavanje >=:prebacivanje.od)  AND (izdavanje<=:prebacivanje.do)
    and izdavanje>='01-jul-07'
    and p.orgjed = u.sorgz (+)
    and DATUM_PREKIDA is not null
    and p.polica=d.polica and d.pov_dopl='P'
    and d.status='F'
    and cisti_ao(p.polica)!=0 
    group by substr(tarifa,1,2);

:prebacivanje.od and :prebacivanje.do are date_from and date_to. Both are DATE fields in the database.

So I need to modify this query to return inserted date_from and date_to depending of what user entered.

enter image description here

Solution

  • As this is a cursor which exists somewhere in a form, that code should probably be executed somehow. Is it when you push a button, or what?

    Anyway: although you never said what the problem is (do you get an error? If so, which one? ORA-xxxxx, FRM-xxxxx?) (my guess is that query doesn't return anything), it seems that parameters bother you.

    Query - as is - is correctly written. Those columns' datatype is DATE - that's what items' datatype should be as well. If it is not (but a CHAR), you'll have to convert it to DATE using TO_DATE, e.g. 

     WHERE izdavanje >= to_date(:prebacivanje.od, 'dd-mon-yy')  
   AND izdavanje <= to_date(:prebacivanje.do, 'dd-mon-yy')
   AND izdavanje >= to_date('01-jul-07'     , 'dd-mon-yy')

    In order to make sure which format mask to use, display items' values at the beginning of that PL/SQL procedure, using the MESSAGE built-in:

    declare
  cursor o1 is ...
begin
  message('od = ' || :prebacivanje.od);
  message('do = ' || :prebacivanje.do);

  ... the rest of your code goes here
end;

    Generally speaking, your best option is to make sure that datatypes match; otherwise, you have to take care about conversion or (worse) rely on implicit conversion Oracle will try to perform; sometimes it'll succeed, sometimes not.