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

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.

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.

Hi Steven,

Yes i'd really like you to sent me the procedure you find !
It would be very helpfull.

You can send it to : [email protected].

Thanks in advance.

Cheers,

Antoine

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.

hello, i have the same problem.
can you send me the procedure to? thanks.

[email protected]

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 ?

Thanks Jon.

Sef: How about copying this into Tips & Traps?

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!!

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,