Hi listers - Have any of you spent any time troubleshooting SQL stuff in version 9 of ARS? I find it is much harder now. I contacted BMC premiere support with the following question:
How do we troubleshoot SQL queries in version 9? For example, here is the SQL query for a person querying for a single incident ticket by the Incident ID # INC000003475560. In past versions it the SQL would look something like this: SELECT T2147.C1000000161, T2147.C1000000018, T2147.C1000000019, T2147.C1000000000, T2147.C303497300, T2147.C1000000164, T2147.C7, T2147.C1000000217, T2147.C1000000218, T2147.C1000005261, T2147.C1000003009, T2147.C1 FROM T2147 WHERE (T2147.C1000000161 = 'INC000003475560') That's not 100% how it would look, but it's very close. Here's the same search in SQL in version 9. This is a real life example where a user cannot get the search results she expects and I honestly have no idea how to figure it out. SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM (SELECT T2147.C1000000161, T2147.C1000000018, T2147.C1000000019, T2147.C1000000000, T2147.C303497300, T2147.C1000000164, T2147.C7, T2147.C1000000217, T2147.C1000000218, T2147.C1000005261, T2147.C1000003009, T2147.C1 FROM T2147 WHERE ((T2147.C1000000161 = N'INC000003475560') AND (EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '20032;803;804;71002;1058;13006;13007;''zzz'';20316;440;0;20315;20213;-20032;20313;20211;1000000057;20033;20055;-1098;1000000014;20061;13010;1000000080;20403;20225;20302;-20000;20003;20000;-20003' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T2147.C60900, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T2147.C60900, '[^;]+')) + 1) ) OR EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '20032;803;804;71002;1058;13006;13007;''zzz'';20316;440;0;20315;20213;-20032;20313;20211;1000000057;20033;20055;-1098;1000000014;20061;13010;1000000080;20403;20225;20302;-20000;20003;20000;-20003' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T2147.C112, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T2147.C112, '[^;]+')) + 1) ))) ORDER BY T2147.C1000000161 DESC, T2147.C1 ASC) AR_SQL_Alias$1) WHERE ((AR_RowNumber_Alias$1 > 0) AND (AR_RowNumber_Alias$1 < 1002)) In the above statement "zzz" is the user's ID, which I had to remove for security reasons. Also small rant: on our system (Linux+Oracle) there's several places where the letter "N" gets put into the SQL statement which clearly make it bad SQL. I am assuming this is a bug in the actual log file, not in the SQL or it would never process. William Rentfrow [email protected] Office: 715-204-3061 or 701-232-5697x25 Cell: 715-498-5056 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
