Skip to content
The Chat Archive
gpt-4.1-2025-04-14· OpenAI LMArena Human Preference 140K

select t_eng.*, montantHTEng montantht, ROUND(montantHTEng + montantTVAEng, 2)…

  1. User

    select t_eng.*, montantHTEng montantht, ROUND(montantHTEng + montantTVAEng, 2) montantttc, montantTVAEng montanttva, DECODE(f_engagementishtorttc(ege_idege), 'HT', montantHTEng-montantHTDeg, (montantHTEng + montantTVAEng)-(montantHTDeg + montantTVADeg)) solde, case when f_engagementishtorttc(ege_idege)= 'HT' then case when montantHTEng-montantHTDeg <= 0 then 'SOLDER' else 'NON_SOLDER' end when f_engagementishtorttc(ege_idege)= 'TTC' then case when ((montantHTEng + montantTVAEng)-(montantHTDeg + montantTVADeg)) <= 0 then 'SOLDER' else 'NON_SOLDER' end else 'NON_SOLDER' end as isSolde, f_engagementishtorttc(ege_idege) indicateurtva, 'N' coche, case when f_engagementishtorttc(ege_idege) = 'HT' then f_getmontanthtengagement(ege_idege) when f_engagementishtorttc(ege_idege) = 'TTC' then f_getmontantttcengagement(ege_idege) else null end as montantInitialGlobal from t_eng inner join ( select tel.egl_idEge, coalesce(SUM(tel.egl_monht), 0) montantHTEng, coalesce(SUM(tel.egl_monTVA), 0) montantTVAEng from t_eng_lig tel inner join t_eng on ege_idEge = egl_idEge group by tel.egl_idEge) montantsLigne on (ege_idEge = montantsLigne.egl_idEge) inner join ( select tel2.egl_idEge, coalesce(SUM(ecr_monhtDEG), 0) montantHTDeg, coalesce(SUM(ecr_monTvaDEG), 0) montantTvaDeg from t_ecr inner join t_eng_lig tel2 on egl_idegl = ecr_idegl group by tel2.egl_idEge) montantsEcriture on (ege_idEge = montantsEcriture.egl_idEge)

    simplify this request

  2. Assistant

    Certainly! Your SQL query is a bit complex due to the use of subqueries, function calls, and conditional logic. Here’s a simplified and reformatted version with comments for clarity. The logic is preserved, but the structure is made clearer and more readable.


    SELECT
        t_eng.*,
        montantsLigne.montantHTEng AS montantht,
        ROUND(montantsLigne.montantHTEng + montantsLigne.montantTVAEng, 2) AS montantttc,
        montantsLigne.montantTVAEng AS montanttva,
        -- Calculate solde based on HT or TTC
        CASE
            WHEN f_engagementishtorttc(ege_idege) = 'HT' THEN
                montantsLigne.montantHTEng - montantsEcriture.montantHTDeg
            ELSE
                (montantsLigne.montantHTEng + montantsLigne.montantTVAEng)
                - (montantsEcriture.montantHTDeg + montantsEcriture.montantTVADeg)
        END AS solde,
        -- Determine if the engagement is settled (SOLDER) or not (NON_SOLDER)
        CASE
            WHEN f_engagementishtorttc(ege_idege) = 'HT' THEN
                CASE
                    WHEN montantsLigne.montantHTEng - montantsEcriture.montantHTDeg <= 0 THEN 'SOLDER'
                    ELSE 'NON_SOLDER'
                END
            WHEN f_engagementishtorttc(ege_idege) = 'TTC' THEN
                CASE
                    WHEN (montantsLigne.montantHTEng + montantsLigne.montantTVAEng)
                         - (montantsEcriture.montantHTDeg + montantsEcriture.montantTVADeg) <= 0 THEN 'SOLDER'
                    ELSE 'NON_SOLDER'
                END
            ELSE 'NON_SOLDER'
        END AS isSolde,
        f_engagementishtorttc(ege_idege) AS indicateurtva,
        'N' AS coche,
        -- Get initial global amount based on HT or TTC
        CASE
            WHEN f_engagementishtorttc(ege_idege) = 'HT' THEN f_getmontanthtengagement(ege_idege)
            WHEN f_engagementishtorttc(ege_idege) = 'TTC' THEN f_getmontantttcengagement(ege_idege)
            ELSE NULL
        END AS montantInitialGlobal
    FROM
        t_eng
        -- Join to get total HT and TVA per engagement
        INNER JOIN (
            SELECT
                egl_idEge,
                COALESCE(SUM(egl_monht), 0) AS montantHTEng,
                COALESCE(SUM(egl_monTVA), 0) AS montantTVAEng
            FROM t_eng_lig
            GROUP BY egl_idEge
        ) montantsLigne ON t_eng.ege_idEge = montantsLigne.egl_idEge
        -- Join to get total HT and TVA from writings per engagement
        INNER JOIN (
            SELECT
                tel2.egl_idEge,
                COALESCE(SUM(ecr_monhtDEG), 0) AS montantHTDeg,
                COALESCE(SUM(ecr_monTvaDEG), 0) AS montantTVADeg
            FROM t_ecr
            INNER JOIN t_eng_lig tel2 ON tel2.egl_idegl = t_ecr.ecr_idegl
            GROUP BY tel2.egl_idEge
        ) montantsEcriture ON t_eng.ege_idEge = montantsEcriture.egl_idEge
    

    Key Simplifications:

    • Aliased subqueries for clarity (montantsLigne, montantsEcriture).
    • CASE statements are indented and simplified for readability.
    • Column aliases are used for clarity.
    • Removed unnecessary inner joins (the original had redundant joins to t_eng inside subqueries).
    • Comments added for each logical block.

    If you want it even more concise, you can remove comments and extra whitespace, but for maintainability, this format is recommended.

    Let me know if you want a version with even fewer columns or further simplification!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.