7 Replies Latest reply on Oct 20, 2014 9:05 AM by W M

    Form result list not sorted correctly after performing a search

      Share This:

      Hi

       

      When I perform a search on the CTM:People Organization form, I get a different sort order on two different servers.

       

      On server 1, the result list is sorted correctly on the default sort order set on this form (Company, Organization, Department)

      On server 2, entries with departments starting with lowercase, are sorted behind entries starting with uppercase.

      e.g. value 'test' would be at the end of the list of departments if the other departments all started with an uppercase.

       

      I have already done quite some investigations, but I cannot pinpoint the reason for this behavior:

      - both servers connect to an oracle db, same version

      - both servers have a schema_sort set for oracle

      - When executing the sql commands that are executed (traced with sql logging) in sql developer, on both servers the result list is sorted correctly

       

      It seems to be a problem on the application layer.

       

      Can somebody help?

       

      Best Regards

      Werner

        • 1. Re: Form result list not sorted correctly after performing a search

          Hi Werner,

           

          I'm not sure, but I could remember you can especify to the DB to be o not to be case sensitive. If this configuration is diferent in both DB, the result you are getting is correct.

           

          Best Regards

          Julian

          • 2. Re: Form result list not sorted correctly after performing a search
            Ganesh Gore

            did you check form properties are identical on both servers? (mainly Result Lists fileds, Sort and index properties)

            • 3. Re: Form result list not sorted correctly after performing a search

              the 'Database Case Sensitive' option is activated on both servers.

               

              CTM:People Organization form on both servers has:

                - Sort: Company / Organization / Department (all Ascending)

                - Indexes: Company / Navigation Menu01 / InstanceId / Status / Last Modified Date

                - Result List Fields: Company / Organization / Department / Status

              • 4. Re: Form result list not sorted correctly after performing a search
                Ganesh Gore

                can you post SQL logs captured on both servers ?

                did you this search operation in different browsers using same user account? same behaviour ?

                • 5. Re: Form result list not sorted correctly after performing a search

                  It was tested in the browser and full client (the customer is still in ARS 7.5) and it was tested with several different accounts.

                  • 6. Re: Form result list not sorted correctly after performing a search

                    SQL log NOK Server:

                     

                    <SQL > <TID: 0000002608> <RPC ID: 0000033309> <Queue: Admin     > <Client-RPC: 390680   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:07.6130 */Journal de suivi SQL. Activé (AR Server 7.5.00 Patch 008 201112090209)

                    <SQL > <TID: 0000003364> <RPC ID: 0000033311> <Queue: Prv:390680> <Client-RPC: 390680   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:07.8780 */SELECT name FROM servgrp_config

                    <SQL > <TID: 0000003364> <RPC ID: 0000033311> <Queue: Prv:390680> <Client-RPC: 390680   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:07.8780 */OK

                    <SQL > <TID: 0000003364> <RPC ID: 0000033311> <Queue: Prv:390680> <Client-RPC: 390680   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:07.8780 */SELECT checkInterval FROM servgrp_config

                    <SQL > <TID: 0000003364> <RPC ID: 0000033311> <Queue: Prv:390680> <Client-RPC: 390680   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:07.8780 */OK

                    <SQL > <TID: 0000002608> <RPC ID: 0000390600> <Queue: Admin     > <Client-RPC: 999999   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:08.6440 */SELECT T1.C1,C152,C155,C156 FROM T1 WHERE ((T1.C8 = '8760') AND (T1.C7 = 0) AND (T1.C150 = 0) AND ((T1.C152 LIKE 'FRB%') OR (T1.C152 IS NULL))) ORDER BY C152 ASC, 1 ASC

                    <SQL > <TID: 0000002608> <RPC ID: 0000390600> <Queue: Admin     > <Client-RPC: 999999   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:08.6440 */OK

                    <SQL > <TID: 0000002608> <RPC ID: 0000390600> <Queue: Admin     > <Client-RPC: 999999   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:09.6280 */SELECT T1.C1,C152,C155,C156 FROM T1 WHERE ((T1.C8 = '8760') AND (T1.C7 = 0) AND (T1.C150 = 0) AND ((T1.C152 LIKE 'FRB%') OR (T1.C152 IS NULL))) ORDER BY C152 ASC, 1 ASC

                    <SQL > <TID: 0000002608> <RPC ID: 0000390600> <Queue: Admin     > <Client-RPC: 999999   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:09.6280 */OK

                    <SQL > <TID: 0000003360> <RPC ID: 0000033314> <Queue: List      > <Client-RPC: 390620   > <USER: 135367                                       > /* lun. oct. 20 2014 16:01:11.0190 */SELECT * FROM ( SELECT  T908.C1,C1000000001,C1000000010,C200000006,C7 FROM T908 ORDER BY C1000000001 ASC,C1000000010 ASC,C200000006 ASC, 1 ASC ) WHERE ROWNUM <=  2147483648

                    <SQL > <TID: 0000003360> <RPC ID: 0000033314> <Queue: List      > <Client-RPC: 390620   > <USER: 135367                                       > /* lun. oct. 20 2014 16:01:11.0190 */OK

                    <SQL > <TID: 0000003344> <RPC ID: 0000033316> <Queue: Fast      > <Client-RPC: 390620   > <USER: 135367                                       > /* lun. oct. 20 2014 16:01:11.2850 */SELECT C1,C2,C3,C4,C5,C6,C7,C8,0,C112,C179,C60900,C70000001,C200000006,C301600300,C700000001,C1000000000,C1000000001,C1000000010,C1000000251,C1000003965,C1000003966,C1000003967 FROM T908 WHERE C1 = 'POR000000001924'

                    <SQL > <TID: 0000003344> <RPC ID: 0000033316> <Queue: Fast      > <Client-RPC: 390620   > <USER: 135367                                       > /* lun. oct. 20 2014 16:01:11.2850 */OK

                    <SQL > <TID: 0000002608> <RPC ID: 0000033318> <Queue: Admin     > <Client-RPC: 390680   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:01:16.3000 */Journal de suivi SQL. Désactivé

                     

                     

                    The select in question is in line 10

                    • 7. Re: Form result list not sorted correctly after performing a search

                      SQL Log OK Server:

                       

                      <SQL > <TID: 0000002800> <RPC ID: 0000010282> <Queue: Admin     > <Client-RPC: 390620   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:26.3490 */Journal de suivi SQL. Activé (AR Server 7.5.00 Patch 008 201112090209)

                      <SQL > <TID: 0000003972> <RPC ID: 0000010284> <Queue: Fast      > <Client-RPC: 390620   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:26.3800 */SELECT name FROM servgrp_config

                      <SQL > <TID: 0000003972> <RPC ID: 0000010284> <Queue: Fast      > <Client-RPC: 390620   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:26.3800 */OK

                      <SQL > <TID: 0000003972> <RPC ID: 0000010284> <Queue: Fast      > <Client-RPC: 390620   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:26.3800 */SELECT checkInterval FROM servgrp_config

                      <SQL > <TID: 0000003972> <RPC ID: 0000010284> <Queue: Fast      > <Client-RPC: 390620   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:26.3800 */OK

                      <SQL > <TID: 0000002800> <RPC ID: 0000390600> <Queue: Admin     > <Client-RPC: 999999   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:27.3800 */SELECT T1.C1,C152,C155,C156 FROM T1 WHERE ((T1.C8 = '8760') AND (T1.C7 = 0) AND (T1.C150 = 0) AND ((T1.C152 LIKE 'FRB%') OR (T1.C152 IS NULL))) ORDER BY C152 ASC, 1 ASC

                      <SQL > <TID: 0000002800> <RPC ID: 0000390600> <Queue: Admin     > <Client-RPC: 999999   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:27.3800 */OK

                      <SQL > <TID: 0000002800> <RPC ID: 0000390600> <Queue: Admin     > <Client-RPC: 999999   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:28.4900 */SELECT T1.C1,C152,C155,C156 FROM T1 WHERE ((T1.C8 = '8760') AND (T1.C7 = 0) AND (T1.C150 = 0) AND ((T1.C152 LIKE 'FRB%') OR (T1.C152 IS NULL))) ORDER BY C152 ASC, 1 ASC

                      <SQL > <TID: 0000002800> <RPC ID: 0000390600> <Queue: Admin     > <Client-RPC: 999999   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:28.4900 */OK

                      <SQL > <TID: 0000003224> <RPC ID: 0000010287> <Queue: List      > <Client-RPC: 390620   > <USER: 135367                                       > /* lun. oct. 20 2014 16:04:29.4270 */SELECT * FROM ( SELECT  T908.C1,C1000000001,C1000000010,C200000006,C7 FROM T908 ORDER BY C1000000001 ASC,C1000000010 ASC,C200000006 ASC, 1 ASC ) WHERE ROWNUM <=  2147483648

                      <SQL > <TID: 0000003224> <RPC ID: 0000010287> <Queue: List      > <Client-RPC: 390620   > <USER: 135367                                       > /* lun. oct. 20 2014 16:04:30.0370 */OK

                      <SQL > <TID: 0000003484> <RPC ID: 0000010289> <Queue: Fast      > <Client-RPC: 390620   > <USER: 135367                                       > /* lun. oct. 20 2014 16:04:30.3340 */SELECT C1,C2,C3,C4,C5,C6,C7,C8,0,C112,C179,C60900,C70000001,C200000006,C301600300,C700000001,C1000000000,C1000000001,C1000000010,C1000000251,C1000003965,C1000003966,C1000003967 FROM T908 WHERE C1 = 'POR000000001924'

                      <SQL > <TID: 0000003484> <RPC ID: 0000010289> <Queue: Fast      > <Client-RPC: 390620   > <USER: 135367                                       > /* lun. oct. 20 2014 16:04:30.3340 */OK

                      <SQL > <TID: 0000002800> <RPC ID: 0000010292> <Queue: Admin     > <Client-RPC: 390620   > <USER: Remedy Application Service                   > /* lun. oct. 20 2014 16:04:35.3650 */Journal de suivi SQL. Désactivé

                       

                      The select in question is in line 10