SQL : Conseils et astuces

 

Accueil ] [ SQL : Conseils et astuces ] JDE Utilitaires à télécharger ] JDE : Stand alone 8.12 sans timeout ] JDE : Astuces One World ] JDE : Rapid Start (Accelerate) ] Définition d'un ERP (PGI) ] JDE World Writer ] JDE : Sous-Traitance ] JDE : Sécurités ] JDE : Calcul du Pump ] JDE : Post-Implementation ] JDE : Periodes Comptables ] JDE : Menus ] JDE : Informations Générales ] JDE : Fastr ] JDE : Conversion Euro ] JDE : Dream-Writer ] JDE : ICA (AAI) ]

 

 

 

N'hésitez pas à poser vos questions sur le forum !

 

1.     Préambule

 Je recommande de toujours faire un select (pour vérifier les sélections) avant de faire un update. D’autre part, je conseille de sauvegarder au préalable le fichier à mettre à jour ou les enregistrements supprimés (par exemple par un create table as select  ou un export excel de Toad, ...).

 

2.     Create table

 Conseil : se servir des scripts stockés avec les tables à titre d’exemple.

 CREATE TABLE PMAF1201

(

  FANUMB  NUMBER,

  FALANO  NUMBER,

  FAAN8   NUMBER

 )

 create table pmaf0911 as select * from f0911 where ... : OK en Oracle, se plante en DB2. Pour la créer vide, mettre un select qui ne donne aucun record.

 create table pmaf0911 like f0911 : OK en DB2. Création à vide.

 CREATE TABLE simdta.pmaf0901 (

gmmcu graphic (12),

gmobj graphic (6),

gmsub graphic (8)

) ;

 

Comment sous SQL Server ??

  

3.     Suppression de table (DANGER)

 drop table

  

4.     Select

 4.1     Enreg de F554102FR inexistant dans F554102 (avec 2 select)

 

select * from qgpl/f554102fr where iclitm not in               

(select imlitm from eudvdta/f4101,eudvdta/f554102 where imitm= 

icitm)

 

SELECT * FROM proddta/f0912,proddta/f0901 WHERE F0912.RJAID =gmaid 

and F0901.GMPEC =' '                                               

 

SELECT * FROM proddta/f0912,proddta/f0901 WHERE F0912.RJAID =gmaid 

and F0901.GMPEC ='N'

  

4.2     Enreg de F0012 inexistant dans F0901 (avec clé composée et 2 select ou avec minus ou left outer join)

 

Minus est Utilisable en Oracle, pas en DB2, « beaucoup plus rapide »  mais tous les champs ne sont pas affichés :

 

select kgmcu||kgobj||kgsub from crpdta.f0012 minus select gmmcu||gmobj||gmsub from crpdta.f0901

  

Si plusieurs champs, utiliser une concaténation des champs ou les parenthèses (plus simple).

 select * from crpdta.f0012 where kgmcu||kgobj||kgsub not in (select gmmcu||gmobj||gmsub from crpdta.f0901)and kgco in ('00101', '00105', '00106') and kgmcu <> ' '

 NB : concat(glicu,gldoc) est équivalent à glicu||gldoc

NB : concat ou || ne fonctionne pas si un des champs est numérique. Utiliser alors char.

Ex : vnedtn || char(vndgj)

 Sous DB2/400, on ne peut pas faire de concat à plus de deux champs par exemple concat(glkco,gldct,glsbl). Faire concat (glkco, concat(gldct, glsbl)) ou mieux glkco concat gldct concat glsbl concat … ou  glkco || gldct || glsbl ||

 select * from crpdta.f0012 where (kgmcu,kgobj,kgsub) not in (select gmmcu,gmobj,gmsub from crpdta.f0901)and kgco in ('00101', '00105', '00106') and kgmcu <> ' '

Nb : (a, b, c) in (select a, b, c from …) fonctionne en Oracle mais pas en DB2, utiliser alors un concat.

 On peut se passer du minus en utilisant sous DB2 un left outer join (tous les enreg de la 1ère table qu'ils aient ou pas une correspondance dans la 2ème table) couplé à une comparaison à null pour un champ de la 2ème table (donc non trouvé dans la 2ème table).

 select * from crpdta.f0012 left outer join f0901 on kgmcu=gmmcu and kgobj=gmobj and kgsub=gmsub where kgmcu <> ' ' and gmmcu is null

 select f1.AICO,f1.aian8,f2.cmslsm from crpdta.f03012 f1 left join crpdta.f42140 f2 on f1.aian8=f2.cman8 and f1.aico=f2.cmco and f1.aian8=11701033 and f1.aico='00012';

Attention ici on a mis un and au lieu d'un where ce qui donne un résultat complètement indésirable !!

  

4.3     Description différente colonne

 Select sum(glaa) sum_gl, ...  ou bien select wadoco as "n° de bon", ...

  

4.4     Plusieurs tables et group by

 Mettre obligatoirement tous les champs à afficher dans le group by.

  

4.5     Contrôle de totaux entre deux tables avec having et sous-requête

 select

f4111.ilicu, f4111.ildgl, F4111.ilkco ,f4111.ildct, f4111.ildoc, 

f4111.ilitm, f4101.IMDSC1,f4111.illocn, f4111.iltrqt, f4111.ilpaid, 

f4111.ilmcu, f4111.ilglpt,  f4111.iljeln

from   trdta.f4111, trdta.f4101

where  f4111.ilitm = f4101.imitm

and    f4111.ILDOC = '884'

and    f4111.ildct = 'I3'

and           (ilkco, ildoc, ildct) in           

                    (

                                select ilkco, ildoc, ildct

                                from trdta.f4111

                               having sum(ilpaid) != -

                                                (

                                                select sum(f0911.glaa)

                                                from trdta.f0911

                                                where LTRIM(f0911.GLSBL,'0') = TO_CHAR(f4111.ildoc)

                                                and   f0911.gldct = 'I3'                                                                      

                                                and   f0911.glicu = f4111.ilicu

                                                and   f0911.glaa > 0

                                                group by f0911.glicu, f0911.gldoc, f0911.gldct

                                                )

                   group by ilkco, ildoc, ildct, ilicu

                   )

and (ilpaid) !=

                   (

                               select -glaa

                               from trdta.f0911

                               where LTRIM(f0911.GLSBL,'0') = TO_CHAR(f4111.ildoc)

                               and   f0911.gldct = 'I3'                                                                       

                               and   f0911.glicu = f4111.ilicu

                               and   f0911.glaa > 0

                               )

 

select sdkcoo,sddct,sddoc,sum(sdaexp)

 from

                proddta.f4211 where sdkcoo='00012'  and sddct='RI' and sdnxtr='999' and sddgl between 107190 and 108365

                                group by sdkcoo,sddct,sddoc

                               having sum(sdaexp) <>

 

                                                (

 

                                                select sum(rpatxa+rpatxn)

 

                                                from proddta.f03b11

 

                                                where sddoc=rpdoc and sddct=rpdct and sdkcoo=rpkco

 

                                                group by rpkco,rpdct,rpdoc

 

                                                )

 

                  ;

 4.6     Exemples de recherche de doublons (avec having)

 

select substr(abalky, 2, 19), count(*) abalph from f0101 where abat1= 'V' group by substr(abalky, 2, 19) having count(*)>1

------------------------------------------------------------------------

 

1                   ABALPH    

------------------- -----------

                             14

09P                           2

1100                          2

 

  11 record(s) selected.

 

 

select aban8, abalph, abalky from f0101 where substr(abalky, 2, 19) in ('09P',  '1100',   '23108'     , '23200', '25200',      '27125'                         ,            '28566'  , '29016'     , '66P'  ,      '693') order by substr(abalky, 2, 19)

------------------------------------------------------------------------

 

ABAN8      ABALPH                                   ABALKY             

---------- ---------------------------------------- --------------------

    27597, PARALLELES - COMMUNICATION VISUELLE      309P               

    27602, NAGORA                                   709P                

 

  22 record(s) selected.

 

 

4.7     Exemples de recherche de nb de valeurs différentes avec min et max (identification des doublons)

 

Select egvono,min(egvtcd),max(egvtcd) from fgledg where egvtcd<>’0’ group by egvono having min(egvtcd)<>max(egvtcd)

Ceci donne les factures frn qui sont multi taux de TVA.

  

4.8     Valeur NULL

 Si aucunes valeurs n'est spécifiées lors de l'insertion dans une colonne NULL, Oracle insérera automatiquement une valeur nulle. Si la colonne possède la contrainte NOT NULL, Oracle affichera alors une erreur

 

select count(*) from f1201 where falano is NULL

 

Donc dans update avec sous-requête tester avec un "and exists (select x from …)" ou un "in (select x from …)".

Attention : if exists () est vrai si la requête à l'intérieur des parenthèses renvoie au moins 1 enreg sans lien avec le début du select!

  

4.9     Instruction CAST : conversion de type

 select wanumb,gdtxky,wadoco,wadcto,gdtxvc from f4801,f00165 where cast(wanumb as varchar2(254))=gdtxky

and wadcto='SE' and wanumb<>0

 

CAST(sdrorn AS NUMERIC(8,0)) convertit champ alpha '00000001' en numérique 1 sur DB2/400.

  

4.10     Arrondis

 round(rirfac,0)

pour 0 décimale.

  

4.11     Accès à des databases différentes

 select * from proddta.f0012@PEOPRD   (Oracle, pas DB2)

database = PEOPRD, table = F0012, schéma ? : proddta

 select * from PS_CRP.crpdta.f0012   (pas Oracle, DB2)

  

4.12     Left outer join (n champs clés)

 select pdkcoo,pddoco,pddcto,pdlnid, pdcndj,pdnxtr,pdlttr,pdaopn, prarec,praopn, prmatc,prkcoo,prdoco,prdcto,prlnid from f4311 left outer join f43121 on

(prdoco= pddoco and prdcto=pddcto and prkcoo=pdkcoo and prlnid=pdlnid)

  where (pdaopn<>0 or prarec=0 or (prarec<>0 and praopn<>0)) and (prmatc='1' or prmatc is null) and pdlttr<>'980'  and pdnxtr>='280' and pddcto not in ('OB', 'OO', 'OR', 'OU') for read only with ur

 Tous les enreg de la 1ère table sont inclus même si inexistants dans la 2ème table. Exemple ici de Join sur plusieurs champs clés. Si non trouvé dans la 2ème table, champs de la 2ème table renseignés par null.

 Remarque : on ne peut pas mixer des F0911, F0901 avec un left outer join F0101. Dans ce cas, mettre que des left outer join. Voir exemple ci-dessous.

 select glfy,glpn,glco,glmcu,globj,glsub,gmr003,gmr004,gmr005,MCRP12,mcrp13,gmdl01,MCDC,glkco,gldct,gldoc,gljeln,glexa,glexr,glan8,abalph,glaa/100

from proddta.f0911 left outer join proddta.f0101 on

(glan8=aban8) left outer join proddta.f0901 on

(glaid=gmaid) left outer join proddta.f0006 on

(glmcu=mcmcu)

where  gllt in ('AA', 'AN') and glpost='P'

and glfy=6 and glpn in (1, 2) and globj between '6' and '799999'

order by glfy,glpn,glco,glmcu,globj,glsub,glkco,gldct,gldoc,gljeln

 

4.13     Full outer join

 Permet d'afficher 2 tables que l'on compare en un seul passage y compris enreg de 2ème table inexistant dans 1ère table.

Fonctionne sur DB2/UDB mais pas sur DB2/400.

 select f1.drsy, f1.drrt, f1.drky, f1.drdl01,f1.drdl02,f1.drsphd,f1.drhrdc,f2.drsy,f2.drrt,f2.drky,f2.drdl01,

f2.drdl02,f2.drsphd,f2.drhrdc

from prodctl.f0005@PEOPRD f1

full outer join crpctl.f0005@PEODEV f2 on

(f1.drsy=f2.drsy and f1.drrt=f2.drrt and f1.drky=f2.drky) where (f1.drky is null or f2.drsy is null

or f1.drdl01<>f2.drdl01 or f1.DRDL02<>f2.drdl02 or

f1.drsphd<>f2.drsphd or f1.drhrdc<>f2.drhrdc) order by f1.drsy, f1.drrt, f1.drky

  

4.14     Rappel sur join DB2/400

 join : tous les enreg commun aux tables 1 et 2

left join = left outer join : tous les enreg de la table 1 communs ou pas avec la table 2

left inner join ou full join ou full outer join : KO sur DB2/400

 On peut tester avec la requête select count(*) from proddta.f0006  full outer join proddta.f41001 on mcmcu=cimcu;

  

4.15     Sous-requête utilisée comme table dans from ou join

 On peut utiliser une sous-requête comme table virtuelle. Permet d'éviter une table de travail intermédiaire, par exemple pour éviter des doublons ou faire des sous-totaux.

 Select * from (select * from )

 select glpo,glkco,gldct,gldoc,gljeln,glaa/100,sdmcu,prmcu,glfy,glpn from proddta.f0911 left join (select sdkco,sddct,sddoc,sdmcu from proddta.f4211 where sdco='00012' group by sdkco,sddct,sddoc,sdmcu) f2  on f2.sdkco=glkco and f2.sddct=gldct and f2.sddoc=gldoc left join (select prkco,prdct,prdoc,prmcu from proddta.f43121 where prco='00012' group by prkco,prdct,prdoc,prmcu) f3 on f3.prkco=glkco and f3.prdct=gldct and f3.prdoc=gldoc  WHERE GLMCU='    12005320' AND GLOBJ='99000' AND GLSUB='38100' and gllt='AA' and glpost='P' ;

  

4.16     Decode

 SELECT supplier_name, decode(supplier_id, 10000, 'IBM', 10001, 'Microsoft', 10002, 'Hewlett Packard', 'Gateway') result FROM suppliers;

permet de converti des données

 On peut faire un group by sur un de code, mieux qu'un case xx when xx else xx end

 select decode(RPDCT,'RB','XA',RPDCT),rpdoc,min(rpkco),max(rpkco) from proddta.f03b11 where rpkco in ('00012', '00014', '00015', '00016')

and rpaap<>0 group by decode(RPDCT,'RB','XA',RPDCT),rpdoc having min(rpkco)<>max(rpkco)

  

4.17     Conversion en date julien

 Fonctionne sur AS/400 mais pas sur Oracle.

 http://peoplesoft.ittoolbox.com/groups/technical-functional/jdedwards-l/how-to-create-a-julian-date-table-for-jde-world-1754445

 

 

How to create a julian date table for JDE world

Reply from Frank Seddon

on 12/7/2007 4:12 AM

I like UWE's answer - here are some others that I've used in the past after seeing people use fancy lookup tables (e.g. F00365) and tools, I thought I would share with you how you can convert a Julian date in JDE to a date you can understand using either SQL or Excel. Also I provide a method of converting a Gregorian date back to Julian.

If anyone has more elegant solutions I'm always willing to steal other people's good ideas!!!

For Query on the AS400:

- Go to "Define result fields",

- Create a new field,

- Add the expression date(substr(digits(XXXXXX + 1900000),2,7)) , where XXXXXX is the field within the table. Note this expression will be over 2 lines.The output will be dd/mm/yy.

SQL for Julian Date to Gregorian (AS400 SQL only)

SELECT DATE(CHAR(103205+1900000)) as JulianToGregorian

FROM QSYS2/QSQPTABL

Input Julian date was 103205, this can also be a field from a table

Output will be 24/07/03 on AS400, or 2003-07-24 on Client Access

 

SQL for Gregorian Date to Julian (AS400 SQL only)

SELECT (INT(CURDATE() - DATE('01/01/1900')) / 10000)*1000 + DAYOFYEAR(CURDATE()) as GregorianToJulian

FROM QSYS2/QSQPTABL

Input Gregorian date was today's date (24/07/2003), this can be entered manually as DATE('07/24/2003') to replace CURDATE()

Output will be 103205

Excel for Julian Date to Gregorian

=DATE((A7 - MOD(A7,1000)) / 1000, 1, MOD(A7,1000))

Formula where A7 is a JDE Julian Date and returns a Gregorian Date (dependant on cell format) in current cell

E.g. A7 = 103205, and resultant cell will display 24/07/2003

Excel for Gregorian to Julian

=(A2 - DATEVALUE("1/1/"&YEAR(A2))+1) + (YEAR(A2) - 1900) * 1000

Cell Formula where A2 is a Gregorian Date and returns a JDE Julian in current cell

E.g. A2 = 24/07/2003, and resultant cell will display 103205

 

 

5.     Update

 Attention ‘’ correspond à vide et ‘ ‘  correspond à blanc.

un update .. set ;;= (select …) aboutit si select vide ou mono-enregistrement. Par contre, ano SQL0811N si select donne plus d’une ligne).

update fichier 1 inner join fichier 2 on … set … fonctionne en access, pas en DB2.

 5.1     But : mettre à jour un 1er  fichier à partir infos d’un 2ème fichier

 But : mettre à jour un 1er  fichier à partir des informations présentes dans un 2ème fichier en utilisant un 3ème fichier pour établir la correspondance entre ITM et LITM.

 Attention : si l’enregistrement n’est pas trouvé dans le 2ème fichier le champ est renseigné par NULL !!

 update eudvdta/f554102 set icsr28=(select icsr28   

from qgpl/f554102fr where                          

(f554102.icmcu=f554102fr.icmcu and f554102.icitm=  

(select imitm from eudvdta/f4101                   

where imlitm=f554102fr.iclitm)))                    

610 rows updated in F554102 in EUDVDTA.

  

Fichiers utilisés

 select icmcu,iclitm,icsr28 from qgpl/f554102fr

 

ICMCU         IMLITM                     ICSR11  

    FROCLS01  8168122                    FR001   

    FROCLS01  723541                     FR002   

    FROCLS01  999                        PM      

    FROCLS01  8435307                    FR003

 

select imitm,imlitm from eudvdta/f4101 where imitm in (204, 723541)

 

IMITM   IMLITM    

    204   8168122   

723,541   8435307

 

Fichier à modifier (avant update)

 select icmcu,icitm,icsr28 from eudvdta/f554102

 

ICMCU              ICITM   ICSR28

    FROCLS01     723,541

    FROCLS01         204  

 

Fichier à modifier (après update)

select icmcu,icitm,icsr28 from eudvdta/f554102

    where icsr28<>''                          

 

ICMCU              ICITM   ICSR28

    FROCLS01     723,541   FR003 

    FROCLS01         204   FR001

 

Exemple avec concaténation et préfixage des champs

 update f1202 set flapyc=(select flapyc from pmaf1202 where f1202.flnumb=pmaf1202.flnumb

and f1202.fllt=pmaf1202.fllt and f1202.flfy=pmaf1202.flfy and f1202.flaid=pmaf1202.flaid)

where f1202.flnumb || f1202.fllt || f1202.flfy || f1202.flaid

in (select pmaf1202.flnumb||pmaf1202.fllt||pmaf1202.flfy||pmaf1202.flaid from pmaf1202)

 

Mise à jour table à partir de sous-requête sur elle-même : la méthode avec table intermédiaire (w_eve) et une vue (w_evevue) semble efficace. La maj s'est terminée en 15 mn seulement !

 Sur Oracle, update (select … from table1, table2 where …) set table1.a=table2.b fonctionne, à tester.

 5.2     But : mettre à jour plusieurs champs d’un 1er  fichier à partir infos d’un 2ème fichier

 Update set (a,b)=(select c,d …)

 5.3     But : renuméroter les F/A uploadés

 update eudvdta/f1201 set fanumb=fanumb-7271224 

where fanumb between 8101224 and               

8101831                                        

606 rows updated in F1201 in EUDVDTA.        

  update eudvdta/f1202 set flnumb=flnumb-7271224 

where flnumb between 8101224 and               

8101831                                        

2426 rows updated in F1202 in EUDVDTA.

 update eudvdta/f1201 set faaaid=fanumb

where fanumb between 830000 and       

830605                                 

604 rows updated in F1201 in EUDVDTA.

 

5.4     But : mise à jour d'un champ avec concaténation, char, dec, to_char, to_number

 UPDATE EUDVDTA/F1201Z1 SET FAASID = concat('INC', char(fanumb))

WHERE fanumb between 832001 and 834216 and faasid=''            

 fanumb est un champ numérique, la fonction char la transforme en alpha.

exemple : fanumb = 840001 -> faasid= 'INC00840001'

 

select mcmcu,substr(mcmcu, 8, 2),substr(mcmcu, 12, 1), concat(substr(mcmcu, 1, 11), '5')  from F0006 where substr(mcmcu, 8, 2) in ('05', '07', '09', '10') and substr(mcmcu, 12, 1) = '0'

 

update F0006 set mcmcu =  concat(substr(mcmcu, 1, 11), '5') where substr(mcmcu, 8, 2) in ('05', '07', '09', '10') and substr(mcmcu, 12, 1) = '0'

 

glasid='00830610' -> dec(glasid)=830610 numérique

 

NB : char et dec fonctionnent en DB2 400 et UDB mais ne fonctionnent pas en Oracle (utiliser to_char, to_number, cast)

NB : si To_number  s’applique à un champ non numérique (ex : blanc), cela provoque une erreur sql.

 

5.5     But : changer les noms des groupes clients et articles tarifs

 update eudvdta/f4094 set kicpgp='FRC01'

where kicpgp='CFR01'                  

5 rows updated in F4094 in EUDVDTA.

 update eudvdta/f4071 set atcpgp='FRC01'

where atcpgp='CFR01'                   

7 rows updated in F4071 in EUDVDTA.    

update eudvdta/f4071 set atprgr='FRI01'

where atprgr='IFR01'                   

6 rows updated in F4071 in EUDVDTA.    

select * from eudvdta/f4071

 

5.6     But : charger les durées de vie réelles de SAP dans F1202

 Sélection incomplète sur types de livre.

 update eudvdta/f1202 set fladlm=(select zzadlm 

from qgpl/f1202DV where                        

(f1202dv.zzdl03=

(select fadl03 from eudvdta/f1201      

where fanumb=flnumb)))                 

2622 rows updated in F1202 in EUDVDTA.

 update eudvdta/f1202 set fladlm=0 whERE fllt in ('AA', 'AU')

                     and fladlm<>0                          

1216 rows updated in F1202 in EUDVDTA.

 SELECT count(*) FROM eudvdta/f1202,qgpl/f1202avdv WHERE        

f1202.flnumb=f1202avdv.flnumb and f1202.fladlm=f1202avdv.fladlm

and f1202.fllt=f1202avdv.fllt                                  

-> 2018 inchangés

 SQL statement, press Enter.                                     

SELECT count(*) FROM eudvdta/f1202,qgpl/f1202avdv WHERE         

f1202.flnumb=f1202avdv.flnumb and f1202.fladlm<>f1202avdv.fladlm

and f1202.fllt=f1202avdv.fllt                                   

->422 modifiés

 Remarque : en cas d'ambiguïté dans les noms de champ, indiquer F1/F2 ou noms directs des tables :

select * from f0101 f1, f0101 f2 ... where f1.aban8=f2.aban8 (DB2/400)

select f1.fmnumb,f1.fmloc from f1204 f1, f1204 f2 where f1.fmnumb=f2.fmnumb and f1.fmloc<>f2.fmloc (Oracle)

 

5.7     Remplacement des caractères d'une chaîne par une sous-chaîne (REPLACE)

 update f0912a set riexr=REPLACE(riexr, '1.00000000', '100000000')

 where rico='00106' and ridoc=7   

5.8     Remplacement des caractères minuscules par des majuscules (UPPER)

 update CRPCTL.f0005 set drdl01=UPPER(drdl01)

 where drsy=’00’ and drrt=’03’ and drky like ‘%500%’   

  

5.9     Champ Blob DB2

 Ex: c1 est de type varchar et c2 de type blob

Code :

UPDATE JAB.TESTBLOB TESTBLOB

   SET C2 = blob(c1)

 
Mais si tu n'as que des string, utilise un champ CLOB au lieu d'un BLOB.

 

6.     Insert

 6.1     Insert à partir d'une autre table

 CPYF FROMFILE(EUDVDTA/F1202) TOFILE(AZ/PM120228) CRTFILE(*YES)

 clrpfm aziegler/pm120228

 INSERT INTO AZ/PM120228 SELECT * FROM eudvdta/f1202

5606 rows inserted in PM120228 in AZ.

 

6.2     Insert avec valeurs précisées + pb constraint

 Upload F4105

 INSERT INTO EUDVDTA/F4105 VALUES(86002838, 'EO4371100',           

'EO4371100', '    FROCLS01', NULL, NULL, NULL, 'IC', 856700, NULL,

NULL, NULL, NULL, NULL, NULL, NULL, 'AZIEGLER', NULL, NULL, NULL, 

NULL)                                                             

INSERT or UPDATE not allowed by CHECK constraint.                 

 

Cause . . . . . :   The value being inserted or updated does not meet the     

  criteria of CHECK constraint QSYS_PRIKEYCHKCST_001126.  The operation is not

  allowed.                                                                    

Recovery  . . . :   Change the values being inserted or updated so that the   

  CHECK constraint is met.  Otherwise, drop the CHECK constraint              

  QSYS_PRIKEYCHKCST_001126.                                                   

 

Check Constraint                                                                 

  Constraint  . . . . . . . . . . . . . . : CST        QSYS_PRIKEYCHKCST_001126  

    Type  . . . . . . . . . . . . . . . . : TYPE       *CHKCST                   

    Check pending . . . . . . . . . . . . :            NO                        

    Constraint state  . . . . . . . . . . : STATE      ESTABLISHED               

                                                       *ENABLED                  

  Check constraint expression . . . . . . : CHKCST     COITM      IS NOT         

    NULL AND COMCU      IS NOT NULL AND COLOCN     IS NOT NULL AND COLOTN        

         IS NOT NULL AND COLEDG     IS NOT NULL                                  

 

INSERT INTO EUDVDTA/F4105 VALUES(86002838, 'EO4371100',         

'EO4371100', '    FROCLS01', ' ', ' ', NULL, 'IC', 856700, NULL,

NULL, NULL, NULL, NULL, NULL, NULL, 'AZIEGLER', NULL, NULL, NULL,

NULL)                                                           

1 rows inserted in F4105 in EUDVDTA.

 

6.3     Insert avec certaines valeurs précisées

 Insert into SYS7333.F00950 (FSSETY, FSUSER, FSOBNM, FSDTAI, FSFRDV, FSSY, FSATN3) Values(' ','EXCLUSIVE',' ', ' ', ' ', ' ' , '1')

  

6.4     Insert à partir d'une autre table avec certaines valeurs modifiées

 insert into F4095

select  MLANUM, '00106' MLCO, MLDCTO, MLDCT, MLGLPT, MLCOST, '     106CCPR' MLMCU, MLOBJ, MLSUB from f4095 where mldct='FA'

and mlglpt='PF12' and mlco='00105'

 

NB : utiliser “insert into” mais pas “CREATE OR REPLACE Insert” en interactif dans Toad.

 

Sur DB2, utiliser l'assistant SQL, cliquer sur select pour récupérer tous les champs d'une table.

 

7.     Vues

 create view PMAf03b11 (rpdoc,rpdct,rpkco,rpsfx,sumrpaapmrpag ,sumrpaap)

as

select rpdoc,rpdct,rpkco,rpsfx,sum (rpaap-rpag) ,sum(rpaap) FROM f03B11 WHERE rpkco in ('00105', '00106') GROUP BY rpdoc,rpdct,rpkco,rpsfx    

with read only

 

Cette commande crée la structure (sans donnée), on l'utilise ensuite

comme une table.

 

Drop view PMAf03B11 pour la supprimer.

  

8.     Autres commandes sql

 desc f4801 : description de la table (OK en Oracle, KO en DB2)

describe select * from f4801 for read only with ur  (KO en Oracle, OK en DB2)

 for read only with ur : ne pas tenir compte des locks et ne pas locker (sur DB2, pas sur Oracle)

  

9.     DB2/UDB

 IBM DB2, Command Center

 connect to jde_dev user testdta using TESTDTA

 connect to jde_prod user proddta using PRODDTA (attention aux majuscules)

 select * from TESTCTL.f0005 pour se connecter à Control Tables TEST

 

10.     DB2/UDB

 10.1     Divers

 Programme > IBM DB2 > Outils ligne de commande > centre de commande

 Exécution interactive, connexion à la base de données, assistant sql.

psft/ ?? ou crpdta/CRPDTA

 Export : Centre de Contrôle > Sélectionné > Exportation

 Import : cf document import_csv_db2.doc

 Centre de commande > onglet Résultats > Résultats d’interrogation > Sauvegarde de données > au format csv > Nom du système : <Sélection > : local (ne fonctionne qu’une seule fois, relancer).

 

La syntaxe d'une exportation vers un fichier .csv qui peut importé dans Excel:

 export to "d:\Mes documents\Référentiel Clients\ExtractRecette\Corresp_AN8\Creation_clients_Customer_PY_12dec05.csv" of del

modified by lobsinfile select CLI.AIAN8, AB.ABALKY, AB.ABALPH from CRPDTA.F03012 CLI INNER JOIN CRPDTA.F0101 AB ON

AB.ABAN8=CLI.AIAN8

WHERE CLI.AIAN8>=61664 AND CLI.AIAN8<999990 AND CLI.AICO!='00000' for read only with ur

 Attention : faire l'export en format csv puis ouvrir excel sans doublecliquer sur le fichier puis ouvrir le fichier afin de le convertir et de bien avoir l'enregistrement en entier !

 - le passage en mode "modif" involontaire dans la grille de Résultats d'un select via DB2 Command Center : cela se produit quand on clique dans l'onglet Résultats, DB2 passe en mode update et bloque la table

 - faire des SELECT x, y ... from theTable FOR READ ONLY WITH UR

 - ne pas cliquer dans la grille de Résultat ; si cela se produit involontairement, cliquer sur le bouton ROLLBACK en bas à droite de la grille pour annuler

 Connect reset lorsqu’on est déconnecté puis connect to PS_CRP user crpdta using CRPDTA

 Installation des alias DB2 (environnements) : passer par Outils ligne de commande puis fenêtre commande puis lancer dans le bon répertoire les catalog_db.bat et catalog_db_prod.bat.

 Pour copier le résultat d'un select, cliquer sur un champ puis touche Escape puis CtrlA

 

10.2     DB2/UDB et Winsql

 l'utilisation de Win SQL qui n'est pas un outil recommandé car il bloque la table même en faisant un SELECT

 Pour éviter ces problèmes, :

 - ne pas utiliser Win SQL qui avait déjà été interdit lors d'un précédent CODEV

 WinSQL est utilisé aussi pour faire des exports de données sous Excel mais cette fonctionnalité peut être obtenue parfois avec B et dans tous les cas avec DB2 Command Center.

 

11.     DB2/400

 Création d'un fichier de travail ou point/virgule pour montants : passer par F13

 

12.     SQL Server

 sur NT, consultant1 ou 2, Accès sur Microsoft SQL Server > Query Analyzer.

SQL Server : ZAGITA68, si accès à prod, SQL Server authentification : login name : zf , password : zf, sinon NT identification.

Puis choisir JDE_FRPROD

select * from FRPRODDTA.F0006

 Ou bien, choisir master par exemple et préciser l'environnement : select * from JDE_LADEV.ladevdta.f4101

 Concaténation : mlmcu + mlobj + mlsub

 

13.     Toad sur Oracle

 13.1     Paramétrage tnsnames.ora et hosts

 C:\oracle\ora92\network\ADMIN\tnsnames.ora

 # TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

 

PEODEV =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devjdeess)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = peodev)

    )

  )

 

PEOPRD=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = uxlogne145)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = peoprd)

    )

  )

  

Placer le fichier hosts pour W XP dans C:\WINDOWS\system32\drivers\etc ou pour W NT dans C:\WINNT\system32\drivers\etc

Bien mettre dans ce répertoire la correspondance IP-host (parfois indispensable). Ensuite ce fichier est supprimé sur le T23, je ne sais pas pourquoi. Recopier à chaque fois celui qui se trouve dans C:\WINDOWS\system32. Ce pb ne se produit pas sur le T41 en XP.

 

13.2     Connection

  Pré-prod : database : PRD2QW02, user = crpdta

 

13.3     Import de table à partir de fichier texte

 Database > Import > Table Data

 Mettre le fichier en 1er onglet d’excel en faisant un copy/paste special, values only.

Utiliser Automap.

Mettre les champs numériques à zéro et blanc au lieu de vide.

Utiliser l’option « don’t commit »

 

 Error "Cannot insert NULL" : ceci était dû au problème de colonne GDLNGP à vide dans excel au lieu de blanc.

 Si temps de réponse trop grand ou Msg Out of memory, importer sur un fichier avec moins d'enreg, voire un fichier bis PMA* puis faire un insert dans table réelle.

 

Si anomalies "longueur trop grande de champ" à tort, utiliser un import fichier texte créé à partir d'un csv créé à partir d'excel plutôt qu'un excel en direct, en plus c'est plus rapide !

  

13.4     Bugs

 Problème d’affichage de fenêtre sur vue SQL editor.

Solution : désinstaller toad puis supprimer répertoire puis réinstaller.

 Pb  : après un import ou sans import, enreg existent mais non affichés dans toad

Solution : utiliser aquastudio car toad est mal installé

 

13.5     Paramétrage d'une base

 IBM DB2>Outil d'installation >Assistant de config>Objets sélectionnés >Ajout d'une base

TCPIP, nom hôte : ERPTST, n° port : 50000, nom base : PSFT811, alias : PS811, cocher enreg , PS811, aucun, système : AIX, instance : peodevi

 

14.     Oracle et Aqua Data Studio

 Ouvrir database servers >JDE Labeyrie>Schema>CRPDTA puis Server>Query Anayzer

 Ou Icône Query analyser puis se connecter au serveur.

 

15.     iseries navigator DB2/400

 15.1     SQL

 Sur un poste lourd, lancer Iseries Navigator

 

 Cliquer sur le système souhaité

 

Saisir user et password (ex : JDE/JDE)

 

Cliquer jusqu'à ouvrir Databases puis Click droit souris sur database

Puis lancer Run SQL scripts

 

Saisir la requête SQL avec « ; » à la fin.

 Pour la lancer, positionner le curseur sur la requête avant le « ; » puis Ctrl+Y

  

Pour faire un copier/coller sur excel, descendre avec la barre d’ascenseur à la fin complète du résultat de la requête.

Cliquer dans les résultats puis Ctrl+A pour tout sélectionner puis Ctrl+C et Ctrl+V dans excel.

  

15.2     Divers

 Select schema to display pour faire apparaître CRPDTA.

 

15.3     Data Transfer

 Dans File name, mettre crpdta/F4095.

 

 

 

Mettre dans PC Details : CSV et ASCII car si ANSI, le transfert met un msg d'ano ou boucle.

 

15.4     Récupérer fichier créé dans JDE sur AS/400 ou Unix

 Mettre dans l'option de traitement JDE le chemin /JDEBank/virt.txt ou //JDEBank/virt.txt

 Donner les droits en écritures sur Jdebank en allant dans Root puis click droit et Sharing puis droit en lecture/écriture.

Sur Windows, pour accéder au répertoire de l'AS/400, faire Tools, map \\Jde\JDEBANK user : JDE, password : JDE

 Accès au répertoire FTP par user JDE/JDE.

 Si serveur d'entreprise = Unix, chemin avec "/" et donner l'accès à l'user JDE sur répertoire "exact" Unix. Eventuellement, mapper la BSFN.

  

N'hésitez pas à poser vos questions sur le forum !

 

Google