Operating with columns within a table [message #662858] |
Sun, 14 May 2017 03:57 |
|
Cirov
Messages: 13 Registered: May 2017
|
Junior Member |
|
|
Hi, I have a table (TRIPS) with the following columns (among others):
PERSONID NOT NULL CHAR(9 CHAR)
DESTINATION NOT NULL CHAR(15 CHAR)
DATETRIP NOT NULL DATE
A particular person might have made a trip to the same place in the last 25 days (person and destination in the table would be the same, datetrip must be 25 days apart). I'm trying all sorts of combinations but I can't seem to find the way. Any tips on this would be very helpful.
|
|
|
|
|
|
|
|
Re: Operating with columns within a table [message #662867 is a reply to message #662862] |
Sun, 14 May 2017 06:08 |
|
Cirov
Messages: 13 Registered: May 2017
|
Junior Member |
|
|
I think I'm getting close:
SELECT person1, date1, dest2, person2, date2, dest2 FROM (SELECT a.personid AS person1 , a.datetrip AS date1, a.destination AS place1,
b.personid AS person2, b.datetrip AS date2, b.destination AS dest2
FROM trips a
JOIN trips B
ON a.personid = b.personid
WHERE a.destination = b.destination
AND a.datetrip <> b.datetrip
)
WHERE ROUND(TO_NUMBER(TO_DATE(date2,'DD/MM/YY')-
TO_DATE(date1,'DD/MM/YY'))) < 4
Now I'm getting a way to compare the dates within both trips, this one is trips that are 4 years apart, how can I get dates that are 25 days apart?
|
|
|
|
Re: Operating with columns within a table [message #662873 is a reply to message #662867] |
Sun, 14 May 2017 08:58 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
1. Date arithmetic uses day as unit of measure.
2. No need for self join. You can use analytic LAG/LEAD or, if you are on 12C match_recognize.
And TO_DATE(date2,'DD/MM/YY') is very bad. You are passing date to TO_DATE function which expects a string. Therefore, oracle implicitly converts date2 to string using default date format and then converts it back to date. It will fail if default date format is, for example, yyyy/mm/dd:
SQL> alter session set nls_date_format='yyyy/mm/dd';
Session altered.
SQL> select to_date(date '2017-05-12','dd/mm/yy') from dual;
select to_date(date '2017-05-12','dd/mm/yy') from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL>
Or worse, you can get wrong results:
SQL> alter session set nls_date_format='mm/dd/yy';
Session altered.
SQL> select to_date(date '2017-05-12','dd/mm/yy') from dual;
TO_DATE(
--------
12/05/17
SQL>
As you can see, May 12 became December 5 since (based on default date format of mm/dd/yy) date was implicitly converted to string with month first and day second while 'dd/mm/yy' interprets that string as day first and month second.
SY.
[Updated on: Sun, 14 May 2017 08:59] Report message to a moderator
|
|
|
|
Re: Operating with columns within a table [message #662950 is a reply to message #662949] |
Wed, 17 May 2017 05:42 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Never, ever use to_date on a date. to_date doesn't accept a date parameter, so when you do that oracle implicitly converts it to a string first.
So that's equivalent to
to_date(to_char(date, '<sessions nls_date_format>'), '<sessions nls_date_format>')
The result of that will vary depending on what the nls_date_format is set to.
You presumably want to use trunc like you did for the other date.
|
|
|
|
|
Re: Operating with columns within a table [message #662954 is a reply to message #662951] |
Wed, 17 May 2017 06:39 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Your close, if you want the trips that are exactly 25 days apart then use
SELECT A.Personid Person1,
A.Datetrip Date1,
A.Destination Place1,
B.Personid Person2,
B.Datetrip Date2,
B.Destination Dest2
FROM Trips A CROSS JOIN Trips B
WHERE A.Personid = B.Personid
AND A.Destination = B.Destination
AND TRUNC (A.Datetrip) - TRUNC (B.Datetrip) = 25
If your datetrip column is indexed and whole dates then get rid of the TRUNC calls
[Updated on: Wed, 17 May 2017 06:40] Report message to a moderator
|
|
|
|
|
Re: Operating with columns within a table [message #662957 is a reply to message #662956] |
Wed, 17 May 2017 06:58 |
|
Cirov
Messages: 13 Registered: May 2017
|
Junior Member |
|
|
The actual query was on a different database, I posted a different example because I wanted to know how to do it and then apply it to my case. Here it is FWIW:
SELECT vehicle, personId, name, surname, buydate, date1, result1, date2, result2
FROM (SELECT p.name AS name, p.surname AS surname, bu.person AS personId,
a.vehicleplate AS vehicle, max(a.dateinspec) AS date1, a.resultinspec AS result1, b.vehicleplate AS vehicle2,
max(b.dateinspec) AS date2, b.resultinspec AS result2, bu.lastowner AS lastowner, bu.buydate AS buydate
FROM itv a, itv b, buys bu, person p
WHERE bu.person = p.id AND
bu.vehicleplate = a.vehicleplate AND
a.vehicleplate = b.vehicleplate AND
a.resultinspec <> 'OK' AND b.resultinspec = 'OK' AND
b.dateinspec > a.dateinspec
GROUP BY a.vehicleplate ,a.resultinspec, bu.buydate, b.vehicleplate, b.resultinspec, bu.person, p.name, p.surname, bu.lastowner
)
WHERE trunc(date2) - to_date(date1) < 25
The request gets all cars that have successfully passed a vehicle inspection 25 days after having failed it.
|
|
|
Re: Operating with columns within a table [message #662959 is a reply to message #662957] |
Wed, 17 May 2017 07:09 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
And it is wrong for the reasons I already stated.
It may work right now but as soon as someone changes the nls_date_format it'll start giving different results.
Use trunc.
Also have a look at the HAVING clause.
I also don't think max is what you want there.
Say have a not OK inspection followed by an OK inspection 20 days later followed by another OK inspection a year later. Your query won't pick that first pair up, I suspect you want it to.
|
|
|
Re: Operating with columns within a table [message #662960 is a reply to message #662957] |
Wed, 17 May 2017 08:06 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Cirov wrote on Wed, 17 May 2017 07:58
WHERE trunc(date2) - to_date(date1) < 25
TO_DATE requires a format mask. This goes along the lines with what cookiemonster just mentioned.
SQL> select 1 from dual where trunc(sysdate)=to_date(sysdate)
2 /
select 1 from dual where trunc(sysdate)=to_date(sysdate)
*
ERROR at line 1:
ORA-01835: day of week conflicts with Julian date
SQL> select 1 from dual where trunc(sysdate)=to_date('12/31/2016');
select 1 from dual where trunc(sysdate)=to_date('12/31/2016')
*
ERROR at line 1:
ORA-01843: not a valid month
|
|
|
|
|
Re: Operating with columns within a table [message #662963 is a reply to message #662957] |
Wed, 17 May 2017 08:18 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Cirov wrote on Wed, 17 May 2017 07:58
The request gets all cars that have successfully passed a vehicle inspection 25 days after having failed it.
No need to group by. Use analytic LEAD/LAG. Something like:
WITH t AS (
SELECT p.name AS name,
p.surname AS surname,
bu.person AS personId,
a.vehicleplate AS vehicle,
a.dateinspec AS date1,
a.resultinspec AS result1,
lead(a.dateinspec) over(partition by a.vehicleplateorder by a.dateinspec) AS date2,
lead(a.resultinspec) over(partition by a.vehicleplateorder by a.dateinspec) AS result2,
bu.lastowner AS lastowner,
bu.buydate AS buydate
FROM itv a,
buys bu,
person p
WHERE bu.person = p.id
AND bu.vehicleplate = a.vehicleplate
)
SELECT vehicle,
personId,
name,
surname,
buydate,
date1,
result1,
date2,
result2
FROM t
WHERE result1 <> 'OK'
AND result2 = 'OK'
AND date1 > date2 - 25
/
SY.
[Updated on: Wed, 17 May 2017 08:35] Report message to a moderator
|
|
|
Re: Operating with columns within a table [message #662964 is a reply to message #662962] |
Wed, 17 May 2017 08:23 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Didn't occur to me there was a format mask that would cause oracle to throw an error on to_date(some date). I just thought it'd work as an unpredictable trunc, where the level it truncates to depends on the current nls_date_format.
What this means of course is that are certain formats that'll throw an error on:
to_date('some text string', 'date format that does match the text string')
In the case of 'month day yyyy' it seems that because you haven't supplied a DD value oracle is going with the default for that - 1 - and so if the day isn't the same day of the week as the day of the week of the 1st of that month (don't like that sentence, too long), it'll have no idea what DD you do mean and throw that error.
|
|
|
|
|
|
|
Re: Operating with columns within a table [message #662989 is a reply to message #662967] |
Thu, 18 May 2017 06:41 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
BlackSwan wrote on Wed, 17 May 2017 08:40Two digit YEAR is evil.
Did you learning nothing from Y2K issue?
'10-11-12'
Which is correct DATE below for string above?
Oct. 11 2012 'MM-DD-RR'
Nov. 10 2012 'DD-MM-RR'
Nov. 12 2010 'RR-MM-DD'
Dec. 11 2010 'RR-DD-MM'
Oct. 12 2011 'MM-RR-DD'
Dec. 10 2011 'DD-RR-MM'
any or all are valid dates; DEPENDING upon which mask is applied to the string!
And to follow on, the RR and RRRR formats were simply band-aids to buy some time on Y2k remediation. They were never intended to still be in use 17 years later. Their use now is simply another flag that the developer has not yet grasped the concept of DATE vs CHARACTER representation of DATE.
|
|
|