ASE应用开发与系统维护管理2.pptVIP

ASE15.0 应用开发与系统维护管理 Name:王明志 议程 ASE架构 ASE安装与客户端配置 系统维护管理 SQL语句规则和优化 应用开发调优 应用开发基础 Controlling Output Presentation It is often desirable to alter the presentation style of a single row of data Default presentation consists of a single line of output Spreading that data over multiple rows is a common goal For example: title_id type pub_id -------- ---------- ------------------------------ ------ BU1032 business The Busy Executives Database 1389 becomes: Book ID: BU1032 ----------------------------------- Type: business Title: The Busy Executives Database Guide Publisher: 1389 Inserting Control Characters Use the char( ) function to insert control characters Add spaces with SPACE(n) For example: Padding Data Values It is sometimes necessary to pad data values with leading characters For example: The process: Step 1: Define the pad The replicate command can be useful here Step 2: Concatenate the pad with the column concatenation requires character strings, so convert the column if necessary Step 3: Return the required number of characters select the rightmost characters to capture the entire column Padding Data Values (continued) Syntax: RIGHT(REPLICATE(“x”,n)+CONVERT(varchar(n), expression),n) Example: Decoding Columns Joins can be costly for performance Sometimes data can be converted in the code to more meaningful values Use case logic: CASE expression WHEN expression THEN expression [WHEN expression THEN expression] [ELSE expression] END Sample Decode 1 select 2 Store = case stor_id 3 when 5023 then Thoreau 4 when 7131 then Doc-U-Mat 5 when 7896 then Fricative 6 when 8042 then Bookbeat 7 else Unknown end 8 , ord_num, title_id, qty from salesdetail Store ord_num title_id qty --------- -------------------- -------- ------ Fricative 234518 TC3218 75 Fricative