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