Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

ERROR: Invalid relational operator...comparing datesHelpful Member! 

AKSmith (Programmer) (OP)
20 Dec 10 14:09
Getting the error  ORA-00920: invalid relational operator
on the following query. What am I doing wrong?

CODE

(select 'Next PM' as pmtype, nvl(pm.location,asset.location) as location, pm.assetnum,
asset.description as assetdescr, pm.pmnum, pm.description, pm.nextdate as pmduedate, pm.jpnum,
jobplan.jpduration as estdur, nvl(jlhrs.elh,0) as estlabhrs, pm.priority as pmpriority, 'PM SCHEDULED' as wonum,
'PM SCHED' as status, pm.nextdate as statusdate, jobplan.laborcode as lead, pm.frequency, pm.frequnit, decode(pm.downtime, 0, 'No', 1, 'Yes') as downtime,
pm.crewid as team, asset.priority as assetpriority, pmseasons.startday, pmseasons.startmonth, pmseasons.endday,
pmseasons.endmonth, (pm.pmcounter + 1) as pmkntr, pm.jpseqinuse, 1 as iterator,
pm.route, pm.siteid
from pm left outer join jobplan
on (pm.jpnum = jobplan.jpnum and pm.siteid = jobplan.siteid)
left outer join asset
on (pm.assetnum = asset.assetnum and pm.siteid = asset.siteid)
left outer join pmseasons
on (pm.pmnum = pmseasons.pmnum and pm.siteid = pmseasons.siteid)
left outer join (select joblabor.jpnum as jpnum, joblabor.siteid as siteid,
sum((nvl(joblabor.laborhrs,0) * nvl(joblabor.quantity,0))) as elh from maximo.joblabor group by joblabor.jpnum,
joblabor.siteid)  jlhrs
on (jlhrs.jpnum = pm.jpnum and jlhrs.siteid = pm.siteid)
where + siteidParam and + locParam and + assetParam
and to_date(pm.nextdate) >= to_date('12/1/2010')
and to_date(pm.nextdate) <= to_date('12/31/2010')
)
union
(select 'Projected PM' as pmtype, nvl(pm.location,asset.location) as location, pm.assetnum,
asset.description as assetdescr, pm.pmnum, pm.description,
decode (pm.frequnit,'DAYS', pm.nextdate + pm.frequency * p.x, 'WEEKS', pm.nextdate + pm.frequency * p.x * 7,
        'MONTHS', add_months (pm.nextdate, pm.frequency * p.x),
        'YEARS', add_months (pm.nextdate,12 * pm.frequency * p.x), null) as pmduedate, pm.jpnum,
jobplan.jpduration as estdur, nvl(jlhrs.elh,0) as estlabhrs, pm.priority as pmpriority, 'PM SCHEDULED' as wonum,
'PM SCHED' as status, pm.nextdate as statusdate, jobplan.laborcode as lead, pm.frequency, pm.frequnit, pm.downtime,
pm.crewid as team, asset.priority as assetpriority, pmseasons.startday, pmseasons.startmonth, pmseasons.endday,
pmseasons.endmonth, (pm.pmcounter + p.x + 1) as pmkntr, pm.jpseqinuse,
p.x + 1 as iterator, pm.route, pm.siteid
from pm left outer join jobplan
on (pm.jpnum = jobplan.jpnum and pm.siteid = jobplan.siteid)
left outer join asset
on (pm.assetnum = asset.assetnum and pm.siteid = asset.siteid)
left outer join pmseasons
on (pm.pmnum = pmseasons.pmnum and pm.siteid = pmseasons.siteid)
left outer join (select joblabor.jpnum as jpnum, joblabor.siteid as siteid,
sum((nvl(joblabor.laborhrs,0) * nvl(joblabor.quantity,0))) as elh from maximo.joblabor group by joblabor.jpnum,
joblabor.siteid)  jlhrs
on (jlhrs.jpnum = pm.jpnum and jlhrs.siteid = pm.siteid),(select rownum as x from pm where rownum < 53) p
where + siteidParam and + locParam and + assetParam
and to_date(decode (pm.frequnit,'DAYS', pm.nextdate + pm.frequency * p.x, 'WEEKS', pm.nextdate + pm.frequency * p.x * 7,
        'MONTHS', add_months (pm.nextdate, pm.frequency * p.x),
        'YEARS', add_months (pm.nextdate,12 * pm.frequency * p.x), null)) >= to_date('12/1/2010')
and to_date(decode (pm.frequnit,'DAYS', pm.nextdate + pm.frequency * p.x, 'WEEKS', pm.nextdate + pm.frequency * p.x * 7,
        'MONTHS', add_months (pm.nextdate, pm.frequency * p.x),
        'YEARS', add_months (pm.nextdate,12 * pm.frequency * p.x), null)) <= to_date('12/31/2010')
)
order by pmduedate
Helpful Member!  SantaMufasa (TechnicalUser)
20 Dec 10 14:14
AK,

In an effort to help you, could you please post a highly abbreviated set of just the minimum code from above that generates your error?

Also, please post a DESCRIBE of the table(s) involved in your minimum-code set.

Thanks,

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

AKSmith (Programmer) (OP)
20 Dec 10 14:32
Mufasa,
I found an error above, sorry I know it's somewhat convoluted. Here's my other question. Why am I getting an error "not a valid month" on the following?

CODE

select pm.nextdate
from pm
where pm.nextdate >= '12/1/2010'
and pm.nextdate <= '12/31/2010'
SantaMufasa (TechnicalUser)
20 Dec 10 14:51
AK,

Oracle, by default, recognizes only one DATE format at a time. For example, here are some rows that I created in my copy of the PM table to illustrate your situation:

CODE

SQL> select nextdate from pm;

NEXTDATE
---------------
20-DEC-10
04-JAN-11
23-DEC-10
25-DEC-10
10-DEC-10
20-NOV-10
05-DEC-10

7 rows selected.
Notice that my default DATE format is "DD-MON-YY'. Notice that the format that you used for your WHERE clause is "MM/DD/YYYY". Oracle is telling you that your characters 4-6 (i.e., the default MON positions), "31/" are not valid month representations:

CODE

select pm.nextdate
  from pm
 where pm.nextdate >= '12/1/2010'
   and pm.nextdate <= '12/31/2010';
where pm.nextdate >= '12/1/2010'
                     *
ERROR at line 3:
ORA-01843: not a valid month
In the Oracle World, we consider it "good form" to compare DATE expressions to DATE expressions. Therefore, your code should read:

CODE

select pm.nextdate
  from pm
 where pm.nextdate >= to_date('12/1/2010','mm/dd/yyyy')
   and pm.nextdate <= to_date('12/31/2010','mm/dd/yyyy');

NEXTDATE
---------------
20-DEC-10
23-DEC-10
25-DEC-10
10-DEC-10
05-DEC-10
Another nice feature of Oracle WHERE clauses is the ability to use the BETWEEN operator:

CODE

select pm.nextdate
from pm
where pm.nextdate
      between to_date('12/1/2010','mm/dd/yyyy')
          and to_date('12/31/2010','mm/dd/yyyy');

NEXTDATE
---------
20-DEC-10
23-DEC-10
25-DEC-10
10-DEC-10
05-DEC-10
Let us know if this resolves your question.
 

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

KenCunningham (TechnicalUser)
21 Dec 10 4:46
Wow, Santa really is limbering up for the long haul on Friday night! Have a star to help you on your way!

The internet - allowing those who don't know what they're talking about to have their say.

SantaMufasa (TechnicalUser)
21 Dec 10 11:56
HoHoHo, Ken...Thank you very much. Merry Christmas !

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

AKSmith (Programmer) (OP)
22 Dec 10 13:55
Thanks for your replies. Yes, that did answer my (second) question.
dkyrtata (Programmer)
23 Dec 10 23:08
Looking at your WHERE condition I noticed this:

CODE

where + siteidParam and + locParam and + assetParam

can someone explain what the "+" signs mean? I have never seen this before
Turkbear (TechnicalUser)
23 Dec 10 23:47
Hi,
IIRC, to indicate an Outer join in Oracle ( before they changed it to be ANSI compliant, in V9 or 10 I think)
you used + signs to indicate the OUTER join like this query:

CODE

select s.AMOUNT, s.ID_DATE, s.ID_SELLER, s.ID_INVOICE,    NVL(n.AMOUNT, s.AMOUNT) amount        from STOCK s, NEW_STOCK n where s.ID_DATE = n.ID_DATE(+)    and s.ID_SELLER = n.ID_SELLER(+)   and s.ID_INVOICE = n.ID_INVOICE(+)   and s.ID_DOC = n.ID_DOC(+)   and s.ID_DATE = 1   and s.ID_SELLER = 'SELL1';

But I have not seen it used like the posted version.

 

profile

To Paraphrase:"The Help you get is proportional to the Help you give.."

dkyrtata (Programmer)
24 Dec 10 13:34
Turkbear

yes, you are correct. I use this style of outer join all the time. But the "+" signs in the posting must do something different. Otherwise how would the the query generate the "date error" It would have generated a syntax error instead - unless the actual query is different than what was posted.

Not only does the posting contain the odd looking "+" signs, but the fields do not even get compared using any relational operators - which leads me to think that something else is going on other than an outer join

Any other theories or do we just conclude that it is a mistyped outer join?
 
Turkbear (TechnicalUser)
24 Dec 10 16:37
Hi,
Could be just a mistyping or somehow it equates to
a WHERE EXISTS clause ( altho' I have no idea how that could be done.)

 

profile

To Paraphrase:"The Help you get is proportional to the Help you give.."

carp (MIS)
24 Dec 10 17:29
dkyrata -
No, you are correct - this is a WHERE clause that is poorly formed (and quite probably the source of the original "invalid relational operator" error).  
dkyrtata (Programmer)
25 Dec 10 11:29
Carp

Oh yes, I was negligent in seeing the original error message. I got focused on the error message that SantaMufasa demonstrated in his example. Of course the "date" error was probably the next message that popped up once AKSmith fixed the "relational operator" error. So SantaMufasa was a one step ahead on the diagnosis.

Ok, more clear now.
AKSmith (Programmer) (OP)
27 Dec 10 9:48
Yes, you found the original error. This query was assigned to a string variable as part of a piece of javascript. I'm building reports in Eclipse BIRT 2.3.2. And I was testing the SQL and neglected to remove some javascript variables that dynamically build the where clause. And how sad that y'all spent your holiday weekend poring over this. smile

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close