SQL

JIRA: How to Select the user and groups mapping 获得用户与用户组关联列表

Categories: Development Notes; Tagged with: JIRA • SQL; @ May 28th, 2013 10:09

Requirement:

Export the users and group mapping list.

Solution:

Seems there’s no feature / plugins for this requirement. If you can access the database, then everything is easy:

SELECT member.[ID] ,[membership_type] ,[group_type] ,[parent_name] ,[lower_parent_name] ,[child_name] ,[lower_child_name] ,member.[directory_id], jira_user.display_name FROM [jiradb].[jiraschema].[cwd_membership] member left join [jiradb].[jiraschema].[cwd_user] jira_user ON member.child_name = jira_user.user_name order by parent_name

SQL Server: Query Result Order by ‘IN’ Conditions/Filter 按照IN条件排序

Categories: Database; Tagged with: SQL • SQL Server; @ May 2nd, 2013 12:12

Requirement:

Select some data using ‘IN’ clause, the result should keep the same order as the in conditions.

Solution: Use CharIndex in Order by


SELECT SECCODE, SECSNAME FROM SECURITY 
WHERE SECCODE IN
    ('1U68',
    '1F77')
ORDER BY 
CharIndex(LTRIM(RTRIM([SECCODE])), '1U68, 1F77')

In MySQL, you can use “Order By Field()
http://www.electrictoolbox.com/mysql-order-specific-field-values/

WordPress: batch deleting spam comments 批量删除垃圾回复

Categories: Development Notes • WordPress; Tagged with: MySQL • SQL • WordPress; @ March 16th, 2013 10:50

Use sql to delete all the spam comments quickly:

Status of comment

SELECT DISTINCT comment_approved  FROM `x_comments`

Results:

0 – Pending

1 – approved

spam – spam

trash – trash

Deleting SQL

–delete spam & trash:

DELETE FROM `dbName`.`x_comments` WHERE comment_approved = 'spam' or comment_approved = 'trash'

--delete pending:
DELETE FROM `dbName`.`x_comments` WHERE comment_approved = '0'

SSIS: Trim the Join Fields for Merge Join

Categories: Database; Tagged with: ETL • SQL • SQL Server • SSIS; @ March 13th, 2013 19:37

Kindly remind you:

Do trim the join fields for your ‘MergeJoin’:

Otherwise it may take hours to debug!!

SELECT LTRIM(RTRIM(seccode)) AS seccode….

TSQL: Converting/Casting String between Unicode and Non-Unicode

Categories: Database; Tagged with: SQL • TSQL; @ January 16th, 2013 12:04

I got an Error in a SSIS package:

cannot convert between unicode and non-unicode string data types.

We can use one component called ‘DataConversion’, but I think directly convert the type in SQL will be better.

SQL:

DECLARE @str_unicode NVARCHAR(32);
SET @str_unicode = NCHAR(9734);
SELECT @str_unicode

— Using CAST
SELECT CAST(@str_unicode AS VARCHAR(32))

— Using Convert
SELECT CONVERT(VARCHAR(32), @str_unicode)

Result:

Older Posts