This has irked me for a long time. I’m using version 10, but have never had this working in previous versions. Can someone tell me if there is a way to format a sql query with a substitution variable in it? When formatting with an &, I get a syntax error, example:
/Commercial Waste RDF Graph Query/
SELECT financial_period_detail_mv.fiscal_qtr, SUM (port_waste_commercial.unit) waste
FROM whse.port_waste_commercial, whse.financial_period_detail_mv
WHERE financial_period_detail_mv.fiscal_month = port_waste_commercial.fiscal_month
AND financial_period_detail_mv.month_end = ‘Y’
/Last 8 Quarters/
AND financial_period_detail_mv.actual_day BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -24))
AND TRUNC (SYSDATE)
&cp_graph_where
–AND port_waste_commercial.unit IN (‘Technical Fabrics US’, ‘PTL’, ‘Saint Clair’)
group by financial_period_detail_mv.fiscal_qtr
order by financial_period_detail_mv.fiscal_qtr;
If you can make the statement * look * like a normal statement, apart from the
use of the ampersand, then it can be formatted.
If you could write
&cp_graph_where
rather as
AND ( &cp_graph_where )
then &cp_graph_where will look like a boolean and the formatter will process it
as a boolean.
The intrinsic problem with substitution variables is that they can stand for
anything and they can be placed about anywhere (but near the head). For
instance,
SELECT &abc
is a valid statement if you replace abc by something decent. The formatter has
to “understand” the statement before it can format it.
Andre, thanks for the response. What you are saying does make sense. For our purposes, the solution will not work as we have several hundred reports using queries formatted the original way. If I could just make TOAD treat the &variable as a comment for formatting purposes only, I think that would solve our problem.
Bob
Just a comment. Automatically formatting any piece of code causes some procedure to be applied to it which physically changes it. This procedure has the possibility of causing errors.
I would never trust an automatic formatting procedure to handle code that has been tested.
Yes, I can see banging away at the keyboard and afterwards formatting it so it looks good and follows standards. I would do that too. But once I have my code done and tested I would never reformat it again. I would just make changes and add spaces etc. Myself. If there was a big change in the middle I would highlight that and format it, but I would never format the whole because then I have to look at it and do a complete test of everything.
In your case you could make the & variable a comment, format the code and then uncomment.
Erwin, I appreciate your comments. The suggestion of commenting out the variable, formatting, and then commenting it back in, is exactly how we have handled this issue for the past eight years. It may seem trivial, but the time it takes to do this adds up. Again, if the substitution variable could be treated as a line comment during formatting, the problem would be solved. I’m not sure how your comments on formatting code and testing apply here. I assume most developers, including myself aren’t in the practice of not testing code once it has been changed.
Thanks,
Bob
But if you make a minor change in a program, are you going to test all possibilities or just the change you have made? That is, suppose you have a package with many procedures and you only change one procedure. Are you going to test all procedures or just the one changed? But if you reformat the entire package then you would have to test all procedures since they have all been possibly changed.
Ahh, the beauty of Code Tester, our automated unit testing suite for PL/SQL. And now with Toad, it’s a one-button click to regression test ALL of your code. Of course you still have to spend some time defining your tests…