looking for guidance on Oracle Sql Formatting standards

I'm looking for some general guidance on Sql formatting, in PL-SQL, views, etc.

Here in our Nazi-driven shop, the standards are, shall we say, "exacting". I'd like something in between - standardized, readable, but not to the point of straightjackets.

One of my primary arguments for compact code is that IT SAVES TIME from scrolling up and down ALL DAY LONG. PLUS - it increases comprehension, productivity, etc. and MOST OF TIME TIME you're not really focusing on the columns, you're focusing on the logic (WHERE ...)

So I'm looking for standards but also the *reason* for the standards.

In our shop it's one line for SELECT, FROM, WHERE, ORDER BY, etc. That drives me nuts, because it's unnecessary, in my (not so) humble opinion. I can't see any good reason for it. When you're looking at code ALL DAY LONG, it doesn't help to put the keywords ON THEIR OWN LINE. The eye/brain is quite capable of processing these words which are ALWAYS ON THE LEFT and then if your other code is reasonably formatted, you save time and aggravation.

Also, I find it unnecessary to put EVERY COLUMN in the select on its own line, again because it takes up space and causes unnecessary scrolling up and down, and loss of "context" of the code.

Also, on simple case statements, it seems reasonable to put on one line, e.g.,
case when ac.Embark_Dt is not null then ac.Embark_Dt else ay.start_date end as Embark_Dt,

Any developer beyond the newbie stage can read this without much difficulty at all.

Also, thoughts on UPPERCASE vs lower, mixed case. Again, I fail to see why keywords in UPPERCASE help all that much. With tools like PL-SQL Developer, they're highlighted anyway, plus anyone past the newbie stage KNOWS WHATS GOING ON.

I think Right-Align is goofy too, although I do like alignment. for example,
  SELECT   last_name, first_name
    FROM   employee
WHERE     department_id = 15
     AND     hire_date < SYSDATE;

I think is better as left-aligned
SELECT     last_name, first_name
FROM       employee
WHERE     department_id = 15
AND          hire_date < SYSDATE;

minor difference but to me your brain skips by those words anyway, they don't need to be thrown in your face as you're reading the logic.

Here's some compact code which to me is better, because MOST OF THE TIME you're really not scanning the columns, you're looking at the WHERE logic. So, to me, it's a WASTE OF TIME scrolling up and down all day long, plus you miss "seeing the picture" because of the incessant scrolling.

COMPACT CODE
select owner c1, object_type c3, object_name c2
from dba_objects
where status != 'VALID'
order by owner, object_type;

much better than the Ask Tom long, drawn out code
select
         owner c1,
         object_type c3,
         object_name c2
from
         dba_objects
where
          status != 'VALID'
order by
           owner,
           object_type;

if you have 20+ columns, you're wasting a lot of space and scrolling as opposed to thinking.
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
Nowadays, many/most IDEs (like PL/SQL Developer from Allround Automations) have some sort of "Beautifier" or "Formatter" built-in, which can be customized to whatever granularity ;-)
In the end, I think it depends (as always) on your needs, setup, restrictions, (development) rules and so on. It's far more important that all dev members commit themselves to just ONE way of formatting (and thus coding, e.g. order of tables, join rules etc...). So, everyone is able to (easily) "read" the others' code (and SQL)...
1
slightwv (䄆 Netminder) Commented:
I use indentation for logical grouping when using nested selects and/or nested functions.  

For example, using XMLELEMENT and the rest of the XML functions to generate XML from several columns/tables.  If you properly indent them, you can easily see missing or mismatched parenthesis.


For complex SQL from this question:
https://www.experts-exchange.com/questions/29013773/Shortest-length-recursive-path-to-fetch-all-rows-oracle-SQL-PLSQL.html

This runs but good luck trying to understand it:
WITH BAYS_BOTH_WAY AS ( SELECT STRUCTURE1, STRUCTURE2, LENGTHH ,BAYID FROM GET_BAYID_TMP WHERE NETWORKTYPE='HV' UNION ALL SELECT STRUCTURE2, STRUCTURE1, LENGTHH,BAYID FROM GET_BAYID_TMP WHERE NETWORKTYPE='HV'), PATHS (NODE, STRUCTURES_PATH,BAYID, SHORTEST_DISTANCE, RNK, LEVL) AS (SELECT A.STRUCTURE2, A.STRUCTURE1 || '->' || A.STRUCTURE2, A.BAYID, A.LENGTHH, 1, 1 FROM BAYS_BOTH_WAY A WHERE A.STRUCTURE1= 'P001' UNION ALL SELECT A.STRUCTURE2, P.STRUCTURES_PATH || '->' || A.STRUCTURE2, P.BAYID || '->' ||A.BAYID BAYID, P.SHORTEST_DISTANCE + A.LENGTHH, RANK () OVER (PARTITION BY A.STRUCTURE2 ORDER BY P.SHORTEST_DISTANCE + A.LENGTHH), P.LEVL+1 FROM PATHS P JOIN BAYS_BOTH_WAY A ON A.STRUCTURE1 = P.NODE AND P.RNK=1) CYCLE NODE SET IS_CYCLE TO '1' DEFAULT '0' , PATHS_RANKED AS (SELECT LEVL, NODE, STRUCTURES_PATH, BAYID, SHORTEST_DISTANCE, RANK () OVER (PARTITION BY NODE ORDER BY SHORTEST_DISTANCE) RNK_TOT FROM PATHS WHERE RNK = 1 AND NODE='P009') SELECT LEVL, STRUCTURES_PATH, BAYID BAYIDS_PATH, SHORTEST_DISTANCE SHORTEST_DIST_IN_MTRS FROM PATHS_RANKED WHERE RNK_TOT = 1 ; 

Open in new window


Then add in comments and it gets even worse.
1
Gadsden ConsultingIT SpecialistAuthor Commented:
Ok, thank you Alexander and slightwv.

Specific question, I really don't see the point of putting everyone column on its own row. Once the code is written, the columns become less important as far as readability is concerned. To me it's like writing
like
this.
what's
the
point?

plus it keeps it nice and compact, so then you're thinking about the logic and not scrolling up and down all day.

What do you think?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
Individual columns, I tend to place multiple per line until they may wrap in my editor window.

I do indent subsequent lines to try and keep keywords left of columns.

I also try to keep the same number of columns and INTO variables on individual lines.  If I am selecting 10 columns into 10 variables, if I have the columns like 6 on the first line and 4 on the next, my INTO variables are also the same 6 and 4.

I do try to keep my where clauses one per line.

Here is something like that my SQL looks like(for the purists out there:  yes, I know there is a syntax issue with it):
select col1, col2 col3,
	col4, col5
into v_col1, v_col2, v_col3,
	v_col4, v_col5
from (
	select col6, col7, col8, col9, col10
	from some_table
	where something=something and
		somethingelse=somethingelse and
		etc=1
	)
where 1=1
/

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gadsden ConsultingIT SpecialistAuthor Commented:
very good, slightwv, thanks. I like that approach.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
our code SWAT team will turn back code because there are three spaces for indentation as opposed to the required four. Numerous good people have moved on as a result of this.
0
slightwv (䄆 Netminder) Commented:
>>our code SWAT team will turn back code

That is a little OCD but I suppose rulz iz rulz!

Personally I HATE space indentation.  I thought that was what tab stops were invented for!

>>Numerous good people have moved on as a result of this

I can imagine!

I remember back in the college days one teachers aid that graded the instructors code always complained if code wasn't commented enough.

One "enterprising" student had enough and turned in a program that had ZERO white space and if memory serves, was fixed width. so everything was a SOLID block of characters.  He said it was worth the rewrite he had to do!!!
0
slightwv (䄆 Netminder) Commented:
Decided to play using SQL.

Give them this:
/*Hello*/select/*This*//*will*//*actually*/sysdate/*run*//*from*/
/*sqlplus*/from/*on*//*my*//*setup*/dual/*that*//*should*//*teach*/
/*them!*/;

Open in new window

0
Gadsden ConsultingIT SpecialistAuthor Commented:
slightwv - - - ha ! good ones.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
FYI - still irritated that columns are required one per line. Reading comprehension is left to right, can't really see any benefit with this approach.

I saw a comment yesterday about writing procedures, packages, etc., how it's nice to "package" up small, similar snippets of code for ease in maintainability. The author said he always has the (logical) begin/end of a code block within the size of his monitor.

Compare what I think is readable code to what I'm required to produce;

<my view of readable code - compact but readable, and can be looked at with one glance>
    CURSOR c_rotc_basic is
       select m.id as student_Id, m.student_num, m.last_name, m.first_name, substr(m.middle_name,1,1) as MI,
              case when m.pref_name = m.first_name then null else m.pref_name end as Preferred_Name, m.formatted_name,
              m.gen as Gender, m.class_applied_for as Class_Yr, ru_pkg.STUDENT_CLASS(m.id) as Class, m.itl as Intl,
              lpad(to_char(m.rotc_co_nbr),2,'0') as Co, ms.title as Status, m.rotc_in_brig as In_Brigade, m.proj_grad_date, m.grad_date,
              m.rotc_commission_date, m.rotc_commission_title,
              trunc(months_between(sysdate,m.birth_date)/12) as Student_Age, m.adas_id, m.high_school_source,
              m.high_satv, m.high_satm, m.commission_legal_code, m.former_mil_svc_code
        from rotc_students m
             inner join mid_statuses ms on ms.code = m.mist_code
        where m.mist_in_brig = 'Y';

<the required rigid standard means you're scrolling up and down (and up and down) and not really able to focus on the actual logic, imo>
    CURSOR c_rotc_basic is
       SELECT
              m.id as student_Id,
              m.student_num,
              m.last_name,
              m.first_name,
              substr(m.middle_name,1,1) as MI,
              CASE
                     when m.pref_name = m.first_name
                                then null
                     else m.pref_name
             END as Preferred_Name,
              m.formatted_name,
              m.gen as Gender,
              m.class_applied_for as Class_Yr,
              ru_pkg.STUDENT_CLASS(m.id) as Class,
              m.itl as Intl,
              lpad(to_char(m.rotc_co_nbr),2,'0') as Co,
              ms.title as Status,
              m.rotc_in_brig as In_Brigade,
              m.proj_grad_date,
              m.grad_date,
              m.rotc_commission_date,
              m.rotc_commission_title,
              trunc(months_between(sysdate,m.birth_date)/12) as Student_Age,
              m.adas_id, m.high_school_source,
              m.high_satv,
              m.high_satm,
              m.commission_code,
              m.former_mil_svc_code
        from rotc_students m
             inner join mid_statuses ms on ms.code = m.mist_code
        where m.mist_in_brig = 'Y';

Plus, in regards to my recent question on lines of code, the rigid standard above results in a misleading LINE_COUNT when trying to gauge level of effort.
0
slightwv (䄆 Netminder) Commented:
I find the required format MUCH EASIER to follow.  I believe you will find my fellow DBA's/Developers will as well.

That is how I write my code and how most SQL beautifiers will generate things as well.

When columns are mixed and matched it is really confusing.  What is a column, what is a function, what is a string literal is all jumbled up.

>>the rigid standard above results in a misleading LINE_COUNT when trying to gauge level of effort.

I can make a 1,000 procedure compile on a single line.  What is the level of effort to maintain it?

I already pointed out one reason why lines of code is a bad method:  Comments!

There are several others.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
ok ok ... :-(

but it seems like the scrolling up and down all day long isn't worth it

>>What is a column, what is a function, what is a string literal is all jumbled up.
- ok, then do a separate line when it's a function or sting manipulation or case statement. But one line for straight columns? Not buying that.

>>I can make a 1,000 procedure compile on a single line.
- understood, I'm not advocating that. Readability is key. With modifications to my code to put string manipulation etc on its separate line, I'd say it's readable.

>>one reason why lines of code is a bad method:  Comments!
- yes, agree, but I wouldn't say it's a bad measure, it's like sailing - it's one measure that has some value.

but thanks for the discussion!
0
slightwv (䄆 Netminder) Commented:
>>but thanks for the discussion!

Any time!  We're always here.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.