Home » SQL & PL/SQL » SQL & PL/SQL » Operating with columns within a table (Oracle 11g 11.2.0.2.0)
Operating with columns within a table [message #662858] Sun, 14 May 2017 03:57 Go to next message
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 #662859 is a reply to message #662858] Sun, 14 May 2017 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no way, Oracle does not natively support constraint between different rows.
You have to create your own procedure to insert or update rows that will check this constraint is verified.

Re: Operating with columns within a table [message #662861 is a reply to message #662859] Sun, 14 May 2017 04:25 Go to previous messageGo to next message
Cirov
Messages: 13
Registered: May 2017
Junior Member
Hi Michel, thanks for the reply.

That wouldn't be to update the table but to retrieve the data. I'd need to get a list of people who have made the trip to the same places in the last 25 days.
Re: Operating with columns within a table [message #662862 is a reply to message #662861] Sun, 14 May 2017 04:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I'm trying all sorts of combinations but I can't seem to find the way.
What code have you tried? A correlated subquery would be one approach.
Re: Operating with columns within a table [message #662864 is a reply to message #662862] Sun, 14 May 2017 05:39 Go to previous messageGo to next message
Cirov
Messages: 13
Registered: May 2017
Junior Member
So far I have an inner join:

SELECT  a.personid , a.datetrip, a.destination, b.personid , b.datetrip, b.destination,
FROM    TRIPS a
JOIN    TRIPS b
ON      a.personid = b.personid
WHERE a.destination = b.destination
AND a.datetrip <> b.datetrip

This way I'm getting a table with every person, and the different dates in which he/she travelled to the same place. What I'd like to know is how can I return only the trips that are 25 days apart.
Re: Operating with columns within a table [message #662865 is a reply to message #662864] Sun, 14 May 2017 05:48 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can you change the predicate? Something like

abs(trunc(a.datetrip) - trunc(b.datetrip)) = 25

if you want a better answer, you'll need to provide the CREATE TABLE statement and some INSERTs of sample data


Re: Operating with columns within a table [message #662867 is a reply to message #662862] Sun, 14 May 2017 06:08 Go to previous messageGo to next message
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 #662868 is a reply to message #662867] Sun, 14 May 2017 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Operating with columns within a table [message #662873 is a reply to message #662867] Sun, 14 May 2017 08:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #662949 is a reply to message #662873] Wed, 17 May 2017 05:25 Go to previous messageGo to next message
Cirov
Messages: 13
Registered: May 2017
Junior Member
Thanks everyone for the input, i've finally figured out how to get the dates using the following formula:

WHERE trunc(date2) - to_date(date1) <= 25

[Updated on: Wed, 17 May 2017 05:25]

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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #662951 is a reply to message #662950] Wed, 17 May 2017 06:11 Go to previous messageGo to next message
Cirov
Messages: 13
Registered: May 2017
Junior Member

Changed to trunc, yes I'm getting the same result. Thanks!
Re: Operating with columns within a table [message #662953 is a reply to message #662951] Wed, 17 May 2017 06:33 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Cirov wrote on Wed, 17 May 2017 06:11

Changed to trunc, yes I'm getting the same result. Thanks!
what about eliminating the use of to_date on something that is already a DATE data type? Did you do that as well? If not, you should. No, you MUST.
Re: Operating with columns within a table [message #662954 is a reply to message #662951] Wed, 17 May 2017 06:39 Go to previous messageGo to next message
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 #662955 is a reply to message #662954] Wed, 17 May 2017 06:41 Go to previous messageGo to next message
Cirov
Messages: 13
Registered: May 2017
Junior Member

Thanks for your help guys, I solved it already.
Re: Operating with columns within a table [message #662956 is a reply to message #662955] Wed, 17 May 2017 06:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Cirov wrote on Wed, 17 May 2017 04:41

Thanks for your help guys, I solved it already.
so post your solution here to benefit others
Re: Operating with columns within a table [message #662957 is a reply to message #662956] Wed, 17 May 2017 06:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 #662961 is a reply to message #662960] Wed, 17 May 2017 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Out of curiosity, what is your nls_date_format set to?
Re: Operating with columns within a table [message #662962 is a reply to message #662961] Wed, 17 May 2017 08:13 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Cookie, I set it to some crazy value just to prove a point. If you really must know, I set it as:

alter session set nls_date_format='month day yyyy';

Re: Operating with columns within a table [message #662963 is a reply to message #662957] Wed, 17 May 2017 08:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #662965 is a reply to message #662964] Wed, 17 May 2017 08:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Worth remembering that format for definitively proving to people that to_date(date) is a bad idea.
Though sods law dictates that when I next try it the current day will be the same as the day of the 1st.
Re: Operating with columns within a table [message #662966 is a reply to message #662961] Wed, 17 May 2017 08:34 Go to previous messageGo to next message
Cirov
Messages: 13
Registered: May 2017
Junior Member
cookiemonster wrote on Wed, 17 May 2017 08:08
Out of curiosity, what is your nls_date_format set to?
It's DD/MM/RR

I've changed to_date to trunc and it works. I'll leave it like that, but I don't have the time to make further changes, I'm working in another problem and the delivery date is tomorrow.
Re: Operating with columns within a table [message #662967 is a reply to message #662966] Wed, 17 May 2017 08:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Two 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!
Re: Operating with columns within a table [message #662968 is a reply to message #662966] Wed, 17 May 2017 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Assuming my scenario about multiple inspections can happen then you better find the time, cause what you've coded wont work. Try Solomon's solution.
Re: Operating with columns within a table [message #662989 is a reply to message #662967] Thu, 18 May 2017 06:41 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
BlackSwan wrote on Wed, 17 May 2017 08:40
Two 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.
Previous Topic: snapshot too old
Next Topic: Procedure not functioning properly
Goto Forum:
  


Current Time: Fri Mar 29 07:09:45 CDT 2024