Menu

Convertir MY SQL dans Access

Katerina85_55 23 Messages postés lundi 19 février 2018Date d'inscription 16 mars 2018 Dernière intervention - 28 févr. 2018 à 11:02
Bonjour à tous,

J'aimerais adapter ma base créée dans My SQL Server à Access. N'étant pas du tout un expert dans ce domaine, je me permet de vous demander de l'aide.
A chaque étape d'avancement dans Access je rencontre des problèmes de syntaxe qui est légèrement différent à My SQL Server (les parenthèses, IFF au lieu de case when, mais mon grande problème ce sont les parenthèses! je ne comprends pas à quel moment les introduire, etc.)
Aurez qqun la gentillesse de convertir mon code My SQL dans Access svp?

Le code est basé sur plusieurs tables "tirées" de ERP. J''utilise Access 2007

Merci d'avance!
SELECT
/*Information générale commande d'achat*/
DATA0023.SUPPLIER_NAME as 'Fournisseur',
DATA0070.PO_NUMBER as 'Numéro PO',
DATA0070.PO_DATE as 'Date de création PO',
YEAR (DATA0070.PO_DATE) as 'Année (date PO)',
CASE WHEN MONTH(DATA0070.PO_DATE) = 1 THEN 'Janvier'
  WHEN MONTH(DATA0070.PO_DATE) = 2 THEN 'Février'
  WHEN MONTH(DATA0070.PO_DATE) = 3 THEN 'Mars'
  WHEN MONTH(DATA0070.PO_DATE) = 4 THEN 'Avril'
  WHEN MONTH(DATA0070.PO_DATE) = 5 THEN 'Mai'
  WHEN MONTH(DATA0070.PO_DATE) = 6 THEN 'Juin'
  WHEN MONTH(DATA0070.PO_DATE) = 7 THEN 'Juillet'
  WHEN MONTH(DATA0070.PO_DATE) = 8 THEN 'Août'
  WHEN MONTH(DATA0070.PO_DATE) = 9 THEN 'Septembre'
  WHEN MONTH(DATA0070.PO_DATE) = 10 THEN 'Octobre'
  WHEN MONTH(DATA0070.PO_DATE) = 11 THEN 'Novembre'
  WHEN MONTH(DATA0070.PO_DATE) = 12 THEN 'Décembre'
  ELSE'-'
  END as 'Mois (date PO)',
/*Information générale Standard PO*/
CASE WHEN DATA0017.INV_PART_NUMBER IS NOT NULL THEN DATA0017.INV_PART_NUMBER
  ELSE DATA0072.DESCRIPTION 
  END as 'Article ST_MiSC',
CASE WHEN DATA0017.INV_PART_DESCRIPTION IS NOT NULL THEN DATA0017.INV_PART_DESCRIPTION
  ELSE DATA0072.DESCRIPTION2 
  END as 'Description article ST_MiSC',
CASE WHEN DATA0071.REQ_DATE IS NOT NULL THEN DATA0071.REQ_DATE 
  ELSE DATA0072.DEL_DATE 
  END as 'Cicor delivery date ST_MiSC',
DATA0071.DEL_DATE as 'Requested ship date ST',
CASE WHEN DATA0071.QUAN_ORD IS NOT NULL THEN DATA0071.QUAN_ORD
  ELSE DATA0072.QUAN_ORD 
  END as 'Qté commandée ST_MiSC',
DATA0022.QUANTITY as 'Qté réceptionnée 1 (22)ST',
DATA0022.TDATE as 'Date réception 1 (22) ST',
CASE WHEN DATA0022.TTYPE=2 THEN 'from PO'
  ELSE '-'
  END as 'Type de transaction (22) ST',
CASE WHEN DATA0206.QUANTITY IS NOT NULL THEN DATA0206.QUANTITY
  ELSE DATA0235.QUAN_RECD 
  END as 'Qté réceptionnée 2 (206) ST_MiSC',
CASE WHEN DATA0206.TRANSACTION_DATE IS NOT NULL THEN DATA0206.TRANSACTION_DATE 
  ELSE DATA0235.DATE_RECD 
  END as 'Date réception 2 (206) ST_MiSC',
CASE WHEN YEAR (DATA0206.TRANSACTION_DATE) IS NOT NULL THEN YEAR (DATA0206.TRANSACTION_DATE)
  ELSE YEAR (DATA0235.DATE_RECD) 
  END as 'Année (date de récéption) ST_MiSC',
CASE 
 WHEN DATA0070.STATUS = 1 THEN 'active'
 WHEN DATA0070.STATUS = 2 THEN 'closed'
 WHEN DATA0070.STATUS = 3 THEN 'cancelled'
 WHEN DATA0070.STATUS = 4 THEN 'reserved'
 WHEN DATA0070.STATUS = 5 THEN 'on hold'
 WHEN DATA0070.STATUS = 6 THEN 'completed'
 END as 'Status PO',
DATA0028.LEAD_TIME as 'Manufacturing lead time ST (en jours)',
DATA0024.SHIPPING_LEAD_TIME as 'Shipping lead time (en jours)',
DATA0024.FOB as 'Incoterm',
CASE 
 WHEN DATA0070.PO_TYPE = 0 THEN 'standard PO'
 WHEN DATA0070.PO_TYPE = 1 THEN 'miscellaneous PO'
 ELSE '-'
 END as 'Type PO',
CASE WHEN DATA0023.ANALYSIS_CODE1='1' THEN 'Stratégique'
  WHEN DATA0023.ANALYSIS_CODE1='2' THEN 'Non-stratégique'               
  ELSE'-'
  END as 'Classification Fournisseur',
CASE WHEN DATA0023.SUPPLIER_NAME='HOFSTETTER PCB AG' 
 OR DATA0023.SUPPLIER_NAME='MECANOR SA'
 OR DATA0023.SUPPLIER_NAME='ROLF HÄNGGI'
 OR DATA0023.SUPPLIER_NAME='COLLINI-FLÜHMANN AG'
 OR DATA0023.SUPPLIER_NAME='KAUFMANN ETAMPES SA'
 OR DATA0023.SUPPLIER_NAME='GALVAMETAL AG  ESTOPPEY-REBER'
 OR DATA0023.SUPPLIER_NAME='MICROCONTACT AG'
 OR DATA0023.SUPPLIER_NAME='ROBERT LAMINAGE'
 OR DATA0023.SUPPLIER_NAME='CPPCF HERMINJARD' 
 OR DATA0023.SUPPLIER_NAME='HYBRID SA'
 THEN 'Sous-traitant'
 ELSE 'Fournisseur'
 END as 'Type de fournisseur',
DATA0070.ANALYSIS_CODE_1  'Supplier confirmation (CA 1 PO)',
DATA0070.ANALYSIS_CODE_2 as 'Date initiale demandé (CA 2 PO)',
DATA0024.LOC_ANALYSIS_CODE1 as '% Over delivery',
CASE WHEN DATA0022.QUANTITY > DATA0206.QUANTITY THEN 'Ajustement ST'
 WHEN DATA0022.TDATE<>DATA0206.TRANSACTION_DATE OR DATA0206.TRANSACTION_DATE<>DATA0206.ENTERED_DATE OR DATA0022.TDATE<>DATA0206.ENTERED_DATE THEN 'Ajustement ST'
 ELSE '-'
 END as 'Contrôle ajustements',
CASE WHEN DATA0023.SUPPLIER_NAME='Sous-traitant' THEN 'En ordre'
  WHEN DATA0070.ANALYSIS_CODE_1='' OR DATA0070.ANALYSIS_CODE_1 IS NULL AND LEFT(DATA0024.FOB,3)='DAP' OR LEFT(DATA0024.FOB,3)='DAT' OR LEFT(DATA0024.FOB,3)='DDP' THEN 'En ordre'
  WHEN DATA0070.ANALYSIS_CODE_1<>'' AND LEFT(DATA0024.FOB,3)='EXW' OR LEFT(DATA0024.FOB,3)='CIP' OR LEFT(DATA0024.FOB,3)='FCA' OR LEFT(DATA0024.FOB,3)='CPT' OR LEFT(DATA0024.FOB,3)='FAS' OR LEFT(DATA0024.FOB,3)='FOB' OR LEFT(DATA0024.FOB,3)='CFR' OR LEFT(DATA0024.FOB,3)='CIF' THEN 'En ordre'
  ELSE'A contrôler'
  END as 'Cotnrôle Supplier Confirmation',
CASE WHEN DATA0070.PO_NUMBER IS NOT NULL THEN 3
  ELSE ''
  END as 'Limite ALT MiSC 1',
CASE WHEN DATA0070.PO_NUMBER IS NOT NULL THEN 5
  ELSE ''
  END as 'Limite ALT MiSC 2',
CASE WHEN DATA0070.PO_NUMBER IS NOT NULL THEN 0.02
  ELSE ''
  END as 'Limite NC 1',
CASE WHEN DATA0070.PO_NUMBER IS NOT NULL THEN 0.05
  ELSE ''
  END as 'Limite NC 2'


FROM
DATA0070

FULL OUTER JOIN
DATA0071 DATA0071 WITH(NOLOCK)
ON DATA0070.RKEY=DATA0071.PO_PTR
AND DATA0070.PO_TYPE=0

FULL OUTER JOIN
DATA0072 DATA0072 WITH(NOLOCK)
ON DATA0070.RKEY=DATA0072.POPTR
AND DATA0070.PO_TYPE=1

FULL OUTER JOIN
DATA0017 DATA0017 WITH(NOLOCK)
ON DATA0071.INVT_PTR=DATA0017.RKEY

LEFT OUTER JOIN
DATA0023
ON DATA0070.SUPPLIER_POINTER=DATA0023.RKEY

/*Liaison data0028 on data0017 -> liaison article + on data0028 -> liaison Fournisseur car lead time article et différent suivant le Fournisseur (idem pour le prix)*/
LEFT OUTER JOIN
DATA0028 DATA0028 WITH (NOLOCK)
ON DATA0017.RKEY=DATA0028.INVENTORY_PTR
AND DATA0023.RKEY=DATA0028.SUPPLIER_PTR

LEFT OUTER JOIN
DATA0206
ON DATA0071.RKEY=DATA0206.PO_PTR

LEFT OUTER JOIN
DATA0235 DATA0235 WITH (NOLOCK)
ON DATA0072.RKEY=DATA0235.D0072_PTR

LEFT OUTER JOIN
DATA0022
ON DATA0206.DATA22_PTR=DATA0022.RKEY 

LEFT OUTER JOIN
DATA0024 DATA0024 WITH(NOLOCK)
ON DATA0023.RKEY=DATA0024.SUPPLIER_PTR

WHERE 
DATA0070.PO_DATE > '01.01.2017'
AND DATA0070.STATUS <> 3 
AND DATA0070.STATUS <> 4 
AND DATA0070.STATUS <> 5 
AND DATA0023.ANALYSIS_CODE1='1'
/*Ajout de modification -> AND (DATA0022.STATUS IS NULL OR DATA0022.STATUS=5) -> permet exclure les transactions de réception cancelled pur éviter des lignes supplémentaires*/
AND (DATA0022.STATUS IS NULL OR DATA0022.STATUS=5)


ORDER BY
DATA0070.PO_NUMBER


EDIT : Ajout des balises code code !
Afficher la suite