Final points.
AST:BaseElement is an Outer Join, so there are many entries with no AST:Attributes record. I just happen to have a block of 100 records from 100-200 with no AST:Attributes. If I start my query at 101 or 2, I get record 201 in the same f() call and so the IN clause is generated correctly with one ID. So, I need only to (manually) ensure that any 100 records with no AST:Attributes are not requested in one go. Ugly but easy enough to work around for me. The Status History of AST:BaseElement comes from AST:Attributes, so the bug is that if the 100 Ids passed to ARGetMultipleEntries() with all NULL as the second ID, the SQL is generated with an invalid IN clause. This is BMC supplied data BTW. Still, any word from BMC that this has been patched? Cheers Ben From: Ben Chernys [mailto:[email protected]] Sent: November-03-15 16:16 To: '[email protected]' Subject: RE: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing expression More info: Each AST:BaseElement is picked up with “(E0 = '000000000000368' and E1 = '000000000000120' )” and the successful pick up of the Status History seems to be missing some qualification terms: ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:55:57.4055 */SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6,T7,U7,T8,U8,T9,U9,T10,U10,T11,U11,T12,U12,T13,U13 FROM H1572 WHERE entryId ARARGetMultipleEntries ==> IN ('000000000000001', '000000000000002', '000000000000101', '000000000000102', '000000000000103', '000000000000104', '000000000000105', '000000000000106', ARGetMultipleEntries ==> '000000000000107', '000000000000108', '000000000000109', '000000000000110', '000000000000111', '000000000000112', '000000000000113', '000000000000114', ARGetMultipleEntries ==> '000000000000115', '000000000000116', '000000000000117', '000000000000118', '000000000000119', '000000000000120', '000000000000121', '000000000000122') In the Join SQLs, E1 = NULL is often included. It seems the IN clause on the Status History, is for those CIs where E1 is a real ID only. Ben From: Ben Chernys [mailto:[email protected]] Sent: November-03-15 15:56 To: '[email protected]' Subject: RE: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing expression Thanks Rick, but that can’t be it. I am doing NO SQL queries. ONLY an ARGetMuplipleEntries() call – which has no query terms at all – so there’s nothing I can adjust – unless BMC publishes source J What’s interesting is that an IN expression was attempted to be generated BUT only for the Status History and 100 different SQL selects were done for the 100 records (which worked). All this is part of the single ARGetMultipleEntries call. The question is – has this been fixed in a patch to 8.1.0? Cheers Ben From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Rick Cook Sent: November-03-15 15:36 To: [email protected] Subject: Re: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing expression ** I saw something similar on an AI Transformation (MSSQL) that was performing a similar function. I, too, thought it was a space issue. I removed one condition (order by) from the SQL query, and it worked. Rick On Nov 3, 2015 2:33 PM, "Ben Chernys" <[email protected]> wrote: ** Hi All, ARS / ITSM 8.1.0 >>8.1.00 201301251157<< on Cent OS against Oracle 11.2 Interesting trace on this one. The query worked fine the first go (there is no query argument to this f) and ARGetMultipleEntries() picked up 100 records from AST:BaseElement. On the second go of 100 records, the SQL log shows a single SQL repeated 100 times for each record, and then (for the status history): ARGetMultipleEntries ==> <SQL > <TID: 4253898496> <RPC ID: 0002163601> <Queue: List > <Client-RPC: 390620 > <USER: Demo > <Overlay-Group: 1 > ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5891 */SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6,T7 U7,T8,U8,T9,U9,T10,U10,T11,U11,T12,U12,T13,U13 FROM H1572 WHERE entryId IN) ARGetMultipleEntries ==> <SQL > <TID: 4253898496> <RPC ID: 0002163601> <Queue: List > <Client-RPC: 390620 > <USER: Demo > <Overlay-Group: 1 > ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5907 */*** ERROR *** ORA-00936: missing expression Interestingly, I think it may be related to not enough space allocated for the IN expression – as each Id will be more than 15 bytes. More interestingly, this table has 0 records. The table in Q (schemaid = 1572) is AST:BaseElement – an infamous join J So, has this been fixed in a server patch? Has anyone else seen this? Cheers Ben Chernys <http://www.softwaretoolhouse.com> www.softwaretoolhouse.com _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
