CODEWITHSUNDEEP
oracle-database
Jeff Cook
Jeff Cook

Reputation: 8804

SQL Oracle - How to make query dynamic to accept multiple days to calculate the expiration

I want to dynamically create query to accept parameters dynamically to send notifications 1,3,5,10,15 and 20 days the password is going expire. I already went through link how to get the 30 days before date from Todays Date.

These date values must be configurable and should be passed through code to method.

I've developed a query, but is there any better ways to pass this values? How to dynamically accept the days value?

SELECT * FROM PASS_EXPIRE
WHERE TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 1) OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 3) OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 5)
OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 7) OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 15) OR TRUNC(EXPIRY_DT) = TRUNC(SYSDATE + 20);

Upvotes: 0

Views: 154

Answers (3)

MT0
MT0

Reputation: 168311

You can use:

SELECT *
FROM   PASS_EXPIRE
WHERE  TRUNC(EXPIRY_DT) IN (
         WITH days (day) AS (
           SELECT  1 FROM DUAL UNION ALL
           SELECT  3 FROM DUAL UNION ALL
           SELECT  5 FROM DUAL UNION ALL
           SELECT  7 FROM DUAL UNION ALL
           SELECT 15 FROM DUAL
         )
         SELECT TRUNC(SYSDATE) + day
         FROM   days
       )

Or, use a collection (which could be passed as a bind parameter):

SELECT *
FROM   PASS_EXPIRE
WHERE  TRUNC(EXPIRY_DT) IN (
         SELECT TRUNC(SYSDATE) + COLUMN_VALUE
         FROM   TABLE(SYS.ODCINUMBERLIST(1, 3, 5, 7, 15))
       )

or you can JOIN (and, again, the collection could be passed to the query as a bind variable):

SELECT p.*
FROM   PASS_EXPIRE p
       INNER JOIN TABLE(SYS.ODCINUMBERLIST(1, 3, 5, 7, 15)) t
       ON (   p.expiry_dt >= TRUNC(SYSDATE) + t.COLUMN_VALUE
          AND p.expiry_dt <  TRUNC(SYSDATE) + t.COLUMN_VALUE + 1 )

Note: This query would allow you to use an index on expiry_dt; in contrast, comparing on TRUNC(expiry_dt) would not allow an index to be used as Oracle would need a separate function-based index on TRUNC(expiry_dt).

db<>fiddle here

Upvotes: 3

ekochergin
ekochergin

Reputation: 4129

The idea I have is to create another table with days to expire:

days
1
3
5

and so on

and then just use cross join for retrieval passwords that are going to expire

select 'password expires in ' || ds.days || case when ds.days = 1 then ' day' else ' days' end
  from PASS_EXPIRE pe
 cross join days ds
 where pe.expiry_dt = (trunc(sysdate) - ds.days);

Thus, all you need to do in the application is to set up a dialog to maintain that table

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231771

I'd probably declare a collection, pass that collection to your procedure, and then use the member of operator to return only the rows from the table that match an element from the collection

create type date_tbl is table of date;

declare
  -- You'd pass in l_dates
  l_dates date_tbl := date_tbl( trunc(sysdate+1), trunc(sysdate+3), trunc(sysdate+5),
                                trunc(sysdate+10), trunc(sysdate+15), trunc(sysdate+20));
begin
  -- Your query goes here.  Use the `member of` function to restrict yourself to
  -- rows from the table that are in the collection
  for i in (select d.*
              from (select trunc(sysdate) + level dt
                      from dual
                   connect by level <= 30) d
             where d.dt member of l_dates )
  loop
    dbms_output.put_line( i.dt );
   end loop;
end;

Prints out (when run on 2021-09-02)

03-SEP-21
05-SEP-21
07-SEP-21
12-SEP-21
17-SEP-21
22-SEP-21

See this fiddle

Upvotes: 1

Related Questions