CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Diablo
Diablo

Reputation: 35

How do I compare a date from one table with the date present in another table?

I need to generate a report every day for exception which have occurred today.

I have a column "Exception Details" where I store the details of the exception along with the date on which it occurred.

I have another table where i store my current date which changes everyday.

I need to compare these two dates and if they match then i am generating a CSV report.

I have managed to generate the reports by manually by adding the dates in my query but I would be automating it so i need a query which would automatically pick up the current date from my table and compare it with date present in the "Excepton Details".

So far i have managed to write below query:

SELECT * FROM EXCEPTION
WHERE EXP_STAT  = 'OPEN'
AND SUBSTR(EXCEPTION_DETAILS ,INSTR(EXCEPTION_DETAILS,'2014-01-22')) = '2014-01-22'

I need to change this so that I can just Pick up whatever date is present in "Exception Details" and compare it with the date in another table and get the records based on that.

Upvotes: 1

Views: 624

Answers (5)

Diablo
Diablo

Reputation: 35

I have found the solution to it using ReGEX:

SELECT * FROM EXCEPTION WHERE EXP_STAT = 'OPEN' AND REGEXP_SUBSTR(EXCEPTION_DETAILS, '(19|20)\d\d- -.- -.') = (select to_char(date, 'YYYY-MM-DD') from calendar_table where date_type='CURRENT')

Upvotes: 0

San
San

Reputation: 4538

You don't need to involve so many functions, one instr is enough:

   SELECT *
     FROM EXCEPTION
    WHERE EXP_STAT  = 'OPEN'
      AND INSTR(EXCEPTION_DETAILS,'2014-01-22') > 0;

To fetch using another table, assuming that you want to compare with the date_field column of some_date_table table

SELECT e.*
  FROM EXCEPTION e
 inner join some_date_table s
 WHERE e.EXP_STAT  = 'OPEN'
   AND INSTR(e.EXCEPTION_DETAILS,to_char(s.date_field, 'YYYY-MM-DD')) > 0;

Upvotes: 0

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2715

Just wondering, why are you actually storing current date on another table? why are you not simply comparing with TRUNC(SYSDATE)?

Anyways,

SELECT * FROM EXCEPTION, B
WHERE EXP_STAT  = 'OPEN'
AND TO_DATE(SUBSTR(EXCEPTION_DETAILS ,INSTR(EXCEPTION_DETAILS,'2014-01-22')),'DD-MM-YY') = TRUNC(B.CURRENT_DATE);

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

SELECT tableA.* FROM tableA, tableB
WHERE tableA.EXP_STAT  = 'OPEN'
AND SUBSTR(EXCEPTION_DETAILS ,INSTR(EXCEPTION_DETAILS,to_char(tableB.current_date,'YYYY-MM-DD')),12) = to_char(tableB.current_date,'YYYY-MM-DD');

Assuming your date table , don't have repetitive dates.

Upvotes: 1

smn_onrocks
smn_onrocks

Reputation: 1342

I have change your query please try it out it must help you

SELECT * FROM EXCEPTION WHERE EXP_STAT = 'OPEN' AND SUBSTR(EXCEPTION_DETAILS ,INSTR(EXCEPTION_DETAILS,select to_char(max(dt_date),'yyyy-mm-dd') from table2)) = (select to_char(max(dt_date),'yyyy-mm-dd') from table2);

Upvotes: 0

Related Questions