SQL error when counting search results and sort order is record type TRIM 732 (926 Views)
Reply
Esteemed Contributor
Joshua Hutley
Posts: 600
Registered: ‎09-29-2008
Message 1 of 12 (926 Views)

SQL error when counting search results and sort order is record type TRIM 732

Hi

 

I am getting this error when conducting a count of records within a TRIM search with sort order of record type.

 

This is in TRIM 7.32. Can other uses see if sorting by record type allows you to count search results in your environments. (note this is done using a end user and a large list of results).

 

Function request (Retrieve dataset rows (GetRecordset64)) for HP TRIM Workgroup Server 'TRIM1' failed. The SQL query failed. Errors were Incorrect syntax near the keyword 'SELECT'.  SQL was SELECT COUNT_BIG(SELECT TSRECORD.uri)  FROM TSRECORD,TSRECTYPE  WITH (NOLOCK)   WHERE (rcRecTypeUri = TSRECTYPE.uri) AND (TSRECORD.URI IN (SELECT  A0.uri  FROM  TSRECORD A0 WITH (NOLOCK)  WHERE ( regDateTime  BETWEEN  '20130515140000' AND  '201305161359591' AND  A0.uri > 0)) AND currDisp IN (1,2,3,4,5)) AND  ( TSRECORD.rcSecLevel <= 10  AND TSRECORD.rc1CaveatKey = 1  AND TSRECORD.rc2CaveatKey = 1  AND TSRECORD.rc3CaveatKey = 1  AND TSRECORD.rc4CaveatKey = 1  AND TSRECORD.rc5CaveatKey = 1  )

HP Expert
Neil Summers
Posts: 1,059
Registered: ‎10-08-2008
Message 2 of 12 (903 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

Yeah I can reproduce the same error. Looks like a bug. Actually it was there in 7.31 too, so not new to 7.32. Trust you Joshua to find bugs no one else in the world has found yet!

 

It's been logged with the development team.

Neil


Note: Any posts I make on this forum are my own personal opinion and do not constitute a formal commitment on behalf of HP.

(Please state the version of TRIM/HPRM you're using in all posts)

HP Software Support Online (SSO): http://support.openview.hp.com
Frequent Advisor
NZ-TRIM-Experts
Posts: 52
Registered: ‎05-05-2010
Message 3 of 12 (878 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

Hi, Can you tell me what builds of 7.32 build this impacts - the latest 5515?  - Chris

InfocentriK Helpdesk

P (+64) 04 – 566 1115
E support@infocentrik.co.nz
W www.infocentrik.co.nz

InfocentriK Limited
PO Box 31139
Lower Hutt 5040
HP Expert
Neil Summers
Posts: 1,059
Registered: ‎10-08-2008
Message 4 of 12 (864 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

As far as I know it affects all 7.3 releases. There has been no fix for it in the latest release.

Neil


Note: Any posts I make on this forum are my own personal opinion and do not constitute a formal commitment on behalf of HP.

(Please state the version of TRIM/HPRM you're using in all posts)

HP Software Support Online (SSO): http://support.openview.hp.com
Frequent Advisor
NZ-TRIM-Experts
Posts: 52
Registered: ‎05-05-2010
Message 5 of 12 (855 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

Thanks for the update  Neil.

Chris

InfocentriK Helpdesk

P (+64) 04 – 566 1115
E support@infocentrik.co.nz
W www.infocentrik.co.nz

InfocentriK Limited
PO Box 31139
Lower Hutt 5040
Valued Contributor
raynebc
Posts: 525
Registered: ‎05-06-2009
Message 6 of 12 (725 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

We just upgraded to 7.3.4 and one of our administrators ran into this so a group of us tried it out.  Half of us get an SQL error (different from the one posted in this thread) and the other half of us get no error and the sort works.  The SQL error in question is:

===

A request to the HP TRIM Workgroup Server '(SERVERNAME)' failed:

Function request (Retrieve dataset rows (GetRecordset64)) for HP TRIM Workgroup Server 'EDMS' failed. Workgroup Error. The SQL query failed. Errors were An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.  SQL was SELECT  tsRecord.uri,rcRecTypeUri  FROM TSRECORD,TSRECTYPE  WITH (NOLOCK)   WHERE (rcRecTypeUri = TSRECTYPE.uri) AND (TSRECORD.URI IN (SELECT  A0.uri  FROM  TSRECORD A0 WITH (NOLOCK)  WHERE ( regDateTime  BETWEEN  '20140311060000' AND  '201403120559591' AND  A0.uri > 0)) AND _TSRECORD.URILIST('rcRecTypeUri','589,609,565,593,566,567,568,610,813,814,569,2040,570,571,572,573,574,579,580,581,582,583,584,588,585,612,586,587,575,576,577,578,591,592,613,614,2003,2004,890,615,616,617,618,619,620,621,622,623,624,625,611,626,628,631,632,630,629,627,594,595,596,598,597,599,601,600,602,605,603,607,606,604,608,2068,2069,2070,2071,2072,2073,2074,2075,2076,2077,2078,2079,2080,2081,2082,2083,2084,2085,729,730,728,733,734,735,736,737,738,739,740,731,732,2056,671,2066,674,672,675,676,677,678,679,680,849,657,659,660,661,662,727,667,681,668,663,664,673,665,666,669,693,656,655,682,683,684,691,670,692,685,686,687,688,775,776,789,2097,787,788,777,2094,2095,797,778,2086,779,780,2059,2087,781,782,796,790,783,784,793,795,791,792,794,2057,2062,798,799,800,801,802,803,804,805,2063,806,807,2064,808,809,810,811,812,2060,2061,531,2008,532,533,534,535,536,2007,542,543,544,545,546,547,548,2090,846,847,848,505,506,507,508,509,530,510,511,512,2088,2089,513,514,515,516,517,518,2091,2092,1,16,24,22,17,6,2058,523,19,528,20,527,522,525,526,28,31,18,30,524,537,539,540,520,519,2045,2042,551,552,558,559,561,560,564,773,557,554,837,555,840,556,841,839,2005,842,843,844,836,838,845,553,562,563,694,695,696,698,697,699,700,701,702,703,704,705,706,707,709,710,714,715,716,717,718,719,720,721,722,723,724,757,725,726,2047,815,816,817,820,821,822,823,824,825,818,819,826,920,827,828,829,830,832,831,833,834,835,888,889,653,743,745,744,746,747,748,749,750,751,753,752,755,774,754,756,758,759,760,761,762,763,764,765,766,767,741,742,768,769,770,771,772,2046,2065,637,638,639,640,648,2039,651,641,642,636,643,633,634,891,646,2006,635,647,689,690,652,650,644,654,645,1503,1504,850,1505,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,892,893,2043,902,903,900,2044,901,921,904,894,905,895,2093,919,906,907,908,909,910,911,912,913,896,914,915,916,897,917,898,918,899,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2041,2023,2024,2048,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2055,2035,2036,2067,2037,2038,2049,2050,2051,2052,541') AND currDisp=1) AND  ( TSRECORD.rcSecLevel <= 35  AND  (3 % TSRECORD.rc1CaveatKey) = 0  AND TSRECORD.rc2CaveatKey = 1  AND TSRECORD.rc3CaveatKey = 1  AND TSRECORD.rc4CaveatKey = 1  AND TSRECORD.rc5CaveatKey = 1  )  ORDER BY  recTypeName  ASC

===

 

Affected users are able to sort by other columns for the same search without issue.  Has HP made any progress with this error in the last year?  Any workarounds?

Honored Contributor
EWillsey
Posts: 1,929
Registered: ‎04-20-2010
Message 7 of 12 (715 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

I think this is a side effect of the QCCR2D45228 fix. Probably no way around it for now.
Valued Contributor
raynebc
Posts: 525
Registered: ‎05-06-2009
Message 8 of 12 (695 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

One of our staff members discovered that when she executed a saved search, it allowed her to sort by record type without generating an SQL error.  This may be a good workaround until HP fixes the bug.

HP Expert
Neil Summers
Posts: 1,059
Registered: ‎10-08-2008
Message 9 of 12 (689 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732


raynebc wrote:

...one of our administrators ran into this so a group of us tried it out.  Half of us get an SQL error (different from the one posted in this thread) and the other half of us get no error and the sort works..."

Can you please give us an example of the way the search was conducted? Are we just talking about something like a Title Word search with a pre-defined sort order? Are we talking about sorting the results by the record type column after running the search, and then hitting the Count button? Trying to reproduce it at my end.

Neil


Note: Any posts I make on this forum are my own personal opinion and do not constitute a formal commitment on behalf of HP.

(Please state the version of TRIM/HPRM you're using in all posts)

HP Software Support Online (SSO): http://support.openview.hp.com
Valued Contributor
raynebc
Posts: 525
Registered: ‎05-06-2009
Message 10 of 12 (682 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

[ Edited ]

This seems to be happening to affected users for any search at all, title word, any word, date registered, creator, etc.  It doesn't matter whether the search result set is large enough to require more than one page to display (ie. results have to be fetched in batches), and hitting the count button before trying to sort does not avoid the error.

HP Expert
Neil Summers
Posts: 1,059
Registered: ‎10-08-2008
Message 11 of 12 (652 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

OK let me try asking the question differently...

 

I'm using 7.34 and don't get the error. This is what I'm doing:

 

1. Hit the search icon to open a Boolean search editor

2. Choose to search by title word. Enter a title word and run the search

3. When the results come back, click on the top of the Record Type column to sort by that column

4. Hit the count button

 

No errors. What are you doing differently when you see the error?

Neil


Note: Any posts I make on this forum are my own personal opinion and do not constitute a formal commitment on behalf of HP.

(Please state the version of TRIM/HPRM you're using in all posts)

HP Software Support Online (SSO): http://support.openview.hp.com
Valued Contributor
raynebc
Posts: 525
Registered: ‎05-06-2009
Message 12 of 12 (644 Views)

Re: SQL error when counting search results and sort order is record type TRIM 732

The "boolean search editor" is selected for record searches.  This happens to affected users when they create a multi-condition search or even a search with a single criterion (just entering it into the search field without formally launching the "Search for Records" window) and then sorting by record type.  The use of the "count" function doesn't seem to make a difference here.  I don't get the SQL error, but various other staff do.  I don't know what other conditions are needed to trigger this bug.

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.