SQL View script formatting

[Bob Cergol]

I am probably the "odd man out" among those far more experienced with writing SQL scripts, and am not well acquainted with "standard" formatting practices for written SQL scripts.
But instead of seeing a script generated as this very hard to read blob of text:

Build_SQL_View.gif (24.88 KiB) Viewed 3632 times

I would much rather see this so I can very easily see what I need to eliminate, re-order, etc.

Build_SQL_View_2.gif (17.06 KiB) Viewed 3632 times

I'll bet its a trivial thing to change in DAI, but many users might complain -- and I guess once views are created in a new system, one rarely creates new ones.

While on the subject, what is your advice regarding placing field name aliases within the script versus using DAI dictionary captions. Most of the time I want to preserve original field names -- because I know them by heart -- and would use the dictionary to create a user-friendly caption, but for mostly legacy reasons, many field names are meaningless, wrong, or have been "re-purposed", and I'd like to change the name. (I'm more reluctant to change the actual SQL column name in the database itself.)

Bob

[Carsten Sørensen]

Hi Bob,

Thanks for the suggestion!

I can agree to your point of view - for elimination, re-ordering putting a field on each line is nicer. I personally like the compressed view though - as I can see more complete views on the screen at the same time. I guess there is no general rule to this. Other tools implement a Format feature - to reformat the content. Maybe we need a Format feature as well for the source itself and not only for viewing (as we already have).

As for field-elimination in itself and re-ordering. This is not - in general - necessary for building reports or working with Dynamic AI. I agree that having nice well-structured data-sources are the basis for any good implementation and best-practices behavior - however in many cases you just ends-up having to add fields to the views from time to time - and this would at least in some cases not be necessary when using the full content of the sources. IMO newer DBMS servers are smart enough to pick only the needed columns from underlying views / selections - and I'm rather sure that the impact of having even 100's of columns in Dynamic AI reports is minor to the performance.

We'll think about the formatting.

Regarding changing field name alias/captions already at view level - is ok as well as using the dictionary. I guess the rule is that the source-fieldname should mean something to someone otherwise it is better to change it as it will appear in available columns, be part of formula's etc.

The benefit of modifying in the view is that you only have to do it in that one place. For many reports - using the join-builder or the database table or views - is sufficient - that's why in those cases the dictionary will serve as the placeholder for alias/captions.

Best regards,
Carsten