This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
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 === | === To resolve Missing filter in Agile board === | ||
<code java> | <code java> | ||
- | **SELECT * | + | SELECT * |
FROM AO_60DB71_RAPIDVIEW | 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 | FROM AO_60DB71_BOARDADMINS | ||
- | WHERE RAPID_VIEW_ID=999; | + | WHERE RAPID_VIEW_ID=999; |
- | **DELETE | + | DELETE |
FROM AO_60DB71_DETAILVIEWFIELD | FROM AO_60DB71_DETAILVIEWFIELD | ||
- | WHERE RAPID_VIEW_ID=999; | + | WHERE RAPID_VIEW_ID=999; |
- | **DELETE | + | DELETE |
FROM AO_60DB71_ESTIMATESTATISTIC | FROM AO_60DB71_ESTIMATESTATISTIC | ||
- | WHERE RAPID_VIEW_ID=999; | + | WHERE RAPID_VIEW_ID=999; |
- | **DELETE | + | DELETE |
FROM AO_60DB71_QUICKFILTER | FROM AO_60DB71_QUICKFILTER | ||
- | WHERE RAPID_VIEW_ID=999; | + | WHERE RAPID_VIEW_ID=999; |
- | **DELETE | + | DELETE |
FROM AO_60DB71_STATSFIELD | FROM AO_60DB71_STATSFIELD | ||
- | WHERE RAPID_VIEW_ID=999; | + | WHERE RAPID_VIEW_ID=999; |
- | **DELETE | + | DELETE |
FROM AO_60DB71_SWIMLANE | FROM AO_60DB71_SWIMLANE | ||
- | WHERE RAPID_VIEW_ID=999; | + | WHERE RAPID_VIEW_ID=999; |
- | **DELETE | + | DELETE |
FROM AO_60DB71_TRACKINGSTATISTIC | FROM AO_60DB71_TRACKINGSTATISTIC | ||
- | WHERE RAPID_VIEW_ID=999; | + | WHERE RAPID_VIEW_ID=999; |
- | **DELETE | + | DELETE |
FROM AO_60DB71_WORKINGDAYS | FROM AO_60DB71_WORKINGDAYS | ||
- | WHERE RAPID_VIEW_ID=999; | + | WHERE RAPID_VIEW_ID=999; |
- | **DELETE | + | DELETE |
FROM AO_60DB71_CARDCOLOR | FROM AO_60DB71_CARDCOLOR | ||
- | WHERE RAPID_VIEW_ID=999; | + | WHERE RAPID_VIEW_ID=999; |
- | **DELETE | + | DELETE |
FROM AO_60DB71_SUBQUERY | 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 | ||
+ | WHERE RAPID_VIEW_ID=999; | ||
- | **SELECT * FROM AO_60DB71_COLUMN | + | Example results: 1,11,111. |
- | WHERE RAPID_VIEW_ID=999; | + | |
- | Example results: 9, | + | //Delete these references in table: AO_60DB71_COLUMNSTATUS// |
- | + | ||
- | **Delete these references in table: AO_60DB71_COLUMNSTATUS | + | |
DELETE FROM 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; | ||
+ | </ | ||
+ | |||
+ | === Get users in a group === | ||
+ | |||
+ | <code java> | ||
+ | select distinct(U.display_name), | ||
+ | from cwd_membership G, cwd_user U | ||
+ | where G.parent_name = << | ||
+ | and G.lower_child_name = U.lower_user_name | ||
+ | and U.active=' | ||
+ | order by U.display_name; | ||
+ | </ | ||
+ | |||
+ | ==== Get filternames in a dashboard ==== | ||
+ | |||
+ | <code java> | ||
+ | SELECT pp.id, pp.username, | ||
+ | JOIN portletconfiguration pc on pp.id = pc.portalpage | ||
+ | JOIN gadgetuserpreference gup on pc.ID = gup.portletconfiguration | ||
+ | JOIN searchrequest s on gup.userprefvalue=s.filtername | ||
</ | </ |