Topic
4 replies Latest Post - ‏2010-04-19T15:58:43Z by SystemAdmin
jpdhouston
jpdhouston
2 Posts
ACCEPTED ANSWER

Pinned topic Perplexing formatting errors in "Where" Clause Searches

‏2010-04-15T15:41:47Z |
I am creating some involved work order search routines and have run into a perplexing situation.

Consider the following two queries:

Query 1: status in (‘WMATL’) and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM'))

Query 2: status in (‘WAPPR’) and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM'))

Both will yield the following error message: BMXWW420E - Database error number 911 has occurred. ORA-00911: invalid character

Trying to isolate the problem I find that there is no problem with this query:

(kbr_actiontaken is null and parent is null) and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM'))

Change it by simply adding “and status in (‘WAPPR’)” to the query results in the same error noted above

(kbr_actiontaken is null and parent is null) and status in (‘WAPPR’) and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM'))

But if I paste in any of the above expressions into the query dialog box, delete the expression ‘WAPPR’ and then simply retype it directly into the dialog box – MAXIMO ACCEPTS IT.

If I copy/paste the entire query back into a word document, then copy/paste it back into the where clause dialog box – IT DOESN’T WORK AGAIN……. I tried the same thing by putting it in a plain text file and into an Excel spreadsheet (with the cell dormatted to both general and text), repeating all the above with the same result – WILL NOT WORK copying from a word or text file but WILL WORK if I type it directly into the query dialog box.
This query works:

(((actstart <= reportdate) or (actstart >= actfinish) or (actstart is null) or (actfinish is null) or (kbr_actiontaken is null and parent is null) or (kbr_altcontact is null and parent is null) or (wopriority < 5) or (phone is null and parent is null) or (supervisor is null and parent is null) or (kbr_deptassigned = 'HEQ') or (workorder.wolo1 is null and parent is null)) and status in ('COMP','CLOSE') and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM')))

The following query doesn’t work unless again, I delete the ‘WAPPR’ and retype it in directly in the query dialog box. :

(((actstart <= reportdate) or (actstart >= actfinish) or (actstart is null) or (actfinish is null) or (kbr_actiontaken is null and parent is null) or (kbr_altcontact is null and parent is null) or (wopriority < 5) or (phone is null and parent is null) or (supervisor is null and parent is null) or (kbr_deptassigned = 'HEQ') or (workorder.wolo1 is null and parent is null)) and status in ('COMP','CLOSE',’WAPPR’) and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM')))

Now to finally blow your minds, I modify this query by simply deleting ‘CLOSE’, to get the following query, copy/paste it into the query box:

(((actstart <= reportdate) or (actstart >= actfinish) or (actstart is null) or (actfinish is null) or (kbr_actiontaken is null and parent is null) or (kbr_altcontact is null and parent is null) or (wopriority < 5) or (phone is null and parent is null) or (supervisor is null and parent is null) or (kbr_deptassigned = 'HEQ') or (workorder.wolo1 is null and parent is null)) and status in ('COMP','WAPPR') and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM')))

And this is the error message I receive: BMXAA4201E - Database error number 907 has occurred. ORA-00907:P missing rihgt parenthesis.

But if I paste in any of the above expressions into the query dialog box, delete the expression ‘WAPPR’ and then simply retype it directly into the dialog box – MAXIMO ACCEPTS IT. If I copy/paste the entire query back into a word document, then copy/paste it back into the where clause dialog box – IT DOESN’T WORK AGAIN…….

I tried the same thing by putting it in a plain text file, repeating all the above with the same result – WILL NOT WORK copying from a word or text file but WILL WORK if I type it directly into the query dialog box.

Anyone got any ides what is going on here?????

If it matters, here is the system particulars:

App Server Weblogic Application Server 9.2.2.0

Version
Asset Management 7.1.0.0 Build 52b DB Build V7100-001
Base Services 7.1.1.4-LA20090423-1329 Build 20081211-0842 DB Build V7114-118
Common PMP 7.1.1.4 Build 20081205D DB Build V710-43

TIA

John
Updated on 2010-04-19T15:58:43Z at 2010-04-19T15:58:43Z by SystemAdmin
  • bbradford
    bbradford
    35 Posts
    ACCEPTED ANSWER

    Re: Perplexing formatting errors in "Where" Clause Searches

    ‏2010-04-15T16:20:32Z  in response to jpdhouston
    I'll bet Word is substituting fancy apostrophes in where you have single quotes. When you tried it with plain text file, did you use wordpad or notepad?

    -B
    • jpdhouston
      jpdhouston
      2 Posts
      ACCEPTED ANSWER

      Re: Perplexing formatting errors in "Where" Clause Searches

      ‏2010-04-16T10:50:17Z  in response to bbradford
      I also thought that it might be introducing some formating quirk. However, I used Word, Note Pad & Word Pad and within each I tried text, RTF, text-MS DOS, text-unicode, you name it. It is the single quote mark that appears to be the source of the problem. However, I have limited knowledge of SQL. No where in what I have yet read does it mention formating quirks appliable to using the language.

      If you notice the other queries that do work the single quote marks do not cause a problem. Thinking that it was related somehow to the two statuses that have a "W" I tried using CLOSED, COMP and APPR. They all blew up in the query: status in (‘CLOSE’) and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM')).

      However, the following works: (((actstart <= reportdate) or (actstart >= actfinish) or (actstart is null) or (actfinish is null) or (kbr_actiontaken is null and parent is null) or (kbr_altcontact is null and parent is null) or (wopriority < 5) or (phone is null and parent is null) or (supervisor is null and parent is null) or (kbr_deptassigned = 'HEQ') or (workorder.wolo1 is null and parent is null)) and status in ('COMP','CLOSE') and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM'))).

      Finally, try this on for size:

      Took the phrase status in (‘COMP’,’CLOSE’) and statusdate >= to_timestamp ('2010-03-10 00:00:00.001' , 'YYYY-MM-DD HH24:MI:SS.FF') and (workorder.location like '%ME-IRQ-TTM-T1%') and ((kbr_deptassigned = 'HEQ') or (kbr_deptassigned = 'HVM'))

      1. Created in Word and pasted into query box: NO GO
      2. Created in Word and "special pasted - text" into Excel then copied from Excel into query box: SUCCESS
      3. Created in both Note Pad & Word Pad as text file and pasted into query box: NO GO
      4. Created in both Note Pad & Word Pad and "special paste - text" into Excel then copied from Excel into query box: NO GO!!!! (This time with the missing right parenthesis message.

      and if 4 doesn't ring your jollies: take a successful statement (number 2 above or any other created in Excel) and copy it into an email (Outlook using Word as the editor), send it, and the receipient can copy paste it from the email into the query box AND IT WORKS....

      Just when it begins to make sense, another curve is thrown in.....

      It seems that until I can figure out what is going on, that the best course of action is to compose all statements in an Excel spreadsheet.
      • SystemAdmin
        SystemAdmin
        5842 Posts
        ACCEPTED ANSWER

        Re: Perplexing formatting errors in "Where" Clause Searches

        ‏2010-04-16T11:25:46Z  in response to jpdhouston
        Hi JP Houston,
        I guess u don't actually have this problem as u have a solution for it.
        but i dont think it's word that is causing the problem as i regularly copy and paste from word without any issue. However whenever i have had that problem it was due to the fact i was compying an example query from somewhere else usually a website and what would happen is that the "'" from the sample query would be converted to "`" by either notepad or word. they are two different characters and ` is not recognised by Maximo.
        • SystemAdmin
          SystemAdmin
          5842 Posts
          ACCEPTED ANSWER

          Re: Perplexing formatting errors in "Where" Clause Searches

          ‏2010-04-19T15:58:43Z  in response to SystemAdmin
          Agree with the previous post on this issue :)

          Have previously seen something very similar with users cutting and pasting sections of email messages from an exchange system into MAXIMO. Users had Word setup as the default mail editor and when they cut and pasted simple text, word would replace the start/end quotes with specific characters with codes above the 128 mark - users would see the message as entered when they first input it, but once it had been saved and retrieved - these characters would be displayed as squares (system was running the ineta driver against an MS SQL Server RDBMS - which caused all non ANSI characters to be trashed)

          Best Regards
          SlightlyCrazy