-
Hi
I have a table like this
REG TIMESTAMP
--------- ---------
0 18-JUN-99
8537 10-SEP-99
69281 04-OCT-99
71222 05-OCT-99
73340 06-OCT-99
75139 07-OCT-99
76848 08-OCT-99
79014 09-OCT-99
80474 10-OCT-99
I want to write a PL/SQL program to check whether the REG between the 1 st and the 2nd row have date of 18- JUN -99 or not and then 2 and 3 rd row to have 10-SEP-99 . If some REG do not have that date then what date do they have
EX
0-8537 should have 18-JUN-99
69281-8537 should have 10-sep -99
if any discrepance then
REG timestamp
2345 - 20-jun-99
Please let me know the program for this
regards
-
clarification
Hi
I have a table with REG numbers and their dates .What I did was create a summary table of the main table which has all the details like this :
REG TIMESTAMP
--------- ---------
0 18-JUN-99
8537 10-SEP-99
69281 04-OCT-99
71222 05-OCT-99
73340 06-OCT-99
75139 07-OCT-99
76848 08-OCT-99
79014 09-OCT-99
80474 10-OCT-99
using select max(reg) trunc(timestamp) from table A
group by trunc(timestamp)
order by trunc(timestamp) ;
The table A has all REG numbers and dates along with it .
I need to write a program where the details of the REG number will be taken from table B . and run against table A to get the dates.Timestamp is the registration date of the user. so to ensure that between 0 and 8537 all users registered on 18-JUN-99.
Eg REG between 0 and 8536 should have 18-JUN-99 as timestamp if not then show those REG and timestamp and then for 8537 and 69281 to have 10-SEP-99 in table A .
Hope it is clear .
regards
-
Your group by select appears to make some assumptions that I don't believe hold true. Look at the following data:
INSERT INTO TABLE_X VALUES (1, '1-JAN-99');
INSERT INTO TABLE_X VALUES (2, '1-JAN-99');
INSERT INTO TABLE_X VALUES (3, '1-DEC-98');
INSERT INTO TABLE_X VALUES (4, '1-JAN-99');
INSERT INTO TABLE_X VALUES (5, '1-MAR-99');
INSERT INTO TABLE_X VALUES (6, '1-APR-99');
INSERT INTO TABLE_X VALUES (7, '1-APR-99');
INSERT INTO TABLE_X VALUES (8, '1-MAY-99');
INSERT INTO TABLE_X VALUES (9, '1-APR-99');
INSERT INTO TABLE_X VALUES (10,'1-APR-99');
INSERT INTO TABLE_X VALUES (11,'1-JUN-99');
INSERT INTO TABLE_X VALUES (12,'1-JUN-99');
I don't understand how you expect your query to work. If we have an entry where the date is erroneously in the past, like entry 3, your group by query will return:
3, '1-DEC-98'
4, '1-JAN-99'
Your subsequent query would then flag records 1 and 2 as the errors when entry 3 is actually in error
So what happens with entry 8, which erroneously has a date that is *newer* than those around it? Your group by query will return
5,'1-MAR-99'
10,'1-APR-99'
8 ,'1-MAY-99'
12,'1-JUN-99'
This will cause obvious issues down the line. Your subsequent query will return nonsensical results. Exactly which records would you expect to be flagged as errors in such a situation?
- Chris
-
Hi again
HI
We had a bug in our software which updated the user registration dates to a later date and I want to get thsoe dates and make it as original . So we got the max(reg) from
table with trunc(timestamp) and want to update the intervals.
Please let me know the program for it . The logic is fine for our environment
regards
-
select reg, timestamp
from mytable
where (reg < 8537 and timestamp <> '18-JUN-1999')
or ((reg between 8537 and 69281) and timestamp <>'10-SEP-1999');
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|