SQL Nav 6.2 - formatter issue

SQL Navigator
1

I've got 2 queries which return about 65 columns. Both queries are the same except one returns two more columns and has an extra where clause.

Attached is the image of how SQL Nav formats them.

The only difference is 2 extra columns:
'TRUE' c98, (SELECT sf_id
FROM sf_contracts
WHERE procuri_id = p.agreement_link) c99

and extra where clause

 AND EXISTS (SELECT sf_id
               FROM sf_contracts
              WHERE procuri_id = p.agreement_link)

I tried to format them both in 5.5.4 and load into 6.2, but 6.2 formatter keeps breaking the second query.

package_spec.png
package_spec.png387×609 14.5 KB

2

Hi Alexey,

Would you mind to attach the second script or send it to me? I tried to make up the code from what I saw in the image plus the extra columns and extra where clause. But I couldn’t repeat the issue. I guess the comlexity of the script caused formatter error. Once I get your script and reproduce the problem, I will report this error and create CR for formatter team to fix.

Thanks,
Vincent

3

Attached are scripts formatted in 5.5.4

This is what breaks formatting in the change.sql
AND EXISTS (SELECT sf_id
FROM sf_contracts
WHERE procuri_id = p.agreement_link)

I tried to move it around but it had no effect until I commented it out.

Message was edited by: ashmelev
head.sql (6.64 KB)

4

Attached are scripts formatted in 5.5.4

This is what breaks formatting in the change.sql
AND EXISTS (SELECT sf_id
FROM sf_contracts
WHERE procuri_id = p.agreement_link)

I tried to move it around but it had no effect until I commented it out.

Message was edited by: ashmelev
change.sql (6.9 KB)

5

Here’s another script that 6.2 refuses to wrap:

SELECT d.sf_id “OPPORTUNITYID”, sfp.id pricebookentryid,
d.serviceunits * d.servicemonths “QUANTITY”,
d.serviceunitrate “LISTPRICE”, d.servicetotalcost “TOTALPRICE”,
(CASE WHEN d.servicemonths > 1 THEN d.servicemonths || ’ Month(s)’ ELSE ‘’ END) “DESCRIPTION”, d.serviceunits “NUM_UNITS__C”,
d.servicemonths “DURATION__C”
FROM (SELECT sfc.sf_id,
ps.service || (CASE WHEN ps.contractedtat IS NOT NULL THEN ’ - ’ || ps.contractedtat END) service, ps.serviceunits, ps.serviceunitrate,
ps.servicemonths, ps.servicetotalcost
FROM procuri p, sf_contracts sfc, procuri_service ps
WHERE EXISTS (SELECT sf_id
FROM sf_contracts
WHERE procuri_id = p.contract_id)
AND p.contract_id = sfc.procuri_id
AND p.cnt_id = ps.cnt_id) d, sf_products sfp
WHERE d.service = sfp.name(+)
ORDER BY 2

6

Thank you for all the feedbacks Alexey. I have created CR to fix this. I will let you know once we fix it.

Thanks,
Vincent

7

BTW, you can try the stacked option rather than wrapped for the select list. Tools | Formatter Tools | Formatting Options, then go to Formatter Options | Statements & Clauses | Other Lists pane. Select “Stacked on more than …” or “Stacked on Overflow or more than …” option for the SELECT / FETCH / RETURNING / INTO item. The stacked option works correctly.

Hope this helps,
Vincent