SELECT CAST(CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1
ELSE 0 END AS bit) as Salable, *
FROM Product
===============================
The case statement is your friend in this situation, and takes one of two forms:
The simple case:
SELECT CASE
WHEN
ELSE
END
FROM
The extended case:
SELECT CASE WHEN
WHEN
ELSE
END
FROM
You can even put case statements in an order by clause for really fancy ordering.
===========================
SELECT *,
Salable =
CASE
WHEN (Obsolete = 'N' AND InStock = 'Y) THEN true
ELSE false
END
FROM Product
Excel Interview Basic Question
EXCEL 參照函數 - OFFSET()
分類：基礎篇
2009/07/18 16:07
EXCEL 的參照含數，個人認為最複雜的莫過於OFFSET了，但是，也是做好用的
一般無法用LOOKUP、VLOOKUP、HLOOKUP()解決的，在使用OFFSET()函數後，大都可以獲得解決
當然得搭配其他函數，如COLUMN()、ROW()、MATCH()....
因為OFFSET()，就是依參數指定的位移行列數，傳回指定儲存格~ 如果可以學好OFFSET()，函數功力有如吃下大補丸，功力倍增喔~~~
OFFSET() 函數功能
傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址
傳回的參照位址可以是單一個儲存格或一個儲存格範圍，也就是可以指定要傳回來的列數和欄數
語法
OFFSET(reference, rows, cols, height, width)
Reference 是個參照位址，它是您用以計算位移結果的起始位置，簡單的說，就像是定位點
Rows 是用來計算定位點儲存格要往上(負數)或往下(正數)移動的列數
例如 rows 值為 5，即定位點往下五列，rows 值為 -8，即定位點往上八列
Cols 是用來計算定位點儲存格要往左(負數)或往右(正數)移動的欄數
例如 cols 值為 3，即定位點往右三欄，cols 值為 4，即定位點往左四欄
Height 是設定傳回的參照位址範圍高度 (範圍的列數) 的數值，此引數必須是正數
Width 是設定傳回的參照位址範圍寬度 (範圍的欄數) 的數值，此引數必須是正數
注意事項~
如果根據所指定的儲存格位址、列距及欄距而算出的參照位址超出了工作表時，則 OFFSET 函數傳回錯誤值 #REF!
如果 height 或 width 引數不可為負數，如果被省略，則使用 reference 引數的高度或寬度
產品名稱
價格
購買地點
顯示器
2000
台中
主機板
1000
彰化
外殼
800
雲林
函數
結果
說明
=OFFSET(A1,3,2,1,1)
雲林
顯示A1儲存格往下三列，往右兩欄的一個儲存格範圍
=SUM(OFFSET(A1,1,1,3,1))
3800
對A1往下一列往右一欄，三列高一欄寬的範圍(即B2:B4)，加總計算
=OFFSET(C3:E5,0,-3,3,3)
#REF!
因為引用範圍已超出工作表，所以傳回錯誤值 #REF!
http://tw.myblog.yahoo.com/vincent-excel/article?mid=-2&next=1164&l=a&fid=27
分類：基礎篇
2009/07/18 16:07
EXCEL 的參照含數，個人認為最複雜的莫過於OFFSET了，但是，也是做好用的
一般無法用LOOKUP、VLOOKUP、HLOOKUP()解決的，在使用OFFSET()函數後，大都可以獲得解決
當然得搭配其他函數，如COLUMN()、ROW()、MATCH()....
因為OFFSET()，就是依參數指定的位移行列數，傳回指定儲存格~ 如果可以學好OFFSET()，函數功力有如吃下大補丸，功力倍增喔~~~
OFFSET() 函數功能
傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址
傳回的參照位址可以是單一個儲存格或一個儲存格範圍，也就是可以指定要傳回來的列數和欄數
語法
OFFSET(reference, rows, cols, height, width)
Reference 是個參照位址，它是您用以計算位移結果的起始位置，簡單的說，就像是定位點
Rows 是用來計算定位點儲存格要往上(負數)或往下(正數)移動的列數
例如 rows 值為 5，即定位點往下五列，rows 值為 -8，即定位點往上八列
Cols 是用來計算定位點儲存格要往左(負數)或往右(正數)移動的欄數
例如 cols 值為 3，即定位點往右三欄，cols 值為 4，即定位點往左四欄
Height 是設定傳回的參照位址範圍高度 (範圍的列數) 的數值，此引數必須是正數
Width 是設定傳回的參照位址範圍寬度 (範圍的欄數) 的數值，此引數必須是正數
注意事項~
如果根據所指定的儲存格位址、列距及欄距而算出的參照位址超出了工作表時，則 OFFSET 函數傳回錯誤值 #REF!
如果 height 或 width 引數不可為負數，如果被省略，則使用 reference 引數的高度或寬度
產品名稱
價格
購買地點
顯示器
2000
台中
主機板
1000
彰化
外殼
800
雲林
函數
結果
說明
=OFFSET(A1,3,2,1,1)
雲林
顯示A1儲存格往下三列，往右兩欄的一個儲存格範圍
=SUM(OFFSET(A1,1,1,3,1))
3800
對A1往下一列往右一欄，三列高一欄寬的範圍(即B2:B4)，加總計算
=OFFSET(C3:E5,0,-3,3,3)
#REF!
因為引用範圍已超出工作表，所以傳回錯誤值 #REF!
http://tw.myblog.yahoo.com/vincent-excel/article?mid=-2&next=1164&l=a&fid=27
Sql Statlement
$query = 'SELECT fl.user_id, fl.friend_id, fl.status frnstatus, fl.date_created, fl.status-1 as frnstatus2,
(CASE WHEN (u.logintime < (SELECT datecreated
FROM tbl_message
WHERE (receiver_id = '.$parameters[0]->user_id.' AND sender_id=fl.friend_id) ORDER BY datecreated DESC LIMIT 1)) THEN "1" ELSE "0" END) as LiveChatTime,
(SELECT COUNT(*) FROM tbl_message WHERE (receiver_id = '.$parameters[0]->user_id.' AND sender_id=fl.friend_id) AND is_read="0") AS TotalUnreadMsg,
u.user_name, u.imageurl, u.date_birth,u.status, u.status_field,
(1.60934*(3959 * ACOS(COS(RADIANS('.$parameters[0]->latitude.')) * COS(RADIANS(u.curlat)) * COS(RADIANS(u.curlon) - RADIANS('.$parameters[0]->longitude.')) +
SIN(RADIANS('.$parameters[0]->latitude.')) * SIN(RADIANS(u.curlat)) ))) AS distance
FROM tbl_friendlist fl
INNER JOIN users u ON (fl.friend_id=u.user_id)
WHERE fl.user_id = '.$parameters[0]->user_id.' AND fl.is_block<>1
AND u.invisible NOT IN (2, 3)'.$search.'
UNION
SELECT f2.user_id, f2.friend_id, f2.status AS frnstatus, f2.date_created, f2.status AS frnstatus2,
(CASE WHEN (u.logintime < (SELECT datecreated
FROM tbl_message
WHERE (receiver_id = '.$parameters[0]->user_id.' AND sender_id=f2.user_id) ORDER BY datecreated DESC LIMIT 1)) THEN "1" ELSE "0" END) as LiveChatTime,
(SELECT COUNT(*) FROM tbl_message WHERE (receiver_id = '.$parameters[0]->user_id.' AND sender_id=f2.user_id) AND is_read="0") AS TotalUnreadMsg,
u.user_name, u.imageurl, u.date_birth,u.status AS online, u.status_field,
(1.60934*(3959 * ACOS(COS(RADIANS('.$parameters[0]->latitude.')) * COS(RADIANS(u.curlat)) * COS(RADIANS(u.curlon) - RADIANS('.$parameters[0]->longitude.')) +
SIN(RADIANS('.$parameters[0]->latitude.')) * SIN(RADIANS(u.curlat)) ))) AS distance
FROM tbl_friendlist f2
INNER JOIN users u ON (f2.user_id=u.user_id)
WHERE f2.friend_id = '.$parameters[0]->user_id.' AND f2.is_block<>1
AND u.invisible NOT IN (2, 3)'.$search.'
ORDER BY frnstatus2 DESC, TotalUnreadMsg DESC, LiveChatTime DESC, distance ASC
LIMIT ' . $limit . '
OFFSET ' . $offset;
(CASE WHEN (u.logintime < (SELECT datecreated
FROM tbl_message
WHERE (receiver_id = '.$parameters[0]->user_id.' AND sender_id=fl.friend_id) ORDER BY datecreated DESC LIMIT 1)) THEN "1" ELSE "0" END) as LiveChatTime,
(SELECT COUNT(*) FROM tbl_message WHERE (receiver_id = '.$parameters[0]->user_id.' AND sender_id=fl.friend_id) AND is_read="0") AS TotalUnreadMsg,
u.user_name, u.imageurl, u.date_birth,u.status, u.status_field,
(1.60934*(3959 * ACOS(COS(RADIANS('.$parameters[0]->latitude.')) * COS(RADIANS(u.curlat)) * COS(RADIANS(u.curlon) - RADIANS('.$parameters[0]->longitude.')) +
SIN(RADIANS('.$parameters[0]->latitude.')) * SIN(RADIANS(u.curlat)) ))) AS distance
FROM tbl_friendlist fl
INNER JOIN users u ON (fl.friend_id=u.user_id)
WHERE fl.user_id = '.$parameters[0]->user_id.' AND fl.is_block<>1
AND u.invisible NOT IN (2, 3)'.$search.'
UNION
SELECT f2.user_id, f2.friend_id, f2.status AS frnstatus, f2.date_created, f2.status AS frnstatus2,
(CASE WHEN (u.logintime < (SELECT datecreated
FROM tbl_message
WHERE (receiver_id = '.$parameters[0]->user_id.' AND sender_id=f2.user_id) ORDER BY datecreated DESC LIMIT 1)) THEN "1" ELSE "0" END) as LiveChatTime,
(SELECT COUNT(*) FROM tbl_message WHERE (receiver_id = '.$parameters[0]->user_id.' AND sender_id=f2.user_id) AND is_read="0") AS TotalUnreadMsg,
u.user_name, u.imageurl, u.date_birth,u.status AS online, u.status_field,
(1.60934*(3959 * ACOS(COS(RADIANS('.$parameters[0]->latitude.')) * COS(RADIANS(u.curlat)) * COS(RADIANS(u.curlon) - RADIANS('.$parameters[0]->longitude.')) +
SIN(RADIANS('.$parameters[0]->latitude.')) * SIN(RADIANS(u.curlat)) ))) AS distance
FROM tbl_friendlist f2
INNER JOIN users u ON (f2.user_id=u.user_id)
WHERE f2.friend_id = '.$parameters[0]->user_id.' AND f2.is_block<>1
AND u.invisible NOT IN (2, 3)'.$search.'
ORDER BY frnstatus2 DESC, TotalUnreadMsg DESC, LiveChatTime DESC, distance ASC
LIMIT ' . $limit . '
OFFSET ' . $offset;
2010年8月21日 星期六
IPhone push notification provider server
http://blog.boxedice.com/2009/07/10/how-to-build-an-apple-push-notification-provider-server-tutorial/
Needa follow the 10 steps to generate .pem file, instead of simply choose .pem file in export format.
Make sure you have selected My Certificates in the left pane of Keychain access, in order to export as .p12 file
Workable .pem files look like this:
Bag Attributes
friendlyName: Apple Production Push Services: A9G1EK2CDF:8Q2MGGN2Q9
localKeyID: A1 F2 01 89 13 9B Z7 E9 90 BE AF 42 94 96 08 21 38 A5 98 AE
subject=/UID=com.company.name.myid/CN=Apple Production Push Services: A9G1EK2CDF:8Q2MGGN2Q9/C=US
issuer=/C=US/O=Apple Inc./OU=Apple Worldwide Developer Relations/CN=Apple Worldwide Developer Relations Certification Authority
-----BEGIN CERTIFICATE-----
data
-----END CERTIFICATE-----
-----BEGIN RSA PRIVATE KEY-----
data
Needa follow the 10 steps to generate .pem file, instead of simply choose .pem file in export format.
Make sure you have selected My Certificates in the left pane of Keychain access, in order to export as .p12 file
Workable .pem files look like this:
Bag Attributes
friendlyName: Apple Production Push Services: A9G1EK2CDF:8Q2MGGN2Q9
localKeyID: A1 F2 01 89 13 9B Z7 E9 90 BE AF 42 94 96 08 21 38 A5 98 AE
subject=/UID=com.company.name.myid/CN=Apple Production Push Services: A9G1EK2CDF:8Q2MGGN2Q9/C=US
issuer=/C=US/O=Apple Inc./OU=Apple Worldwide Developer Relations/CN=Apple Worldwide Developer Relations Certification Authority
-----BEGIN CERTIFICATE-----
data
-----END CERTIFICATE-----
-----BEGIN RSA PRIVATE KEY-----
data
Hedge Fund Interview - Junior Programmer 雜記
Company : Inxxxx Parxxxx
Self Introduction -- key words " Technical analysis"
vba experience
vlookup hlookup loopholes
reason of working for financial institutions
describe career path briefly
first parts : english self intro and some follow-up questoin
second part: chinese casual interview with his colleagues.
Self Introduction -- key words " Technical analysis"
vba experience
vlookup hlookup loopholes
reason of working for financial institutions
describe career path briefly
first parts : english self intro and some follow-up questoin
second part: chinese casual interview with his colleagues.
2009年12月26日 星期六
JSP jsp-api
Unable to read TLD “META-INF/c.tld” from JAR file
I installed the lastest version of JasperServer on my my brand new server based on tomcat6 with jdk6 but on the first page I got the message “Unable to read TLD “META-INF/c.tld” from JAR file…”.
My first try was to downgrade tomcat and jdk to a previous version andit seemed to work. I first thought in a bug somewhere in tomcat and after some time (20 minutes of my precious time!!!!!) my attention got capured by a message in catalina.out “.. file geronimo.jar skiped … contains Servet violation spec….. ”
So JasperServer contains in WEB-INF/lib some servlet libraries?!?! Could be possible it’s not a war made by me so somebody could have made some mistake. Listing the files in WEB-INF/lib i found the entire world of web libraries, included jsp-api. Yes this is the problem!
Tomcat excludes genonimo.jar because it contains Servlet api’s but loads jsp-api located in WEB-INF/lib because no checkis made. No jsp can be compiled because jasper (not jasper reports) compiles from a classloader and jsp-api are located in a different classloader.
Removig jsp-api everything works fine again.
I installed the lastest version of JasperServer on my my brand new server based on tomcat6 with jdk6 but on the first page I got the message “Unable to read TLD “META-INF/c.tld” from JAR file…”.
My first try was to downgrade tomcat and jdk to a previous version andit seemed to work. I first thought in a bug somewhere in tomcat and after some time (20 minutes of my precious time!!!!!) my attention got capured by a message in catalina.out “.. file geronimo.jar skiped … contains Servet violation spec….. ”
So JasperServer contains in WEB-INF/lib some servlet libraries?!?! Could be possible it’s not a war made by me so somebody could have made some mistake. Listing the files in WEB-INF/lib i found the entire world of web libraries, included jsp-api. Yes this is the problem!
Tomcat excludes genonimo.jar because it contains Servlet api’s but loads jsp-api located in WEB-INF/lib because no checkis made. No jsp can be compiled because jasper (not jasper reports) compiles from a classloader and jsp-api are located in a different classloader.
Removig jsp-api everything works fine again.
訂閱：
文章 (Atom)