一條好使好用既Oracle SQL

// get all user photo ,then,one user only select userMaxRecord photos
sql = " select * from ("+
"SELECT p.id,p.name,p.click_total,p.file_id,p.column_id,p.user_id,p.district_id,u.nickname,c.name as column_name "+
",row_number() over (partition by p.user_id "+ sqlOrder+") rnum "+
" FROM album_photo_t p left join album_column_t c on p.column_id=c.id left join user_t u on p.user_id = u.user_id"+
" WHERE p.record_state >=0 AND c.type_id="+AlbumDAO.TYPE_ID_ALBUM + " AND ";

sql += sqlPrivate;
sql += sqlOrder;
sql +=") where rnum <= "+userMaxRecord;

//另一條做reference
sql = " select a.QUEST_ID, a.NAME, a.TYPE, a.CAN_REPEAT, nvl(b.STATUS, 'NEW') as STATUS from ( "
+ " ( select * from QUEST_T where TYPE = ? ) a "
+ " left join "
+ " ( select * from "
+ " ( select QUEST_ID, USER_ID, STATUS, row_number() over (partition by QUEST_ID order by START_TIME desc) rnum from USER_QUEST_T where USER_ID = ? ) "
+ " where rnum <=1 ) b "
+ " on a.QUEST_ID = b.QUEST_ID ) order by QUEST_ID ";

再加送一篇「商務應酬葡萄酒知識(下)」

內容唔多講，主要都係教導如果分辨葡萄酒既等級
一齊學做個有內涵同埋有深度既人啦 ;)

Answering Complaint Letter 教學

各位在職人仕可能在佰忙中，偶爾需要處理一些投訴信
卻一下子忘記了該有的格式和信件的風格；
或者要面對公開考試的莘莘學子，每隔一兩年，在Writing考卷中的其中一條題目，
要求閣下跟據題目中的scenario的主角，寫一封信去回應客人的complaint letter
剛好，今天看「招職」的時候，便教導了回應投訴信當中該有的因素
於是本人便把該篇文章scan了下來，希望與大家分享

Convert BLOB to plain text (Oracle)

今日幾經辛苦，終於搵到個方法
可以直接於SQL Developer上面打完一個select statement之後，直接將BLOB格式的內容 display為plain text格式，範例如下:
select POST_ID,
POST_SUBJECT,
UTL_RAW.CAST_TO_NVARCHAR2(DBMS_LOB.SUBSTR(POST_TEXT, 2000,1))POST_TEXT
from JFORUM_POSTS_TEXT;

p.s. POST_TEXT就是被設為BLOB格式的COLUMN
Remark: 但是，這個做法有個限制，就是轉做varchar2 / nvharchar2之後，size無法大於2000