5 Replies Latest reply on Dec 22, 2016 10:36 PM by Vadim Tropashko-Oracle

    Formatter on 4.2 EA2

    sarlaac

      I grabbed the new EA2 release today and wanted to see if format issues I had with first EA were fixed.. Unfortunately they don't seem to be, so posting them now.

       

      format settings used.

      I'd like to see

                - CASE have a before option for each.

                - INTO breaks after. would like to have no breaks.

                - Boolean, no before option.

       

      The issues I see are

           - concatenation setting ignored.

           - In the example below, after first LEFT JOIN seems to just pile everything together.

       

      Query I was testing with, displayed after running the format settings above.

                  SELECT alc1.displayed_field type

                     ,   ip.payee_address1

                       ||  ' '

                       ||  ip.payee_city

                       ||  ' '

                       ||  ip.payee_state

                       ||  ' '

                       ||  ip.payee_postal_code tp_address

                     ,   ip.payee_country

                     ,   ac.address_line1

                       ||  ' '

                       ||  ac.city

                       ||  ' '

                       ||  ac.state

                       ||  ' '

                       ||  ac.zip

                       ||  ' '

                       ||  ac.province payment_address

                     ,   ip.payment_process_request_name

                     ,   ip.payment_status

                     ,   ac.base_amount functional_amount

                  FROM apps.iby_payments_all ip

                      JOIN apps.ap_checks_all ac ON ip.payment_id = ac.payment_id

                      JOIN apps.ap_suppliers s ON ac.vendor_id = s.vendor_id

                  LEFT JOIN apps.ap_lookup_codes alc1

                  ON ac.payment_type_flag = alc1.lookup_code AND alc1.lookup_type = 'PAYMENT TYPE' LEFT JOIN apps.ce_payment_documents pd

                  ON ac.payment_document_id = pd.payment_document_id WHERE ip.payment_date BETWEEN v_start_date AND v_end_date AND ip.paper_document_number BETWEEN

      nvl(p_doc_from,ip.paper_document_number) AND nvl(p_doc_to,ip.paper_document_number) AND ( p_org_id IS NULL OR ip.org_id = p_org_id

                  ) AND ip.int_bank_account_number = nvl(p_bank,ip.int_bank_account_number) AND ( p_pay_method IS NULL OR ip.payment_method_code = p_pay_method

                  ) AND ( p_currency IS NULL OR ip.payment_currency_code = p_currency

                  );

       

       

      Using SQL Dev 4.1.5 I am able to get the following formatting, which is what I was hoping for.

      SELECT   alc1.displayed_field type

        ,ip.payee_address1 || ' ' || ip.payee_city || ' ' || ip.payee_state || ' ' || ip.payee_postal_code tp_address

        ,ip.payee_country

        ,ac.address_line1 || ' ' || ac.city || ' ' || ac.state || ' ' || ac.zip || ' ' || ac.province  payment_address

        ,ip.payment_process_request_name

        ,ip.payment_status

        ,ac.base_amount functional_amount

        FROM apps.iby_payments_all ip

        JOIN apps.ap_checks_all ac

        ON ip.payment_id = ac.payment_id

        JOIN apps.ap_suppliers s

        ON ac.vendor_id = s.vendor_id

        LEFT JOIN apps.ap_lookup_codes alc1

        ON ac.payment_type_flag = alc1.lookup_code

          AND alc1.lookup_type = 'PAYMENT TYPE'

        LEFT JOIN apps.ce_payment_documents pd

        ON ac.payment_document_id = pd.payment_document_id

        WHERE ip.payment_date BETWEEN v_start_date AND v_end_date

          AND ip.paper_document_number BETWEEN NVL(p_doc_from ,ip.paper_document_number) AND NVL(p_doc_to ,ip.paper_document_number)

          AND(p_org_id IS NULL

          OR ip.org_id = p_org_id)

          AND ip.int_bank_account_number = NVL(p_bank ,ip.int_bank_account_number)

          AND(p_pay_method IS NULL

          OR ip.payment_method_code = p_pay_method)

          AND(p_currency IS NULL

          OR ip.payment_currency_code = p_currency) ;

       

       

      PS. Was trying the 4.2 EA in hopes it would format queries in sys_refcursors, and YES! it does, so looking forward to that.

       

      PPS. Really love SQL Developer.

        • 1. Re: Formatter on 4.2 EA2
          thatJeffSmith-Oracle

          I'll log a bug on the concatenation, thanks!

          • 2. Re: Formatter on 4.2 EA2
            Jim 13131

            The line break on commas and SELECT/FROM/WHERE are also not functioning in EA2. My settings are as shown here:

             

            Capture.PNG

             

            Yet this statement

             

            SELECT dummy, dummy, dummy FROM dual WHERE 1 = 1;

             

            formats as:

             

            SELECT dummy,

                   dummy,

                   dummy

            FROM dual

            WHERE 1 = 1;

             

             

            Jim

            • 3. Re: Formatter on 4.2 EA2
              thatJeffSmith-Oracle

              I reckon the 'no breaks' falls all under the same bug.

              • 4. Re: Formatter on 4.2 EA2
                sarlaac

                Any thoughts on the LEFT JOIN situation? Is that a bug or something else?

                 

                I assume my other observations are just functions missing from the EA2 since the 4.1.5 version had those options.

                • 5. Re: Formatter on 4.2 EA2
                  Vadim Tropashko-Oracle

                  This is bug as well. (It formats fine with Line breaks ->compound logical expressions = true)

                   

                  There is also outer join indentation bug which can be fixed right away. On the "custom format" page edit that obscure rule list as follows:

                   

                  ...

                  | [node) original_method_body_specification

                  /* insert this -> */ | [node) outer_join_clause

                  | [node) parallel_clause

                  ...

                   

                  With "Line breaks -> Ansi Joins = true" I get:

                   

                  SELECT

                      alc1.displayed_field type

                     ,ip.payee_address1

                       ||  ' '

                       ||  ip.payee_city

                       ||  ' '

                       ||  ip.payee_state

                       ||  ' '

                       ||  ip.payee_postal_code tp_address

                     ,ip.payee_country

                     ,ac.address_line1

                       ||  ' '

                       ||  ac.city

                       ||  ' '

                       ||  ac.state

                       ||  ' '

                       ||  ac.zip

                       ||  ' '

                       ||  ac.province payment_address

                     ,ip.payment_process_request_name

                     ,ip.payment_status

                     ,ac.base_amount functional_amount

                  FROM

                      apps.iby_payments_all ip

                      JOIN

                          apps.ap_checks_all ac

                      ON

                          ip.payment_id = ac.payment_id

                      JOIN

                          apps.ap_suppliers s

                      ON

                          ac.vendor_id = s.vendor_id

                      LEFT JOIN

                          apps.ap_lookup_codes alc1

                      ON

                          ac.payment_type_flag = alc1.lookup_code

                      AND

                          alc1.lookup_type = 'PAYMENT TYPE'

                      LEFT JOIN

                          apps.ce_payment_documents pd

                      ON

                          ac.payment_document_id = pd.payment_document_id

                  WHERE

                      ip.payment_date BETWEEN v_start_date AND v_end_date

                  AND

                      ip.paper_document_number BETWEEN nvl(p_doc_from,ip.paper_document_number) AND nvl(p_doc_to,ip.paper_document_number)

                  AND (

                          p_org_id IS NULL

                      OR

                          ip.org_id = p_org_id

                  ) AND

                      ip.int_bank_account_number = nvl(p_bank,ip.int_bank_account_number)

                  AND (

                          p_pay_method IS NULL

                      OR

                          ip.payment_method_code = p_pay_method

                  ) AND (

                          p_currency IS NULL

                      OR

                          ip.payment_currency_code = p_currency

                  );

                   

                  Perhaps a dedicated document how to use this custom format page is warranted. In a word, on this page you can specify formal grammar condition when to ident code fragment or not. I wonder if the same should be done for line breaks triggered by discovery of certain grammar constructs.