SET LIN 200
COLUMN class_id format a15
COLUMN criteria_id format a20
COLUMN criteria_name format a30
COLUMN class_name format a30
SELECT criteria_id || '' criteria_id,
criteria_name,
class_id,
class_name
FROM (SELECT a.id criteria_id,
a.description criteria_name,
b.value class_id
FROM nodetable a,
propertytable b
WHERE a.parentid = 3642
AND a.id = b.parentid
AND b.propertyid = 53
AND a.id IN (SELECT DISTINCT parentid
FROM admincriteria
WHERE attid = &ATTRIBUTE_ID
AND relop NOT IN ( 9, 10 ))) criterias,
(SELECT n.id cid,
n.description class_name
FROM nodetable n,
(SELECT id
FROM nodetable
WHERE description = '&CLASS_NAME'
AND objtype IN ( 5, 13 )) nc
WHERE n.id = nc.id
OR n.parentid IN (SELECT id
FROM nodetable
WHERE objtype = 14
AND parentid = nc.id)) classes
WHERE classes.cid = criterias.class_id;
SQL>/
Enter value for attribute_id: 1271
old 15: WHERE attid = &ATTRIBUTE_ID
new 15: WHERE attid = 1271
Enter value for class_name: Change Orders
old 22: WHERE description = '&CLASS_NAME'
new 22: WHERE description = 'Change Orders'
CRITERIA_ID CRITERIA_NAME CLASS_ID CLASS_NAME
-------------------- ------------------------------ --------------- ------------------------------
2474421 JieCriteria 6000 Change Orders
SET LIN 200Then you can open the Report object and modify the Query Definition to remove the reference, if you have the privilege.
COLUMN report_id format a10
COLUMN report_name format a40
COLUMN query_id format a10
SELECT report_id || '' report_id,
report_name,
query_id || '' query_id
FROM (SELECT a.id report_id,
A.NAME report_name,
B.TYPE report_class_id,
b.id query_id
FROM REPORT A,
QUERY B
WHERE A.CRITERIA_ID = B.ID
AND B.ID IN (SELECT DISTINCT QUERY_ID
FROM CRITERIA
WHERE ATTR_ID = &ATTRIBUTE_ID
AND RELATIONAL_OP NOT IN ( 9, 10 ))) reports,
(SELECT n.id cid,
n.description class_name
FROM nodetable n,
(SELECT id
FROM nodetable
WHERE description = '&CLASS_NAME'
AND objtype IN ( 5, 13 )) nc
WHERE n.id = nc.id
OR n.parentid IN (SELECT id
FROM nodetable
WHERE objtype = 14
AND parentid = nc.id)) classes
WHERE reports.report_class_id = classes.cid;
SQL>/
Enter value for attribute_id: 1271
old 13: WHERE ATTR_ID = &ATTRIBUTE_ID
new 13: WHERE ATTR_ID = 1271
Enter value for class_name: Change Orders
old 20: WHERE description = '&CLASS_NAME'
new 20: WHERE description = 'Change Orders'
REPORT_ID REPORT_NAME QUERY_ID
---------- ---------------------------------------- ----------
14325816 Can you guess who I am? (d) 14325817
14325809 Can you guess who I am? (c) 14325811
SET LIN 200You can ask the user "admin" to modify his Search criteria. Note: if query_name is null or the format is like loginid plus a number like "admin1401373750229", it means they are temporary Advanced Search. They must be deleted manually via SQL:
COLUMN loginid format a15
COLUMN query_name format a40
COLUMN query_id format a10
SELECT u.loginid,
query_id || '' query_id,
query_name
FROM (SELECT A.ID query_id,
A.TYPE query_class_id,
A.NAME query_name,
A.owner ownerid
FROM QUERY A
WHERE A.ID IN (SELECT DISTINCT QUERY_ID
FROM CRITERIA
WHERE ATTR_ID = &ATTRIBUTE_ID
AND RELATIONAL_OP NOT IN ( 9, 10 ))) queries,
(SELECT n.id cid,
n.description class_name
FROM nodetable n,
(SELECT id
FROM nodetable
WHERE description = '&CLASS_NAME'
AND objtype IN ( 5, 13 )) nc
WHERE n.id = nc.id
OR n.parentid IN (SELECT id
FROM nodetable
WHERE objtype = 14
AND parentid = nc.id)) classes,
agileuser u
WHERE queries.query_class_id = classes.cid
AND queries.ownerid = u.id
AND (query_name is null OR query_id || '' <> query_name);
sql>/
Enter value for attribute_id: 1271
old 11: WHERE ATTR_ID = &ATTRIBUTE_ID
new 11: WHERE ATTR_ID = 1271
Enter value for class_name: Change Orders
old 18: WHERE description = '&CLASS_NAME'
new 18: WHERE description = 'Change Orders'
LOGINID QUERY_ID QUERY_NAME
--------------- ---------- ----------------------------------------
admin 14321593
admin 14325249
admin 14325817 admin1401373750229
admin 14325848 Can you guess who I am? (a)
admin 14325896 Can you guess who I am? (b)
delete query where id=&query_id;
delete criteria where query_id=&query_id;
delete select_list where query_id=&query_id;
commit;