![]() INNER JOIN student_history AS sh USING (student_id) WHERE e.registration_code NOT IN ('A7', 'D0', 'WL') INNER JOIN program_enrollment AS pe ON e.student_id = pe.student_id AND e.term = pe.term AND e.offering_id = pe.offering_id INNER JOIN offering AS o USING (term, offering_id) WHEN NVL(o.weeks, 0) > 5 THEN (NVL(o.lect_hours, 0) + NVL(o.lab_hours, 0) + NVL(o.ext_hours, 0)) / NVL(o.weeks, 0) SELECT e.term, e.student_id, NVL(o.credits, 0) credits, SUM(credits) AS credits, SUM(hours_per_week) AS hours_per_week Pm.credits AS ft_credits, pm.hours AS ft_hours_per_week, WHEN ((ft_credits > 0 AND credits >= ft_credits) OR (ft_hours_per_week > 3 AND hours_per_week >= ft_hours_per_week)) THEN 'F' (It's also been altered to disguise its origins, and I may have introduced errors in so doing.) SELECT term, student_id, Pretty much everything you'd encounter in a SELECT statement shows up in this one. I'll finish with one of my more complex creations of late using this formatting style. Indenting after wrapped INNER JOIN lines is also somewhat arbitrary.īut for whatever reason, I still find it easier to read than the alternatives. It would also be more natural to indent the entire predicate of the WHERE clause, but I usually align following AND and OR operators at the left margin. The 8-space indent means that ORDER BY and GROUP BY either misalign the indent, or split the word BY off by itself. ![]() VALUES let me be the first to admit that this style has it's problems. SET column_name_1 = condition_1 = the INSERT: INSERT INTO target_table (column_name_1, column_name_2, In short: 8-space indentation, keywords in caps (although SO colours them better when in lowercase), no camelcase (pointless on Oracle), and line wraps when needed. INNER JOIN second_join_table AS sjt ON st.source_table_id = sjt.source_table_id INNER JOIN join_table AS jt USING (source_table_id) Here's the sample SELECT statement: SELECT st.column_name_1, jt.column_name_2, I'm late to the party, but I'll just add my preferred formatting style, which I must've learned from books and manuals: it's compact. I'm constantly amazed how many developers can follow standard formatting for other languages and suddenly go so random when hitting SQL. I look forward to seeing whether there is any consensus in the Stack Overflow community on this topic. ) values the most part, these don't deviate that far from the way MS SQL Server Managements Studio / query analyser write out SQL, however they do differ. updateĬolumnName1 = an insert: insert into TargetTable ( Similarly, a linefeed after group by or order by keeps our column layouts clear and easy to read. However, in queries such as the update below, we see that the line feed after the where gives us good column alignment. ![]() Following on from that, simply keeping a consistent line feed after the key query elements seemed to result in a good level of readability.ĭropping the linefeed after the from and where would be an understandable revision. The intention on the select line is to allow other operators such as "top X" without altering the layout. There was some disagreement about line feeds after select, from and where. Unlike most other coding environments, I haven't found much of a consensus online for them. I'm interested to know what other people use for SQL formatting standards. We also developed coding standards, but these are more platform-specific so I'll not go into them here. In my last job, we worked on a very database-heavy application, and I developed some formatting standards so that we would all write SQL with a common layout.
0 Comments
Leave a Reply. |