Is it possible to copy User Overrides (F98950) by sql ?

antoine_mpo

Reputable Poster
Hi List,

I need to copy many User Overrides (especially grid format) from a user to some others.
If if do so using JDE interface, it's not easy because you have to copy every line one by one.

I was wondering if it was possible to copy it by sql, knowing that in the F98950 table, there is a BLOB field ... (that can't be copied using SQL PLUS or other SQL Editor).

Does anyone faced this issue and found a way to solve it ?

Thanks for your help.

Antoine
OW system Administrator.
 

Ian_Simmons

Active Member
I've had the same problem. If you have a lot of overrides, I can't see a way around it. If you have a lot of users, you can always publish then to groups (even *PUBLIC).
 
Antoine,

I have successfully created a SQL procedure to do this. I then link this sql procedure to an online application within OneWorld. If you want, I can send you my sql procedure and the other pieces of code.
 

antoine_mpo

Reputable Poster
Hi guys,

One of my colleague find another way to copy User Overrides very easily. You just have to use Microsoft Access.

Here is an example : i want to copy all the user overrides of the user "USER1" to a group of users "GROUP1", in PY7333 environment.

To do so, i create an Access Database, with a link table (using ODBC) with PY7333.F98950.
Then i create an Access sql request (in "SQL" mode) :

INSERT INTO PY7333_F98950 ( UOUOTY, UOUSER, UOOBNM, UOFMNM, UOCTRLID, UOVERS, UOLNGP, UOSEQ, UOBINDTA, UOOVF1, UOOVF2 )
SELECT PY7333_F98950.UOUOTY, "GROUP1", PY7333_F98950.UOOBNM, PY7333_F98950.UOFMNM, PY7333_F98950.UOCTRLID, PY7333_F98950.UOVERS, PY7333_F98950.UOLNGP, PY7333_F98950.UOSEQ, PY7333_F98950.UOBINDTA, PY7333_F98950.UOOVF1, PY7333_F98950.UOOVF2
FROM PY7333_F98950
WHERE (UOUSER="USER1");


It works fine.

Cheers,

Antoine
OW system administrator.
 

altquark

Legendary Poster
The way I do this is to create table (temptable) as select * from (blobtable) where (selection criteria)

then update whatever you need on the temporary table - and then use

insert (blobtable) using select * from (temptable)

Heres an example. Say I want to copy "DDUCK" User Overrides to "MMOUSE" overrides in Development

NOTE : The "Create Table AS" command is generic across every real SQL database (!) - but only just got implemented in V5R2....

1. create table codv7333/f98950temp as (select * from codv7333/f98950 where uouser='DDUCK')
2. update codv7333/f98950temp set uouser='MMOUSE'
3. insert into codv7333/f98950 select * from codv7333/f98950temp
4. drop table codv7333/f98950temp

Of course, this works under Oracle and SQL Server perfectly well....

I got this into 4 lines of SQL - can anyone get it into less ?
 

Wes_D

Well Known Member
you can do it in one statement:

Insert into codv7333/f98950
select UOUOTY, "MMOUSE", UOOBNM, UOFMNM, UOCTRLID, UOVERS, UOLNGP, UOSEQ, UOBINDTA, UOOVF1, UOOVF2
from codv7333/f98950

where uouser='DDUCK'

One more thing: if the XML file is too long, the additional data is stored in F98BLOB. If you don't copy that record, jde will blow away the record from F98950!!
 

Adrian_Chimirel

Legendary Poster
Hi Wes,
I've created a standard UBE to copy these records, but the blob BINDTA doesn't get copied; should I use a Table Conversion instead?
Thank you,
 
Top