select t_eng.*, montantHTEng montantht, ROUND(montantHTEng + montantTVAEng, 2)…
- 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
- 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_enginside 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!
- Aliased subqueries for clarity (
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.