Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only

  • 1.  SQL comments in WHERE clause: "Invalid condition"

    Posted Sat June 12, 2021 04:35 PM
    Edited by System Admin Wed March 22, 2023 11:52 AM
    MAM 7.6.1.2; Oracle 19c:

    I thought I'd pass along a hard-won lesson to anyone that might find it helpful:

    Scenario:

    I wrote a complex query in Toad. I wanted to use the query in Maximo, so I wrapped it in a subquery/WHERE clause and copy/pasted it into the Maximo WHERE clause window.

    classstructureid in 
(
select
    classstructureid
--    description,
--    class_rowstamp,
--    max(starttime) as estimated_last_edit_date
from
    (
    select 
        a.classstructureid,
        a.description,
        a.rowstamp as class_rowstamp,
        b.rowstamp as cron_rowstamp,
        b.starttime
    from   
        (select classstructureid, description, cast(rowstamp as number) as rowstamp from maximo.classstructure) a
    cross join
        (select cast(rowstamp as number) as rowstamp, starttime from maximo.crontaskhistory where crontaskname = 'PMWoGenCronTask') b 
    where
        b.rowstamp < a.rowstamp
    )
group by
    classstructureid,
    description,
    class_rowstamp
having
    trunc(max(starttime)) = to_date('05/04/2021', 'MM-DD-YYYY')
)


    That query worked fine in Toad (I added SELECT and FROM clauses, of course). But it errored-out in Maximo:

    BMXAA7024E - The query contains an invalid condition. Modify the query and try again.



    The problem ended up being the comments. For whatever reason, Maximo can't seem to handle SQL comments in the WHERE clause. 

    I removed the comments and it worked without issue. Hopefully, that can save someone some trouble in the future -- if they're wondering why their query works in Toad, but not Maximo.

    Cheers.


    #AssetandFacilitiesManagement
    #Maximo


  • 2.  RE: SQL comments in WHERE clause: "Invalid condition"

    Posted Sat June 12, 2021 04:47 PM
    Edited by System Admin Wed March 22, 2023 11:44 AM
    I have a related post here, for anyone who's interested:

    Generate WHERE clause from resultset ids?
    https://stackoverflow.com/questions/67952593/generate-where-clause-from-resultset-ids

    #AssetandFacilitiesManagement
    #Maximo


  • 3.  RE: SQL comments in WHERE clause: "Invalid condition"

    Posted Mon June 14, 2021 07:17 AM
    Hi.
    Adding comments is something worth doing as it can make it a lot easier to debug - something I will blog about soon

    The problem is how you have structured your comments.

    classstructureid in 
(
select
    classstructureid
--    description,
--    class_rowstamp,
--    max(starttime) as estimated_last_edit_date
from
    You are relying on the carriage returns to stop the comments.
    Toad can make that interpretation.
    Maximo won't interpret the carriage returns in the same way.

    Maximo actually interprets it like this:
    select classstructureid -- description, -- class_rowstamp, -- max(starttime) as estimated_last_edit_date from.

    when it is read like this you can see that the --description actually comments out the rest of the statement.

    If you want to add comments then use the /* comment */

    so it could look like one of these:

    select classstructureid
    /* description, */
    /* class_rowstamp, */
    /* max(starttime) as estimated_last_edit_date */
    from

    so the final SQL statement would be interpreted like this:

    select classstructureid /* description, */ /* class_rowstamp, */ /* max(starttime) as estimated_last_edit_date */ from

    this essentially replicate the comment on every line look that you had.
    A neater way would be to do this:

    select classstructureid
    /* description, 
     class_rowstamp,
     max(starttime) as estimated_last_edit_date */
    from

    so the final SQL statement would be interpreted like this:
    select classstructureid /* description, class_rowstamp, max(starttime) as estimated_last_edit_date */ from

    best regards,
    mark


    ------------------------------
    Mark Robbins
    Support Lead/Technical Design Authority / IBM Champion 2017 & 2018 & 2019 & 2020 & 2021
    Vetasi Limited
    https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/
    ------------------------------

    Original Message


  • 4.  RE: SQL comments in WHERE clause: "Invalid condition"

    Posted Tue June 15, 2021 11:08 AM
    The /* comment */ technique works *almost* everywhere. I think KPIs are one of the few places they don't work -- where comments just are *not* allowed.

    ------------------------------
    Blessings,
    Jason Uppenborn
    Sr. Technical Maximo Consultant
    Ontracks Consulting
    ------------------------------

    Original Message


  • 5.  RE: SQL comments in WHERE clause: "Invalid condition"

    Posted Thu October 26, 2023 12:36 PM

    Thank you! Was trying to find a way around this with out deleting much needed comments.



    ------------------------------
    Ryan Eiding
    Greater Cincinnati Water Works
    513-591-7758
    ------------------------------

    Original Message