function/procedure with dates

Hello,

I need a procedure/function/view where I get the result of a Query in which I have to work with the reference towards the current date.

The server is a Oracle 7.3

Underneath you find the Query as I wrote it to work in Delphi but I want it to run on the server and get the date from server.

input for the procedure

   :ID is the Id of the country in tbl_country

input which now needs to come from server (calculated) :day : today ( example : 19 if today is january 19) :dayofweek : number of day of the week ( monday=2) can be changed

Output
  All clients who need to be processed that day

SCRIPT:
  select distinct tbl_clients.id, tbl_Clients.Clientname, tbl_clients.clientok   from tbl_clients, tbl_country
 WHERE
(countryid=:id) and
(((tbl_clients.daymon = 1) AND (:dayofweek = 2)) OR
((tbl_clients.daytue = 1) AND (:dayofweek = 3)) OR
((tbl_clients.daywed = 1) AND (:dayofweek = 4)) OR
((tbl_clients.daythu = 1) AND (:dayofweek = 5)) OR
((tbl_clients.dayfri = 1) AND (:dayofweek = 6))) AND

       ((tbl_clients.week1 = 0 AND tbl_clients.week2 = 0 AND
        tbl_clients.week3 = 0 AND tbl_clients.week4 = 0 ) OR


        ((tbl_clients.week1 = 1 AND :day <= 6) OR


         (tbl_clients.week2 = 1 AND :day >= 7 AND
                           :day <= 13) OR
         (tbl_clients.week3 = 1 AND :day >= 14 AND
                           :day <= 20) OR
         (tbl_clients.week4 = 1 AND :day >= 21))
        ) AND


        (tbl_clients.priceclient = 0)


        and (tbl_country.id=tbl_clients.countryid)

Please send answers to Lvandenborre_at_puratos.be

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Jan 20 1999 - 04:15:01 CST