nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Sql querry

padytom

Need help with this query to parse passed and failed from longdescription text
 
select 
MAXIMO.WOACTIVITY.WOGROUP,
MAXIMO.WOACTIVITY.WORKORDERID,
MAXIMO.WOACTIVITY.DESCRIPTION,
MAXIMO.WORKORDER.ACTFINISH,
MAXIMO.WORKORDER.STATUS,
MAXIMO.WORKORDER.WONUM,
MAXIMO.WORKORDER.JPNUM,
MAXIMO.WORKORDER.ISTASK,
MAXIMO.WORKORDER.LOCATION,
MAXIMO.WORKORDER.WORKTYPE,
MAXIMO.WORKORDER.SUBWTYPE,
MAXIMO.WORKORDER.BRANCHID,
MAXIMO.LONGDESCRIPTION.LDTEXT,
 
MAXIMO.WORKORDER.CREWID,
MAXIMO.LOCATIONS.SPACECLASS
from 
MAXIMO.WOACTIVITY
INNER JOIN MAXIMO.WORKORDER ON MAXIMO.WOACTIVITY.WOGROUP = MAXIMO.WORKORDER.WONUM
INNER JOIN MAXIMO.LONGDESCRIPTION ON MAXIMO.WOACTIVITY.WORKORDERID = MAXIMO.LONGDESCRIPTION.LDKEY
INNER JOIN MAXIMO.LOCATIONS ON MAXIMO.WORKORDER.LOCATION = MAXIMO.LOCATIONS.LOCATION
where
MAXIMO.WORKORDER.WORKTYPE = 'TOURS'
and
MAXIMO.WORKORDER.SUBWTYPE = 'T:INSP'
and
MAXIMO.WORKORDER.BRANCHID = 'PBMO'
and
MAXIMO.WORKORDER.CREWID = 'PCQA'
 
and
MAXIMO.WORKORDER.ISTASK = '0.00'
and
MAXIMO.LOCATIONS.SPACECLASS in ('Corridor',' Public Restroom', 'Ramp')
and
MAXIMO.WORKORDER.JPNUM like '%QA%'
and 
MAXIMO.LONGDESCRIPTION.LDTEXT = 
(CASE
WHEN MAXIMO.LONGDESCRIPTION.LDTEXT like '%PASS' THEN 'Passed' 
WHEN MAXIMO.LONGDESCRIPTION.LDTEXT like '%FAIL' THEN 'Failed'
 
End)
 
ORDER BY MAXIMO.WORKORDER.LOCATION

Find more posts tagged with

Comments

wwilliams

Have you tried
WHEN MAXIMO.LONGDESCRIPTION.LDTEXT like '%PASS%' THEN 'Passed'
Oracle
WHEN UPPER(MAXIMO.LONGDESCRIPTION.LDTEXT) like '%PASS%' THEN 'Passed'
 
 
So someone is typing in the longdescription PASS or FAIL? I would recommend a different method\column for that information.

rdpeterson

<div>There are a couple of problems with the following line in your WHERE clause for long description:</div>
<div> </div>
<div style="margin-left:40px;">
MAXIMO.LONGDESCRIPTION.LDTEXT =
(CASE
WHEN MAXIMO.LONGDESCRIPTION.LDTEXT like '%PASS' THEN 'Passed'
WHEN MAXIMO.LONGDESCRIPTION.LDTEXT like '%FAIL' THEN 'Failed'
End)
</div>
<div> </div>
<div>First, LONGDESCRIPTION.LDTEXT field in Maximo 7.5 is data type CLOB (character large object), not a standard text field (data type VARCHAR2). IBM changed the field to this data type from earlier versions of Maximo because CLOB can hold much more data. Multi-page documents are frequently copied and pasted into the Work Order long description field in our organization. You can use the Oracle SQL TO_CHAR({text field name}) to convert the CLOB to text. You can also use the UPPER() or LOWER() functions to convert the search text to upper or lower case to eliminate case sensitivity in your search. Note TO_CHAR, LOWER, and UPPER will only work when converting long description CLOB field to text for search in your WHERE clause for any length of CLOB field. Oracle will not allow conversion of a CLOB field to text in the SELECT clause for display if the CLOB length exceeds 4,000 characters. I use an expression like this to display these longer CLOB fields:</div>
<div> </div>
<div style="margin-left:40px;">dbms_lob.substr(ldtext,{number of characters to display <= 4,000} , {starting position in string})</div>
<div> </div>
<div>The solution in my reports is to break up the long description into blocks not exceeding 4,000 characters each. Each block is a separate field in my query. I then concatenate these "pieces" of the complete long description together in the BIRT display object.</div>
<div> </div>
<div>Second, and perhaps more importantly, when will you run into a work order with long description containing ONLY the word "Passed" or "Failed" as generated by your CASE expression on the right side of the equals sign? Did you intend to simply impose the condition where the long description must contain the word "pass" or "fail"? If so, the WHERE statement would simply be</div>
<div> </div>
<div style="margin-left:40px;">....</div>
<div style="margin-left:40px;">AND ( UPPER(LONGDESCRIPTION.LDTEXT) LIKE '%PASS%' OR UPPER(LONGDESCRIPTION.LDTEXT) LIKE '%FAIL%' )</div>
<div style="margin-left:40px;"> </div>
<div>You can then use a CASE statement in your SELECT clause to display the word "passed" or "failed" depending on what the query returns in the long description field.</div>
<div> </div>
<div>I hope this helps.</div>

padytom

Thank you for your response. Yes the long description contains only "pass" and "fail" The problem is to be able to filter status of pass and failed from the long description if possible into a computed column so I can do a count on them