Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Unanswered: Views//anyone know a simplier way to write the following;

    CREATE OR REPLACE VIEW v_field_audit
    AS
    SELECT FIELD_AUDIT_ID,
    TABLE_TYPE,
    TABLE_ID,
    FIELD_NAME,
    decode(instr(old_value,chr(124)),0,old_value,subst r(old_value,1,instr(old_value,chr(124),1,1)-1)) OLD1,
    decode(instr(old_value,chr(124)),0,null,substr(old _value,instr(old_value,chr(124),1,1)+1,decode(inst r(old_value,chr(124),1,2),0,length(old_value)-instr(old_value,chr(124),1,1),instr(old_value,chr( 124),1,2)-instr(old_value,chr(124),1,1)+1))) OLD2,
    decode(instr(old_value,chr(124)),0,null,substr(old _value,instr(old_value,chr(124),1,2)+1,decode(inst r(old_value,chr(124),1,3),0,decode(instr(old_value ,chr(124),1,2),0,null,length(old_value)-instr(old_value,chr(124),1,2)),instr(old_value,chr (124),1,3)-instr(old_value,chr(124),1,2)+1))) OLD3,

    decode(instr(old_value,chr(124)),0,null,substr(old _value,instr(old_value,chr(124),1,3)+1,decode(inst r(old_value,chr(124),1,4),0,decode(instr(old_value ,chr(124),1,3),0,null,length(old_value)-instr(old_value,chr(124),1,3)),instr(old_value,chr (124),1,4)-instr(old_value,chr(124),1,3)+1))) OLD4,

    decode(instr(new_value,chr(124)),0,new_value,subst r(new_value,1,instr(new_value,chr(124),1,1)-1)) NEW1,
    decode(instr(new_value,chr(124)),0,null,substr(new _value,instr(new_value,chr(124),1,1)+1,decode(inst r(new_value,chr(124),1,2),0,length(new_value)-instr(new_value,chr(124),1,1),instr(new_value,chr( 124),1,2)-instr(new_value,chr(124),1,1)+1))) NEW2,
    decode(instr(new_value,chr(124)),0,null,substr(new _value,instr(new_value,chr(124),1,2)+1,decode(inst r(new_value,chr(124),1,3),0,decode(instr(new_value ,chr(124),1,2),0,null,length(new_value)-instr(new_value,chr(124),1,2)),instr(new_value,chr (124),1,3)-instr(new_value,chr(124),1,2)+1))) NEW3,

    decode(instr(new_value,chr(124)),0,null,substr(new _value,instr(new_value,chr(124),1,3)+1,decode(inst r(new_value,chr(124),1,4),0,decode(instr(new_value ,chr(124),1,3),0,null,length(new_value)-instr(new_value,chr(124),1,3)),instr(new_value,chr (124),1,4)-instr(new_value,chr(124),1,3)+1))) NEW4,
    USER_ID,
    CHANGED_ON,
    REASON
    FROM FIELD_AUDIT
    WHERE TABLE_TYPE='P'
    and field_audit_id=138
    Cheers
    Etravels

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How about creating a function to do the string parsing?
    Code:
    CREATE OR REPLACE FUNCTION delimfield
    ( p_string IN VARCHAR2
    , p_delim  IN VARCHAR2
    , p_fieldno IN INTEGER
    ) RETURN VARCHAR2
    IS
      v_pos1 INTEGER := 1;
      v_pos2 INTEGER := INSTR(p_string,p_delim,1,p_fieldno);
      v_len  INTEGER := LENGTH(p_string);
    BEGIN
      IF p_fieldno > 1 THEN
        v_pos1 := INSTR(p_string,p_delim,1,p_fieldno-1)+1;
        IF v_pos1 = 1 THEN 
          RAISE NO_DATA_FOUND;
        END IF;
      END IF;
      IF v_pos2 > 0 THEN
        v_len := v_pos2-v_pos1;
      END IF;
      RETURN SUBSTR(p_string,v_pos1,v_len);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN NULL;
    END;
    /
    Then your view simplifies to:
    Code:
    CREATE OR REPLACE VIEW v_field_audit
    AS
    SELECT FIELD_AUDIT_ID,
    TABLE_TYPE,
    TABLE_ID,
    FIELD_NAME,
    delimfield(old_value,'|',1) OLD1,
    delimfield(old_value,'|',2) OLD2,
    delimfield(old_value,'|',3) OLD3,
    delimfield(old_value,'|',4) OLD4,
    delimfield(new_value,'|',1) NEW1,
    delimfield(new_value,'|',2) NEW2,
    delimfield(new_value,'|',3) NEW3,
    delimfield(new_value,'|',4) NEW4,
    USER_ID,
    CHANGED_ON,
    REASON
    FROM FIELD_AUDIT
    WHERE TABLE_TYPE='P'
    and field_audit_id=138

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •