Remember about the more complex SQL

2010-07-19  来源:本站原创  分类:Database  人气:166 

Recently used in the application of two tables:

XZ_VIEW_INDEX_SJFW: the main table

ACF_SYS_GWATTACH: text / accessories store table

Body or an attachment to Blob fields are stored in the body / accessories store in the table, the table FILE_ID field and the main table ZW (body number) or the FJ (annex number) is associated. But the main table in the ZW and FJ field is VARCHAR type, and text / accessories store in the table FILE_ID field is numeric, and the main table ZW, FJ two fields may be empty, but also Perhaps it is, it is not directly related to query records from three tables.

Now required is to use a SQL query data, if the value of ZW or FJ is required from the body / accessories store in the table to the corresponding text or attachments are taken out.

In order to prevent non-numeric fields with numeric values to compare lead to errors, we first wrote a custom function is used to determine whether a value is a number:

CREATE OR REPLACE FUNCTION IsNum (p_in VARCHAR2) RETURN NUMBER AS
  val NUMBER;
BEGIN
  select NVL(length(translate(trim(p_in),' +-.0123456789',' ')),0) into val from dual;
  if val=0 then
    return 1;
  else
    return 0;
  end if;
END IsNum;

Then went to great lengths to piece together a SQL, look here remember, to forget:

-- Issuing randomly
select * from (
  select
      A.MODULE_CODE,
      A.BIZ_CODE,
      A.OP_TYPE,
      A.LASTTIME,
      A.BT,
      A.WH,
      A.ZTC,
      A.CYCL,
      A.SJ,
      A.ZW,
      A.FJ,
      decode(isNum(A.ZW), 1,  B.EXT_NAME, -1) " Body extension  ",
      decode(isNum(A.ZW), 1,  B.FILE_NAME, -1) " The body file  ",
      decode(isNum(A.ZW), 1,  B.FILE_CONTENT,  empty_blob()) " Body  ",
      decode(isNum(A.FJ), 1,  C.EXT_NAME, -1) " Attachment extension  ",
      decode(isNum(A.FJ), 1,  C.FILE_NAME, -1) " Attachment filename  ",
      decode(isNum(A.FJ), 1,  C.FILE_CONTENT, empty_blob()) " Annex  "
  from UA.XZ_VIEW_INDEX_SJFW A, UB.ACF_SYS_GWATTACH B, UB.ACF_SYS_GWATTACH C
    where B.FILE_ID(+)=decode(isNum(A.ZW), 1, A.ZW, null)
      and C.FILE_ID(+)=decode(isNum(A.FJ), 1, A.FJ, null)
  order by dbms_random.value
)
where rownum <= 10
相关文章
  • Remember about the more complex SQL 2010-07-19

    Recently used in the application of two tables: XZ_VIEW_INDEX_SJFW: the main table ACF_SYS_GWATTACH: text / accessories store table Body or an attachment to Blob fields are stored in the body / accessories store in the table, the table FILE_ID field

  • Complex SQL statement question 2010-11-01

    TABLE t2 TABLE t1 and the same table structure, but the data are different: t1: create table t1 ( id int, tm datetime, data varchar (200) ) Data: 12009-11-11 data 1 12009-11-12 data 2 22009-11-12 data 3 32009-11-12 data 4 t2: create table t2 ( id int

  • Commonly used in more complex SQL statements 2011-06-01

    1. Dynamically update a field update ot_dstrregtaxpayerbasic set taxofficialcode = (select taxofficialcode from tb_basic_enterprisebasicinfo where tb_basic_enterprisebasicinfo.taxregcode = ot_dstrregtaxpayerbasic.taxregcode) 2. By date sort N records

  • Used more complex SQL statements 2011-06-01

    1 Dynamic update a field update ot_dstrregtaxpayerbasic set taxofficialcode = (select taxofficialcode from tb_basic_enterprisebasicinfo where tb_basic_enterprisebasicinfo.taxregcode = ot_dstrregtaxpayerbasic.taxregcode) 2 sorted by date, read the fir

  • Oracle SQL Optimization 2010-02-15

    SQL optimization should be adjusted from five aspects: 1. Remove unnecessary large table full table scans 2. Cached small table full table scans 3. Inspection optimize the use of the index 4. Test the connection optimization technology 5. Cost to min

  • SQL Optimization 34 [SQL Collection] 2010-03-10

    Not only do we have to write SQL, but make sure to write good performance SQL, the following is the author study, extract, and summarize some of the information to share with you! (1) Select the most efficient sequence table name (only the effective

  • Experience in SQL optimization 2010-03-26

    Not only do we have to write SQL, but make sure to write good performance SQL, the following is the author study, extract, and summarize some of the information to share with you! (1) Select the most efficient sequence table name (only the effective

  • (Switch) SQL optimization recommendations 34 2010-02-09

    (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in the FROM clause of the table name, FROM clause written in the final table (base t

  • ORACLE SQL statement optimization summarized in 2010-02-23

    ORACLE SQL statement optimization summarized in (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in the FROM clause of the table name

  • Optimization of SQL statement based on the index of 2010-03-15

    1 Introduction 2 2 Master 2 3 Eighteen 3 The first metacarpal to avoid the operation of the column 3 The second metacarpal to avoid unnecessary type conversions 4 The third metacarpal increased to limit the query 4 Get rid of the fourth metacarpal as

  • sql statements execution order - Introduction 2010-04-08

    Original: http://www.blogjava.net/youling/archive/2009/03/06/243177.html PS: as I left the last part of the original modification This seems good: http://www.phpchina.com/html/96/1296-4555.html http://maxclapton.comoj.com/?p=210 But the select first

  • SQL Language Arts (11) specializes in trick: to save the response time 2010-04-12

    This chapter discusses table design, process architecture and even demands are unreasonable under the premise of the optimization of complex SQL Database performance depends on a reasonable database design, followed by a clear strategy and relies on

  • Efficient SQL Summary (change) 2010-04-18

    Not only will we have to do to write SQL, but also achieve excellent performance to write SQL, the following is the author study, extract, and summarize some of the information to share with you! (1) Select the most efficient sequence table name (onl

  • SQL join query processing and multi-conditions 2010-04-29

    Today was a complex SQL, multi-criteria queries First got it. select * from (select hsff as HSFF, fylb as FYLB, sum(SKFY + TKFY) as FY, sum(SKBS - TKBS) as BS, sum(TKFY) as TKFY, sum(TKBS) as TKBS, sum(SKFY) as SKFY, sum(SKBS) as SKBS from (select b.

  • Very useful SQL statement optimization 34 2010-05-11

    1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser according to the order processing from right to left the table name FROM clause, FROM clause written in the final table (base table driving tabl

  • Oracle.SQL Server.Sybase.MySQL Performance Optimization 2010-05-22

    Reprinted: http://hi.baidu.com/zhizhesky/blog/item/c0a64190dc061287a977a4d3.html (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in

  • Analysis of the implementation plan optimization SQL <3> ORACLE implementation plan (transfer) 2010-05-27

    Background: In order to better carry out the following elements we must understand some conceptual terms: Sql statement to not share the same The SQL statement parsing Zhong Fu (Yinweijiexi Caozuobijiao costs of resources, can cause performance degra

  • Oracle SQL Performance Tuning 2010-06-07

    ORACLE parser in accordance with the order processing from right to left in the FROM clause of the table name, the FROM clause written in the final table (base table driving table) will be the first to deal with. In the FROM clause contains more than

  • SQL Optimization Tips (Oracle) 2010-06-07

    Not only do we have to write SQL, but make sure to write good performance SQL, the following is the author study, extract, and summarize the Department of Points to share with you! (1) Select the most efficient sequence table name (only the effective

  • Reposted elsewhere - by analyzing the SQL statement execution plan optimization of SQL (summary) 2010-06-30

    By analyzing the SQL statement execution plan optimization of SQL (summary) DBA did almost 7 years, and sentiment among many. In the DBA's daily work, to adjust individual performance to a less challenging when the SQL statement of work. The key lies