ERROR: Invalid relational operator...comparing dates 1

[AKSmith]

Getting the error ORA-00920: invalid relational operator
on the following query. What am I doing wrong?

(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

 

[SantaMufasa]

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,

Mufasa
(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]

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?

select pm.nextdate
from pm
where pm.nextdate >= '12/1/2010'
and pm.nextdate <= '12/31/2010'

 

[SantaMufasa]

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:

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:

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:

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:

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.


Mufasa
(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]

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]

HoHoHo, Ken...Thank you very much. Merry Christmas !

Mufasa
(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]

Thanks for your replies. Yes, that did answer my (second) question.

 

[dkyrtata]

Looking at your WHERE condition I noticed this:

where + siteidParam and + locParam and + assetParam

can someone explain what the "+" signs mean? I have never seen this before

 

[Turkbear]

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:

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.





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

 

[dkyrtata]

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]

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.)





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

 

[carp]

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]

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]

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.