Conversion requête de oracle ver SQL server
Bonjour,
j'ai transcris une requête opérationnel sur Oracle vers SQL server 2008.
j'ai un petit problème de résultat sur la requête SQL server. Il ne me renvoie pas le même nombre de ligne ou j'ai bcp de ligne NULL.
Je pense que mon problème provient des jointures externes.
Requête Oracle :
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| SET VERIFY OFF;
SET HEADING OFF;
SET PAGESIZE 0;
SET LINESIZE 2000;
SET FEEDBACK OFF;
SET TERMOUT OFF;
SET TRIMSPOOL ON;
SPOOL &3;
SELECT F.SEC_NO_EQUIPE||'|'||
B.REP_CODE_TELEPHONIQUE||'|'||
B.REP_NOM||'|'||
B.REP_PRENOM||'|'||
TO_CHAR(A.HORT_DATE, 'YYYYMMDD')||'|'||
A.HORT_DUREE_ACTIVITES_MIN||'|'||
A.HORT_LEGENDE||'|'||
D.RES_CODE||'|'||
B.REP_NO_EMPLOYE||'|'||
B.REP_TELEPHONE_01
FROM CALHORAIRESXJOURXPREPOSE A,
CALPREPOSES B,
CALPREPOSESXEQUIPE C,
CALTYPESCONTRAINTE D,
CALCONTRAINTESXPREPOSE E,
CALEQUIPES F
WHERE A.HORT_DATE>=TO_DATE ('&1','DD/MM/YYYY') AND
A.HORT_DATE<=TO_DATE ('&2','DD/MM/YYYY') AND
F.SEC_SEQUENCE=C.REP_SEC_XRF_SEC_SEQUENCE AND
B.REP_SEQUENCE=C.REP_SEC_XRF_REP_SEQUENCE AND
B.REP_SEQUENCE=A.HORT_XRF_REP_SEQUENCE AND
A.HORT_LEGENDE NOT LIKE '%Non disponible%' AND
D.RES_SEQUENCE(+) = E.REP_RES_XRF_RES_SEQUENCE AND
E.REP_RES_SEQUENCE(+) = A.HORT_REF_RES_PRO_SEQUENCE
ORDER BY A.HORT_DATE,
C.REP_SEC_REF_NO_EQUIPE,
B.REP_CODE_TELEPHONIQUE,
A.HORT_HEURE_DEBUT;
SPOOL OFF;
EXIT; |
Requête SQL Server :
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
SELECT CAST(
CASE CHARINDEX('PROD ENTRAIDE', A.HORT_LEGENDE)
WHEN 0 THEN F.SEC_NO_EQUIPE
ELSE SUBSTRING(A.HORT_LEGENDE, CHARINDEX('PROD ENTRAIDE', A.HORT_LEGENDE) + 13, 7)
END AS VARCHAR) + '|' + CAST(B.REP_CODE_TELEPHONIQUE AS VARCHAR) + '|' + CAST(B.REP_NOM AS VARCHAR) + '|' + CAST(B.REP_PRENOM AS VARCHAR) + '|' + CONVERT(VARCHAR(8), A.HORT_DATE,112) + '|' + CAST(A.HORT_DUREE_ACTIVITES_MIN AS VARCHAR) + '|' + CAST(
CASE CHARINDEX('ENTRAIDE', A.HORT_LEGENDE)
WHEN 21 THEN ((SUBSTRING(A.HORT_LEGENDE, 1, 20) + 'En service ') + SUBSTRING(A.HORT_LEGENDE, 21, LEN(A.HORT_LEGENDE)))
ELSE A.HORT_LEGENDE
END AS VARCHAR) + '|' + CAST(D.RES_CODE AS VARCHAR) + '|' + CAST(B.REP_NO_EMPLOYE AS VARCHAR) + '|' + RTRIM(LTRIM(CAST(B.REP_TELEPHONE_01 AS VARCHAR)))
FROM CALTYPESCONTRAINTE D RIGHT OUTER JOIN CALCONTRAINTESXPREPOSE E ON D.RES_SEQUENCE = E.REP_RES_XRF_RES_SEQUENCE RIGHT OUTER JOIN CALHORAIRESXJOURXPREPOSE A ON E.REP_RES_SEQUENCE = A.HORT_REF_RES_PRO_SEQUENCE ,
CALPREPOSES B,
CALPREPOSESXEQUIPE C,
CALEQUIPES F
WHERE A.HORT_DATE >= CONVERT(DATETIME, "02/04/2012", 103) AND
A.HORT_DATE <= CONVERT(DATETIME, "30/04/2012", 103) AND
F.SEC_SEQUENCE=C.REP_SEC_XRF_SEC_SEQUENCE AND
B.REP_SEQUENCE=C.REP_SEC_XRF_REP_SEQUENCE AND
B.REP_SEQUENCE=A.HORT_XRF_REP_SEQUENCE AND
A.HORT_LEGENDE NOT LIKE '%Non disponible%'
ORDER BY A.HORT_DATE,
C.REP_SEC_REF_NO_EQUIPE,
B.REP_CODE_TELEPHONIQUE,
A.HORT_HEURE_DEBUT |
Merci de votre aide.