Toad World® Forums

Formatting problem

Hi

Just got a problem formatting an SQL :

Version 6.7 (OK) :

SELECT artc, SUM(qt_colis) qt_colis, SUM(brut_wght) brut_wght, SUM(net_wght) net_wght

FROM (SELECT TO_NUMBER(d_dopr.c40_10) artc

         , **SUM**(d_artc.c31_02) qt_colis

         , **SUM**(d_artc.c35_02) brut_wght

         , **SUM**(d_artc.c38_02) net_wght

      **FROM** d_dopr, d_artc, d_head

     **WHERE** d_dopr.cod_pays = ps_cod_pays

           **AND** d_dopr.c40_03 = vs_mrn

           **AND** **TO_NUMBER**(d_dopr.c40_10) = vn_artc

           **AND** d_artc.cod_pays = d_dopr.cod_pays

           **AND** d_artc.succ = d_dopr.succ

           **AND** d_artc.lien_detail = d_dopr.lien_detail

           **AND** d_artc.num_lg_artc = d_dopr.num_lg_artc

           **AND** d_head.cod_pays = d_dopr.cod_pays

           **AND** d_head.succ = d_dopr.succ

           **AND** d_head.lien_detail = d_dopr.lien_detail

           **AND** d_head.mrn **IS** **NOT** **NULL**

           **AND** d_head.cod_status_gen = 'TER'

           **AND** d_head.statut **NOT** **IN** ('D', 'ARCHIVE')

           **AND** (d_dopr.cod_pays, d_dopr.succ, d_dopr.lien_detail, d_dopr.num_lg_artc) **NOT** **IN**

                 (**SELECT** d_dopr.cod_pays, d_dopr.succ, d_dopr.lien_detail, d_dopr.num_lg_artc

                    **FROM** d_docu, d_dopr, d_head

                   **WHERE** d_dopr.c40_03 = vs_mrn

                         **AND** d_dopr.cod_pays = ps_cod_pays

                         **AND** **TO_NUMBER**(d_dopr.c40_10) = vn_artc

                         **AND** d_docu.cod_pays = d_dopr.cod_pays

                         **AND** d_docu.succ = d_dopr.succ

                         **AND** d_docu.lien_detail = d_dopr.lien_detail

                         **AND** d_docu.num_lg_artc = d_dopr.num_lg_artc

                         **AND** d_docu.c44_21 = '3039'

                         **AND** d_docu.c44_29 **IS** **NOT** **NULL**

                         **AND** d_head.cod_pays = d_dopr.cod_pays

                         **AND** d_head.succ = d_dopr.succ

                         **AND** d_head.lien_detail = d_dopr.lien_detail

                         **AND** d_head.mrn **IS** **NOT** **NULL**

                         **AND** d_head.cod_status_gen = 'TER'

                         **AND** d_head.statut **NOT** **IN** ('D', 'ARCHIVE'))

    **GROUP** **BY** d_dopr.c40_10

    **UNION**

    **SELECT** **TO_NUMBER**(d_dopr.c40_10) artc

         , **SUM**(d_docu.c44_29) qt_colis

         , **SUM**(d_artc.c35_02) brut_wght

         , **SUM**(d_artc.c38_02) net_wght

      **FROM** d_dopr, d_artc, d_head, d_docu

     **WHERE** d_dopr.cod_pays = ps_cod_pays

           **AND** d_dopr.c40_03 = vs_mrn

           **AND** **TO_NUMBER**(d_dopr.c40_10) = vn_artc

           **AND** d_docu.cod_pays = d_dopr.cod_pays

           **AND** d_docu.succ = d_dopr.succ

           **AND** d_docu.lien_detail = d_dopr.lien_detail

           **AND** d_docu.num_lg_artc = d_dopr.num_lg_artc

           **AND** d_docu.c44_21 = '3039'

           **AND** d_docu.c44_29 **IS** **NOT** **NULL**

           **AND** d_docu.c44_29b **IS** **NOT** **NULL**

           **AND** d_artc.cod_pays = d_dopr.cod_pays

           **AND** d_artc.succ = d_dopr.succ

           **AND** d_artc.lien_detail = d_dopr.lien_detail

           **AND** d_artc.num_lg_artc = d_dopr.num_lg_artc

           **AND** d_head.cod_pays = d_dopr.cod_pays

           **AND** d_head.succ = d_dopr.succ

           **AND** d_head.lien_detail = d_dopr.lien_detail

           **AND** d_head.mrn **IS** **NOT** **NULL**

           **AND** d_head.cod_status_gen = 'TER'

           **AND** d_head.statut **NOT** **IN** ('D', 'ARCHIVE')

    **GROUP** **BY** d_dopr.c40_10)

GROUP BY artc

ORDER BY artc;

Version from 7.2 beta :


SELECT artc, SUM(qt_colis) qt_colis, SUM(brut_wght) brut_wght, SUM(net_wght) net_wght

FROM (SELECT TO_NUMBER(d_dopr.c40_10) artc

         , **SUM**(d_artc.c31_02) qt_colis

         , **SUM**(d_artc.c35_02) brut_wght

         , **SUM**(d_artc.c38_02) net_wght

      **FROM** d_dopr, d_artc, d_head

     **WHERE** d_dopr.cod_pays = ps_cod_pays

           **AND** d_dopr.c40_03 = vs_mrn

           **AND** **TO_NUMBER**(d_dopr.c40_10) = vn_artc

           **AND** d_artc.cod_pays = d_dopr.cod_pays

           **AND** d_artc.succ = d_dopr.succ

           **AND** d_artc.lien_detail = d_dopr.lien_detail

           **AND** d_artc.num_lg_artc = d_dopr.num_lg_artc

           **AND** d_head.cod_pays = d_dopr.cod_pays

           **AND** d_head.succ = d_dopr.succ

           **AND** d_head.lien_detail = d_dopr.lien_detail

           **AND** d_head.mrn **IS** **NOT** **NULL**

           **AND** d_head.cod_status_gen = 'TER'

           **AND** d_head.statut **NOT** **IN** ('D', 'ARCHIVE')

           **AND** (d_dopr.cod_pays, d_dopr.succ, d_dopr.lien_detail, d_dopr.num_lg_artc) **NOT** **IN** (**SELECT** d_dopr.cod_pays

                                                                                                   , d_dopr.succ

                                                                                                   , d_dopr.lien_detail

                                                                                                   , d_dopr.num_lg_artc

                                                                                                **FROM** d_docu, d_dopr, d_head

                                                                                               **WHERE** d_dopr.c40_03 = vs_mrn

                                                                                                     **AND** d_dopr.cod_pays =

                                                                                                           ps_cod_pays

                                                                                                     **AND** **TO_NUMBER**(

                                                                                                           d_dopr.c40_10) =

                                                                                                           vn_artc

                                                                                                     **AND** d_docu.cod_pays =

                                                                                                           d_dopr.cod_pays

                                                                                                     **AND** d_docu.succ =

                                                                                                           d_dopr.succ

                                                                                                     **AND** d_docu.lien_detail =

                                                                                                           d_dopr.lien_detail

                                                                                                     **AND** d_docu.num_lg_artc =

                                                                                                           d_dopr.num_lg_artc

                 &

Apparently the “folded IN” got broken as of version 5.252 of the QP5.dll component.

I’ve fixed it.

Thanks for reporting this,

Andre

Martin, this has been fixed in the latest beta 3991. Please check it out.

Hi Vincent

Works fine now. thanks.

Regards

Martin