Home » SQL & PL/SQL » SQL & PL/SQL » problem with where condition including range of date?
problem with where condition including range of date? [message #303147] Thu, 28 February 2008 03:02 Go to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
I've a query to bring data whithin a specific period of time.
for example i've write:
(IND_TIME_YEAR,IND_TIME_MONTH) in ((2000,5) , (2001,5))

but the problem is in that way it brings only the data that match one of this value while when writing:
(IND_TIME_YEAR,IND_TIME_MONTH) between (2000,5) and (2001,5)
it gives me an error.

I'd like to know where is the problem?
Regards
Re: problem with where condition including range of date? [message #303149 is a reply to message #303147] Thu, 28 February 2008 03:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The problem is most likely this is not a valid syntax.

The error that was given might be a clue, but you didn't tell us what it was.

Also, post your version since behaviour might have changed between version.
Re: problem with where condition including range of date? [message #303157 is a reply to message #303147] Thu, 28 February 2008 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As this a newbie question it should be posted in newbie forum.
Read Not an EXPERT? Post in the NEWBIES forum, NOT here sticky and OraFAQ Forum Guide

Regards
Michel
Re: problem with where condition including range of date? [message #303180 is a reply to message #303149] Thu, 28 February 2008 04:35 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
I've oracle 10g relase 2 and the error message was:
ORA-01796: this operator cannot be used with lists

Regards
Re: problem with where condition including range of date? [message #303183 is a reply to message #303180] Thu, 28 February 2008 04:38 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So it seems you can't use the between operator this way.

One way to solve it would be to convert the year / month to some sort of number and apply a between on that :


WHERE ( IND_TIME_YEAR * 100 ) + IND_TIME_MONTH between 200005 and 200105


Or convert the year/month into a date and apply the between on that :
WHERE To_Date(
         To_Char(IND_TIME_YEAR,'fm0000') || To_Char(IND_TIME_MONTH,'fm00')
      ,'yyyymm')
      BETWEEN To_Date('200005','yyyymm') AND To_Date('200105','yyyymm')


(Both untested, so there might be typos in them)

Hope that helps
Previous Topic: pivoting numbers and bring the names instead of numbers
Next Topic: Multiple INSERTS
Goto Forum:
  


Current Time: Sat Dec 03 16:12:45 CST 2016

Total time taken to generate the page: 0.09000 seconds