Differences

This shows you the differences between two versions of the page.

--- jira_sql_queries [2018/10/01 04:27]
shabnam
+++ jira_sql_queries [2019/06/11 13:43] (current)
shabnam
@@ Line 62: / Line 62: @@
 === To resolve Missing filter in Agile board  ===
 <code java>
-**SELECT *
+SELECT *
 FROM AO_60DB71_RAPIDVIEW
-WHERE SAVED_FILTER_ID=99999;** <- this is the filter id mentioned in the error
+WHERE SAVED_FILTER_ID=55555; //<- this is the filter id mentioned in the error//
-Take a note of the ID field value (eg.: 999), and delete the reference records from the below tables as below:
+//Take a note of the ID field value (eg.: 999), and delete the reference records from the below tables as below://
-**DELETE
+DELETE
 FROM AO_60DB71_BOARDADMINS
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-**DELETE
+DELETE
 FROM AO_60DB71_DETAILVIEWFIELD
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-**DELETE
+DELETE
 FROM AO_60DB71_ESTIMATESTATISTIC
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-**DELETE
+DELETE
 FROM AO_60DB71_QUICKFILTER
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-**DELETE
+DELETE
 FROM AO_60DB71_STATSFIELD
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-**DELETE
+DELETE
 FROM AO_60DB71_SWIMLANE
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-**DELETE
+DELETE
 FROM AO_60DB71_TRACKINGSTATISTIC
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-**DELETE
+DELETE
 FROM AO_60DB71_WORKINGDAYS
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-**DELETE
+DELETE
 FROM AO_60DB71_CARDCOLOR
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-**DELETE
+DELETE
 FROM AO_60DB71_SUBQUERY
-WHERE RAPID_VIEW_ID=999;**
+WHERE RAPID_VIEW_ID=999;
-Take a note of the IDs from the table **AO_60DB71_COLUMN**
+//Take a note of the IDs from the table AO_60DB71_COLUMN//
+SELECT * FROM AO_60DB71_COLUMN
+WHERE RAPID_VIEW_ID=999;
-**SELECT * FROM AO_60DB71_COLUMN
+Example results: 1,11,111.
-WHERE RAPID_VIEW_ID=999;**
-Example results: 9,99,9999.
+//Delete these references in table: AO_60DB71_COLUMNSTATUS//
-**Delete these references in table: AO_60DB71_COLUMNSTATUS
 DELETE FROM AO_60DB71_COLUMNSTATUS
-WHERE COLUMN_ID IN (9,99,9999);**
+WHERE COLUMN_ID IN (1,11,111);
+//Then delete the primary record of AO_60DB71_COLUMN table//
+DELETE FROM AO_60DB71_COLUMN
+WHERE RAPID_VIEW_ID=999;
+//Now finally delete the record in primary table://
+DELETE
+FROM AO_60DB71_RAPIDVIEW
+WHERE SAVED_FILTER_ID=55555;
+</code>
+=== Get users in a group  ===
+<code java>
+select distinct(U.display_name), U.lower_user_name, U.email_address, G.parent_name
+from cwd_membership G, cwd_user U
+where G.parent_name = <<group_name>>
+and G.lower_child_name = U.lower_user_name
+and U.active='1'
+order by U.display_name;
+</code>
+==== Get filternames in a dashboard ====
+<code java>
+SELECT pp.id, pp.username, pp.pagename, s.filtername, s.id, gup.USERPREFVALUE FROM portalpage pp
+JOIN portletconfiguration pc on pp.id = pc.portalpage
+JOIN gadgetuserpreference gup on pc.ID = gup.portletconfiguration
+JOIN searchrequest s on gup.userprefvalue=s.filtername
 </code>

PawelWiki

User Tools

Site Tools

Differences

Page Tools