SQL Copy Function In SQR, Part 2

I wish SQL had a simple copy command. It would insert a new row into a table with almost all the values of an existing row of that table. Last week, we saw how to obtain data dictionary information from an Oracle database. This week our program will help us specify our needs and write SQL for us.

Updating Last Week

I usually finish my program before I write about it but I’ve been busy this month. I started the program, wrote “Part 1,” then finished the program, and wrote “Part 2.” While finishing the program, I found that I needed to change things I wrote about last week. I’ll list them here, and edit my previous blog entry.

First, I need a data_type field in the copy array. Second, the identification of the columns in the unique index was too slow (6 seconds on my system). I changed the three-way join of SYS.COL$, SYS.ICOL$, and SYS.COL$ to a two-way join of SYS.ICOL$ and SYS.COL$.

begin-setup
! The second (occurs) index is addressed by the COLUMN_ID field of the
! ALL_TAB_COLUMNS table, meaning we won’t use 0.
  create-array name=copy size=1
    field=table:char=''
    field=column:char:{num_columns}=''
    field=data_type:char:{num_columns}=''
    field=index:integer:{num_columns}={false}
    field=from_key:char:{num_columns}=''
    field=to_key:char:{num_columns}=''
end-setup

begin-procedure get_index_name
  move {true} to #unique_index_not_found
begin-select
SO2.NAME
SO2.OBJ#
  move {false} to #unique_index_not_found
  move &SO2.NAME to $index_name
 from SYS.OBJ$ SO1, SYS.IND$ SI, SYS.OBJ$ SO2
where SO1.NAME = $table_name
  and SO1.TYPE# = 2 ! 1 = index, 2 = table, 4 = view, 5 = dbms view
  and SI.BO# = SO1.OBJ#
  and bitand(SI.PROPERTY, 1) = 1 ! unique index
  and SO2.OBJ# = SI.OBJ#
end-select
  if #unique_index_not_found
    show $table_name ' does not have a unique index'
  end-if
end-procedure get_index_name

begin-procedure get_column_names
begin-select
COLUMN_NAME
DATA_TYPE
COLUMN_ID
  move &COLUMN_ID to #column_id
  put &COLUMN_NAME &DATA_TYPE into copy(0) column(#COLUMN_ID) data_type(#COLUMN_ID)
 from ALL_TAB_COLUMNS
where TABLE_NAME = $table_name
end-select

begin-select
SC.COL#
  let copy.index(0, &SC.COL#) = {true}
 from SYS.ICOL$ SI, SYS.COL$ SC
where SI.OBJ# = &SO2.OBJ#
  and SC.OBJ# = SI.BO#
  and SC.COL# = SI.COL#
end-select
end-procedure get_column_names

Do What I Mean

The “read my mind” command and the “do what I mean” button are coming, I’m sure, in the next version of Microsoft Office. In the meanwhile, let’s make it as easy as possible to specify our copy requirements. After the human specifies a table, the program can identify the columns.

The next step is to determine which row to copy. The program will scan the table structure and get values for the columns that constitute the unique index. Before it asks the human for the value, it should check the existing rows of the table.

If the key column has the same value in every row (as some organizations have for EMPL_RCD in PS_JOB, SETID in PS_DEPT_TBL, or COMPANY in PS_PAY_CALENDAR), the program should supply that value and save the human some work.

If there are only a few distinct values (as some organizations have for EFFSEQ in PS_JOB, EFFDT in PS_DEPT_TBL, or PAYGROUP in PS_PAY_CALENDAR), the program should supply a numbered list of values and let the human choose by number.

If there are too many distinct values (EMPLID in PS_JOB) for a multiple choice selection, the program should validate the human’s data entry.

After we know the values for the keys of the source row, we need to specify the key values of the row we will create. They could be anything, but the program will make it easy to have a new value that matches the old value. We start by adding an array to hold the number of distinct values that we find for a column. The size of the array is up to you, but I think 10 is big enough.

  create-array name=key size={num_choices}
    field=choice:char=''

Input The “From” Keys

Here’s how we collect the user’s choice of the source (or “from”) row to copy. This approach minimizes the typing required and eliminates the chance of specifying a nonexistent row.

begin-procedure input_from_values
  let #column_id = 1
  while #column_id < {num_columns}
    if copy.index(0, #column_id)
      get $column from copy(0) column(#column_id)
      do select_key_choices

      evaluate #num_distinct

        when = 1
! With only one possible value, show it, don’t ask for it. This
! branch needs a “break” to because the value “1” qualifies for
! the next branch, too.
          show 'Enter "from" value for ' $column ': ' &VALUE
          put &VALUE into copy(0) from_key(#column_id)
          break

        when < {num_choices}
! With a few possible values, offer a multiple choice.
          let #i = 0
          while #i < #num_distinct
            get $key_choice from key(#i)
            show #i edit 99 '. ' $key_choice
            add 1 to #i
          end-while
          while {true}
            show 'Choose "from" value for ' $column ': ' noline
            input #from_num type=integer noprompt
            if range(#from_num, 0, #num_distinct - 1)
              break
            end-if
          end-while
          let copy.from_key(0, #column_id) = key.choice(#from_num)

        when-other
! With a lot of possible values, ask for it and validate it.
          while {true}
            show 'Enter "from" value for ' $column ': ' noline
            input $from_value noprompt
            do validate_from_value
            if #value_found
              put $from_value into copy(0) from_key(#column_id)
              break
            else
              show '"' $from_value '" was not found in ' $column '. Try again (Y/N)? ' noline
              input $continue noprompt
              if $continue = 'N'
                stop
              end-if
            end-if
          end-while

      end-evaluate
    end-if
    add 1 to #column_id
  end-while
end-procedure input_from_values

begin-procedure select_key_choices
! Convert the database value into a character field.
  let $data_type = copy.data_type(0, #column_id)
  evaluate $data_type
    when = 'DATE'
! The date format should be the default for your database; we’ll use
! this character string later in SQL statements.
      let $select_column = 'to_char(' || $column || ', ''DD-MON-YYYY'')'
    when = 'FLOAT'
    when = 'NUMBER'
      let $select_column = 'to_char(' || $column || ')'
    when-other
      let $select_column = $column
  end-evaluate

! Use all the key values we already have to narrow the choice of the next value.
  let $where = '1 = 1'
  let #key_id = 1
  while #key_id < #column_id
    if copy.index(0, #key_id)
      get $key $type from copy(0) column(#key_id) data_type(#key_id)
      let $where = $where || ' and ' || copy.column(0, #key_id) || ' = '
      if instr(' CHAR DATE VARCHAR2 ', copy.data_type(0, #key_id), 1) > 0
        let $where = $where || '''' || copy.from_key(0, #key_id) || ''''
      else
        let $where = $where || copy.from_key(0, #key_id)
      end-if
    end-if
    add 1 to #key_id
  end-while

! Originally, we used “begin-select distinct”, but that sorted on the column
! after it was converted to character. Numbers were sorted 1, 10, 2, 21, 3,
! etc. Dates were sorted 11-MAR-2009, 21-APR-2008, 21-FEB-2007, etc. To
! sort by the column value but read the column converted to character value,
! we use the group by operation.
  let #num_distinct = 0
begin-select
[$select_column] &VALUE=char
count(*) &COUNT
  put &VALUE into key(#num_distinct)
  add 1 to #num_distinct
  if #num_distinct >= {num_choices}
    exit-select
  end-if
 from [$table_name]
where [$where]
group by [$column]
end-select
end-procedure select_key_choices

begin-procedure validate_from_value
  move {false} to #value_found
begin-select loops=1
[$select_column] &VALUE2=char
  move {true} to #value_found
 from [$table_name]
where [$where]
  and [$select_column] = $from_value
end-select
end-procedure validate_from_value

Input the “To” Keys

Here’s how we collect the user’s definition of the destination (or “to”) row to create. I wanted to use one routine for the “from” values and the “to” values, but easing data entry takes different approaches in each case.

begin-procedure input_to_values
! We assume that the new row will share some key values of the old row. We
! name each column and repeat the value the user just chose for that column.
! If the user presses the “enter” key without anything else, we use the old
! row value for the new row value.
  let #column_id = 1
  while #column_id < {num_columns}
    if copy.index(0, #column_id)
      get $column $from_key from copy(0) column(#column_id) from_key(#column_id)
      show $column ' "from" value is ' $from_key '. Enter "to" value: ' noline
      input $to_key noprompt
      let $to_key = nvl($to_key, $from_key)
      put $to_key into copy(0) to_key(#column_id)
    end-if
    add 1 to #column_id
  end-while

! Now we perform code similar to that in “select_key_choices” procedure to
! determine whether a row with these key values already exists. We already
! know that our key columns constitute a unique index. The routine that
! calls the “input_to_values” procedure should loop until the user enters
! keys for a non-existent row or gives up trying.
  let $data_type = copy.data_type(0, 1)
  evaluate $data_type
    when = 'DATE'
      let $select_column = 'to_char(' || $column || ', ''DD-MON-YYYY'')'
    when = 'FLOAT'
    when = 'NUMBER'
      let $select_column = 'to_char(' || $column || ')'
    when-other
      let $select_column = $column
  end-evaluate

  let $where = '1 = 1'
  let #key_id = 1
  while #key_id < {num_columns}
    if copy.index(0, #key_id)
      get $key $type from copy(0) column(#key_id) data_type(#key_id)
      let $where = $where || ' and ' || copy.column(0, #key_id) || ' = '
      if instr(' CHAR DATE VARCHAR2 ', copy.data_type(0, #key_id), 1) > 0
        let $where = $where || '''' || copy.to_key(0, #key_id) || ''''
      else
        let $where = $where || copy.to_key(0, #key_id)
      end-if
    end-if
    add 1 to #key_id
  end-while

  move {false} to #row_found
begin-select
[$select_column] &VALUE3=char
  show 'That row already exists.'
  move {true} to #row_found
 from [$table_name]
where [$where]
end-select
end-procedure input_to_values

Copy The Row

Here’s how we generate an SQL insert statement and perform the copy. This is the third time we’ve generated slightly different SQL. You may wish to move this functionality into a subroutine, but it may not make the program clearer or more maintainable. The SQL statement is in the form “insert into table (select … from table where …).” There are a few points to note about this SQL statement.

  • List column names for columns that we copy unchanged. List values for key columns that have changed.
  • Oracle does not allow us to “copy” all columns this way. It rejected LONG columns, and I suspect it would also reject BLOB, CLOB, LONG RAW, and RAW columns. I made it work for a LONG column by replacing the column name with a literal value, a space.
  • The first criterion is “where 1 = 1, ” which is always true. This simplifies the construction of the rest of the SQL by allowing us to add “and column = value” each time. We took advantage of this form earlier to simplify the cases of (1) no criteria, (2) one criterion, and (3) multiple criteria.

begin-procedure copy_row
  let #column_id = 1
  let $sql = 'insert into ' || $table_name || ' (select '
  while copy.column(0, #column_id) <> ''
    if #column_id > 1
      concat ',' with $sql
    end-if
    if copy.to_key(0, #column_id) = copy.from_key(0, #column_id)
      if instr(' BLOB CLOB LONG RAW ', copy.data_type(0, #column_id), 1) > 0
        let $sql = $sql || ''' '''
      else
        let $sql = $sql || copy.column(0, #column_id)
      end-if
    else
      if instr(' CHAR DATE VARCHAR2 ', copy.data_type(0, #column_id), 1) > 0
        let $sql = $sql || '''' || copy.to_key(0, #column_id) || ''''
      else
        let $sql = $sql || copy.to_key(0, #column_id)
      end-if
    end-if
    add 1 to #column_id
  end-while

  let $sql = $sql || ' from ' || $table_name || ' where 1 = 1'
  let #key_id = 1
  while #key_id < {num_columns}
    if copy.index(0, #key_id)
      get $key $type from copy(0) column(#key_id) data_type(#key_id)
      let $sql = $sql || ' and ' || copy.column(0, #key_id) || ' = '
      if instr(' CHAR DATE VARCHAR2 ', copy.data_type(0, #key_id), 1) > 0
        let $sql = $sql || '''' || copy.from_key(0, #key_id) || ''''
      else
        let $sql = $sql || copy.from_key(0, #key_id)
      end-if
    end-if
    add 1 to #key_id
  end-while
  concat ')' with $sql

  begin-sql on-error=sql_error
    [$sql]
  end-sql
end-procedure copy_row

Northern California Regional User Group

The user group for Peoplesoft Enterprise, Enterprise One (formerly J.D. Edwards), and World (formerly J.D. Edwards) is reorganizing. Last May, I got an email from the national Quest User Group inviting me to help reactivate the group. Since most Peoplesoft Enterprise customers work with SQR, we should participate. If you, dear reader, live in Northern California, please contact me. You can leave a comment or send me an email (steven@peoplesoftsqr.com). I won’t publish your comment unless you authorize it, but I will keep you informed of the new user group’s developments.

Comments are closed.