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.
How SQL and Meta-SQL Copy Now
There are already ways to do this in SQL, but they’re not simple. If the table does not have unique keys, we can write “insert into table (select * from table where …)”, with a where clause that tries to specify a single row. There are two reasons to be dissatisfied with that solution.
- We don’t know how many rows that command will create. If the select returns no rows, the insert will create no rows. If the where clause is unspecific enough, the insert might copy every row in the table. Even if the where clause specifies every column, the lack of unique keys means that we could copy multiple rows.
- The new rows are identical to the selected rows in every column. It’s difficult to follow such a “perfect” copy with another SQL operation that applies to just the new rows, which raises the question, why would we want another, identical row? I wished for a method to create almost identical rows, not identical rows.
If the table does have unique keys, the “insert into table (select * from table where …)” command will trigger a unique violation, assuming the selection returns any rows. Instead, we have to write out the entire list of columns in order.
insert into mytable
(select 'new value', field2, field3, … field75 from mytable where …)
The PSRECFIELD table reveals that Peoplesoft 8.9 HCM has 199 record definitions with over one hundred fields. True, half are derived tables, funclib records, state tables, work tables, and views, which don’t have rows to copy. Still, there are a lot of tables that would require lengthy select commands to copy a row. That may have been part of the inspiration for the %InsertSelect meta-SQL command in Application Engine and PeopleCode.
%InsertSelect(mytable, mytable, field1 = 'new value')
from mytable
where …
This PeopleTools function works by exception. It assumes that we want to copy the value of a column in the second table to the column of the same name in the first table, unless we set the value explicitly. %InsertSelect has a lot of power and flexibility, but let’s save that discussion for a blog about PeopleTools.
How I Want To Copy
If we need to copy many rows as part of an often used production application, it doesn’t seem onerous to write (or cut & paste) the columns in the SQL statement. If we have to copy one row, one time, perhaps in a setup or prompt table, it is annoying to do so much work.
My project this week requires two new TRCs (time reporting codes – like earning codes on timecards). These new TRCs should be almost identical to two existing TRCs, but with different names and with one other field set to “No” instead of “Yes.”
I want an interactive SQR program that will conduct the following dialog.
- Input the table name.
- Confirm that it is a valid table.
- Input the key values of the source row.
- Confirm that the source row exists.
- Input the key values of the destination row.
- If the destination row exists, offer to delete it, and make sure the user really wants to delete it.
- Insert the destination row with the key values from the user and the other values from the source row.
To support this dialog, the SQR program needs information from the database to
- identify tables.
- identify the columns of a table.
- identify the columns of a unique key of a table.
- check for the existence of a row with a given set of key values.
Unfortunately, the next part of this discussion will be specific to one database management system – Oracle. People who work with other databases can follow this general approach in their environments. If you want more information about the tables and views I discuss, search the web for “Oracle ALL_TABLES” (or whatever) and select the entry from the Oracle documentation website.
Identify Table
Oracle has a view called ALL_TABLES, which gives us all the database tables that we can access. It would be more comprehensive to use DBA_TABLES, which also gives us tables we cannot access, but what’s the point? If we cannot access them, we cannot copy their rows.
There is a similar view called ALL_VIEWS, which gives us all the database views that we can access. We prefer it to DBA_VIEWS for the reason given above.
The SQL statements behind these views are very complex, joining many tables, some with outer joins or “where exists” subqueries, performing bitlevel logic operations and decode functions, doing far more work than we need. But it’s convenient and fast, so we start with it.
#define false 0
#define true 1
begin-procedure get_table_name
input $table_name 'Enter table name'
if $table_name = ''
stop
end-if
uppercase $table_name
move {true} to #table_not_found
begin-select
NUM_ROWS
move {false} to #table_not_found
from ALL_TABLES
where TABLE_NAME = $table_name
end-select
move {false} to #table_is_a_view
begin-select
OWNER
move {true} to #table_is_a_view
from ALL_VIEWS
where VIEW_NAME = $table_name
end-select
if #table_is_a_view
show $table_name ' is a view'
stop
else
if #table_not_found
show $table_name ' is not accessible or does not exist'
stop
else
if &NUM_ROWS = 0
show $table_name ' has no rows to copy'
stop
end-if
end-if
end-if
end-procedure get_table_name
Identify Columns
Oracle has a view called ALL_TAB_COLUMNS, and all our previous comments still apply. We prefer it to DBA_TAB_COLUMNS. It has complex SQL. It’s convenient and fast.
We also need to identify the key fields of the unique index. Oracle’s view, ALL_IND_COLUMNS, seems to provide the answers, but upon closer examination, we find that it doesn’t.
A database table can have any number of indices and they can be unique or nonunique. If the table has a unique index, we want to use it because it ensures that we can specify a unique row. If the table has no indices or just nonunique indices, we still may be able to specify a unique row. It’s also possible that the row we want to copy has a duplicate row that is identical in every column. Those rows could only be isolated by SQL’s ROWNUM pseudo-column.
When the PeopleTools Application Designer builds indices for a table, it gives the unique index, if there is one, the same name as the table. It gives nonunique (alternate key) indices names that are similar to the table name. The unique index of PS_JOB is called PS_JOB. The nonunique indices are called PSAJOB, PSBJOB, PSCJOB, etc.
It’s tempting to assume that we know the name of the unique index, but there are tables that don’t have unique indices or tables that don’t follow that convention. We should determine the actual status of the indices from the database. After studying the SQL of ALL_IND_COLUMNS, I found the underlying tables and columns that solve the problem robustly.
begin-setup
create-array name=copy size=1
field=table:char=''
field=column: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
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
COLUMN_ID
let copy.column(0, &COLUMN_ID) = &COLUMN_NAME
from ALL_TAB_COLUMNS
where TABLE_NAME = $table_name
end-select
begin-select
SC.COL#
let copy.index(0, &SC.COL#) = {true}
from SYS.OBJ$ SO, SYS.ICOL$ SI, SYS.COL$ SC
where SO.NAME = $table_name
and SI.OBJ# = SO.OBJ#
and SC.OBJ# = SI.BO#
and SC.COL# = SI.COL#
end-select
end-procedure get_column_names
Looking Ahead
Next, our program needs to ask us to specify the source and destination rows of the copy operation. How easy and safe can we make that dialog? Finally, we need to generate and execute the SQL select and insert commands, using dynamic variables.
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