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

Categories: Database; Tagged with: ; @ 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/

SSIS: Trim the Join Fields for Merge Join

Categories: Database; Tagged with: ; @ March 13th, 2013 19:37

Kindly remind you:

Do trim the join fields for your ‘MergeJoin’:

Otherwise it may take hours to debug!!

image

 

 

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

TSQL: Get the length of String including/excluding trailing spaces

Categories: Database; Tagged with: ; @ January 16th, 2013 11:53

LEN (Transact-SQL)
Returns the number of characters of the specified string expression, excluding trailing blanks.
Note: To return the number of bytes used to represent an expression, use the DATALENGTH function.

http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx

DATALENGTH (Transact-SQL)

Returns the number of bytes used to represent any expression.

 

SQLs:

DECLARE @STR VARCHAR(32);
SET @STR = ’12    ‘
SELECT LEN(@STR)  — 2
SELECT DATALENGTH(@STR); –6

SSIS: 在OLE DB Source SQL Command中使用Variable | Using variable in the SQL Command of OLE DB Source

Categories: Database; Tagged with: ; @ December 14th, 2012 19:46

需求:

SELECT
    ACCOUNT,
    DTCREATED
FROM USERS
WHERE DTCREATED > ?

? 来源于某Variable

解决方案:

1.  输入SQL:

image

注意 Data access mode不是: from variabel。

2. 点击Parameters… 在弹出对话框中选择变量即可。

SSIS: 将SQL结果存入变量 Select SQL result into variable

Categories: Database; Tagged with: ; @ December 14th, 2012 18:42

1. 修改SQL Task 的Result Set属性

image

2. 在ResultSet tab下选择要输出的Column及对应的Variable:

image

Older Posts



// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.