Yes Sir, this my request :
WITH
Champs
AS
(SELECT Rch.Seqnc AS Ref_regchamp,
Rch.Code,
:Pnu_ref_registre AS Ref_registre,
Rch.Libelle AS Libelle_champ,
Rch.Format_champ_afich,
Rsc.Ordre_presn AS Ordre_presn_section,
Rch.Ordre_presn AS Ordre_presn_champ,
Vfo.Format_oracle AS Format_oracle
FROM Regsection_champ Rsc
INNER JOIN Regchamp Rch ON Rsc.Seqnc = Rch.Ref_regsection_champ
INNER JOIN V_reg_formt_oracle Vfo ON Rch.Format_champ_afich = Vfo.Format_champ_afich),
Champs_regst
AS
(SELECT Rch.Ref_registre,
Rch.Code,
Rch.Libelle_champ,
NULL AS Valeur_champ,
Rch.Ordre_presn_section,
Rch.Ordre_presn_champ,
Rch.Format_oracle
FROM Champs Rch
WHERE NOT EXISTS
(SELECT Seqnc
FROM Registre_valeur_champ Rvc
WHERE Rvc.Ref_registre = Rch.Ref_registre
AND Rvc.Ref_regchamp = Rch.Ref_regchamp)
UNION ALL
SELECT Rch.Ref_registre,
Rch.Code,
Rch.Libelle_champ,
Gpatr_04_regst_champ.Convr_anytp_varchar2 (Rvc.Valeur, Rch.Code, Rch.Format_oracle)
AS Valeur_champ,
Rch.Ordre_presn_section,
Rch.Ordre_presn_champ,
Rch.Format_oracle
FROM Champs Rch
INNER JOIN Registre_valeur_champ Rvc
ON Rvc.Ref_registre = Rch.Ref_registre AND Rvc.Ref_regchamp = Rch.Ref_regchamp
INNER JOIN V_reg_formt_oracle Vfo
ON Rch.Format_champ_afich = Vfo.Format_champ_afich)
SELECT Json_objectagg (
KEY Code VALUE (
Json_object ( KEY 'LIBELLE_CHAMP' VALUE Libelle_champ,
KEY 'VALEUR_CHAMP' VALUE Valeur_champ,
KEY 'FORMAT_ORACLE' VALUE Format_Oracle,
KEY 'ORDRE_PRESN_SECTION' VALUE Ordre_presn_section,
KEY 'ORDRE_PRESN_CHAMP' VALUE Ordre_presn_CHAMP
)
)
)
AS Regst_valr_champ_json
FROM Champs_regst Rch
This is my result:
The fisrt date like "NOMUSUEL" is my KEY Code VALUE
{
"NOMUSUEL": {
"LIBELLE_CHAMP": "Nom usuel",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": null,
"ORDRE_PRESN_SECTION": 1,
"ORDRE_PRESN_CHAMP": 1
},
"SURFACEDELAPLATEFORM": {
"LIBELLE_CHAMP": "Surface de la plateforme",
"VALEUR_CHAMP": " 25,00",
"FORMAT_ORACLE": "999G999G999G999G990D00",
"ORDRE_PRESN_SECTION": 3,
"ORDRE_PRESN_CHAMP": 1
},
"ANNEEFONCT": {
"LIBELLE_CHAMP": "Année d'entrée en fonction",
"VALEUR_CHAMP": " 2 014",
"FORMAT_ORACLE": "999G999G999G999G990",
"ORDRE_PRESN_SECTION": 99,
"ORDRE_PRESN_CHAMP": 1
},
"DATEDEVALUATION": {
"LIBELLE_CHAMP": "Date d'évaluation",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": "DD-Month-YYYY",
"ORDRE_PRESN_SECTION": 99,
"ORDRE_PRESN_CHAMP": 3
},
"C2527": {
"LIBELLE_CHAMP": "Couleur",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": null,
"ORDRE_PRESN_SECTION": 4,
"ORDRE_PRESN_CHAMP": 4
},
"GALERIE": {
"LIBELLE_CHAMP": "Galerie",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": null,
"ORDRE_PRESN_SECTION": 4,
"ORDRE_PRESN_CHAMP": 6
},
"ORIENTATION": {
"LIBELLE_CHAMP": "Orientation",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": null,
"ORDRE_PRESN_SECTION": 4,
"ORDRE_PRESN_CHAMP": 1
},
"ORDINAIRE": {
"LIBELLE_CHAMP": "Ordinairedd",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": null,
"ORDRE_PRESN_SECTION": 4,
"ORDRE_PRESN_CHAMP": 2
},
"ORDINAIREPLUS": {
"LIBELLE_CHAMP": "Ordinaire+",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": null,
"ORDRE_PRESN_SECTION": 4,
"ORDRE_PRESN_CHAMP": 3
},
"DIMENSION": {
"LIBELLE_CHAMP": "Dimension",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": null,
"ORDRE_PRESN_SECTION": 4,
"ORDRE_PRESN_CHAMP": 5
},
"TOICAROLE": {
"LIBELLE_CHAMP": "Toi Carole",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": null,
"ORDRE_PRESN_SECTION": 99,
"ORDRE_PRESN_CHAMP": 2
},
"COMMENTAIRE": {
"LIBELLE_CHAMP": "Commentaire",
"VALEUR_CHAMP": null,
"FORMAT_ORACLE": null,
"ORDRE_PRESN_SECTION": 3,
"ORDRE_PRESN_CHAMP": 1
}
}