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.
Recent Comments