[10g] Jointure "Null" (ou autre chose)

CUCARACHA · 25/06/2010, 17h06

Salut,

J'ai fait une petite vue ci après :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
SELECT 
    M.ID_BREACH_MAIN,
    P.ID_BREACH_PED,
    M.IS_ARCHIVED AS MAIN_IS_ARCHIVED,
    P.IS_ARCHIVED AS PED_IS_ARCHIVED,
    M.ISIN,
    nvl(M.PTF_ID,'') AS PTF_ID,
    S.ID_PED_STATUS AS STATUS_ID,
    S.NAME AS STATUS_NAME,
    TO_CHAR(PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME),'999G999G999G990') AS NOMINAL,
    TO_CHAR((nvl(PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME),0) - nvl(PAK_BHR_V2.GET_POSITION_NOMINAL_PREC(M.ISIN,M.PTF_ID,T.NAME),0)),'999G999G999G990')  AS DELTA_NOMINAL_PREC,
    CASE
        WHEN (nvl(PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME),0) - nvl(PAK_BHR_V2.GET_POSITION_NOMINAL_PREC(M.ISIN,M.PTF_ID,T.NAME),0)) < 0 THEN
            '~/Images/Down.gif'
        WHEN (nvl(PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME),0) - nvl(PAK_BHR_V2.GET_POSITION_NOMINAL_PREC(M.ISIN,M.PTF_ID,T.NAME),0)) > 0 THEN
            '~/Images/Up.gif'
        ELSE
            '~/Images/Stable.gif'
    END AS IMG_PREV_NOMINAL,  
    TO_CHAR(PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME),'999G999G999G990') AS AMOUNT,
    TO_CHAR((nvl(PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME),0)-nvl(PAK_BHR_V2.GET_POSITION_AMOUNT_PREC(M.ISIN,M.PTF_ID,T.NAME),0)),'999G999G999G990')  AS DELTA_AMOUNT_PREC,
    CASE
        WHEN (nvl(PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME),0)-nvl(PAK_BHR_V2.GET_POSITION_AMOUNT_PREC(M.ISIN,M.PTF_ID,T.NAME),0)) < 0 THEN
            '~/Images/Down.gif'
        WHEN (nvl(PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME),0)-nvl(PAK_BHR_V2.GET_POSITION_AMOUNT_PREC(M.ISIN,M.PTF_ID,T.NAME),0)) > 0 THEN
            '~/Images/Up.gif'
        ELSE
            '~/Images/Stable.gif'
    END AS IMG_PREV_AMOUNT,  
    (
        NVL
        (
            CONCAT
            (
                TO_CHAR ((PAK_BHR_V2.Get_POSITION_NOMINAL(M.ISIN,M.PTF_ID,T.NAME)/PAK_BHR_V2.Get_POSITION_AMOUNT(M.ISIN,M.PTF_ID,T.NAME))*100,'990D00')
                ,
                ' %'
            )
            ,''
        )
    ) AS RATIO,
    PAK_BHR_V2.GET_POSITION_LIMIT(T.NAME) AS BREACH_RATIO,
    P.FK_TYP_BREACH,
    T.NAME AS BREACH_TYPE,
    PAK_BHR_V2.Get_POSITION_IS_NEW_BREACH(P.ID_BREACH_PED) AS IS_NEW_BREACH,
    TO_CHAR(P.START_DATE,'DD/MM/YYYY') AS START_DATE,
    nvl(PAK_BHR_V2.Get_POSITION_LAST_DATE(P.ID_BREACH_PED),'<span style="color:#cccccc;font-style:italic;">open</span>') AS END_DATE,
    P.RM_MSG,
    P.IS_ARCHIVED
FROM 
    BHR_BREACH_MAIN M
        INNER JOIN BHR_BREACH_PED P  
            ON P.FK_BREACH_MAIN = M.ID_BREACH_MAIN
        RIGHT OUTER JOIN BHR_PED_STATUS S
            ON P.FK_PED_STATUS = S.ID_PED_STATUS
        RIGHT OUTER JOIN BHR_TYP_BREACH T
            ON P.FK_TYP_BREACH = T.ID_TYP_BREACH
        LEFT OUTER JOIN BHR_CALCULS C
            ON M.ISIN = C.ISIN AND M.PTF_ID = C.ID_PTF
ORDER BY
    MAIN_IS_ARCHIVED,
    PED_IS_ARCHIVED,
    M.ISIN,
    M.PTF_ID,
    P.START_DATE DESC

Mon problème est que malgrès le nvl, nvl(M.PTF_ID,'') AS PTF_ID me renvoie une chaine de caractère 'Null' même si je mets nvl(M.PTF_ID,'[N.A.]').

Du coup j'ai envie de me suicider dans un haut fourneau façon Alien 3 en tenant ma boite Oracle et ses CD bien fort contre mon ventre pour être certain qu'elle ne nuira plus à personne...

Un petit coup de main serait le bienvenu...

++

Laurent