SQL Server: Query Result Order by ‘IN’ Conditions/Filter 按照IN条件排序
Categories: Database; Tagged with: SQL • SQL Server; @ May 2nd, 2013 12:12Requirement:
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: ETL • SQL • SQL Server • SSIS; @ March 13th, 2013 19:37Kindly remind you:
Do trim the join fields for your ‘MergeJoin’:
Otherwise it may take hours to debug!!
SELECT LTRIM(RTRIM(seccode)) AS seccode….
TSQL: Get the length of String including/excluding trailing spaces
Categories: Database; Tagged with: SQL • SQL Server • TSQL; @ January 16th, 2013 11:53LEN (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: ETL • SQL Server • SSIS; @ December 14th, 2012 19:46需求：
SELECT
ACCOUNT,
DTCREATED
FROM USERS
WHERE DTCREATED > ?
? 来源于某Variable
解决方案：
1. 输入SQL：
注意 Data access mode不是： from variabel。
2. 点击Parameters… 在弹出对话框中选择变量即可。
SSIS: 将SQL结果存入变量 Select SQL result into variable
Categories: Database; Tagged with: ETL • SQL Server • SSIS; @ December 14th, 2012 18:421. 修改SQL Task 的Result Set属性
2. 在ResultSet tab下选择要输出的Column及对应的Variable：
