column count reconciliation in JD Edwards, specs vs reality

Below is some sample SQL that will create a reconciliation table (fia_temp_table_count) and then populate this with the central objects count of columns for that table.  It’ll also put in the relational database count and then show you a nice little summary of the differences.

This is quite nice for a quick (minutes) comparison of specs vs. relational database columns.

create table fia_temp_table_count 
(table_name varchar(50) not null, 
spec_column_count integer not null, 
relational_column_count integer not null) 
;
drop table fia_temp_table_count ;
-- insert the relational column counts 
-- 
insert into fia_temp_table_count 
select table_name, 0, count(1) 
from all_tab_columns 
where owner = 'FIADTA' 
group by table_name 
order by table_name ;
commit;
-- get the spec table counts from the F98711 
-- 
update fia_temp_table_count 
set spec_column_count = ( 
select count(1) 
from PY910.f98711 
where  ltrim(rtrim(tdobnm)) = table_name);
commit;
select table_name || ' ' || spec_column_count || ' ' || relational_column_count 
from fia_temp_table_count 
where spec_column_count != relational_column_count;

Note that an extension for this (and only for use by experts) is to use some of this to generate the DDL for creating the additional columns.  For example, just say I find that a table in FIADTA is missing some columns.  Well, I could run the below to give me the DML to make a correction to the table.  This is JUST for missing columns and you really need to know what you are doing to attempt it.  In this example, I’m specifying the F3703, but I could just as well do it for all of the results of the last SQL above.

Remember, you don’t need to run the results, but the output will assist you!

I did two statements, you probably only need 1 using data_type.  Mine was a little more complicated because I has some NCHAR and CHAR inconsistencies.

select 'ALTER TABLE FIADTA.F3703 ADD  ' || t2.column_name || ' ' || data_type || ';' 
from all_tab_columns t2
where t2.owner = 'FBADTA'
and t2.table_name = 'F3703' 
and not exists
(
select 1  
from all_tab_columns t1 
where t1.owner = 'FIADTA' 
and t1.table_name ='F3703' 
and t2.column_name = t1.column_name)
and t2.data_type = 'NUMBER'
union
select 'ALTER TABLE FIADTA.F3703 ADD  ' || t2.column_name || ' ' ||  data_type || '(' || data_length || ');' 
from all_tab_columns t2
where t2.owner = 'FBADTA'
and t2.table_name = 'F3703' 
and not exists
(
select 1  
from all_tab_columns t1 
where t1.owner = 'FIADTA' 
and t1.table_name ='F3703' 
and t2.column_name = t1.column_name)
and t2.data_type = 'NCHAR'
;