Earlier quoted context omitted.
Sorry for the confusion. When my list of columns is long, I split it up into multiple lines, usually 7-8 per line (where I work, column names are capped at 30 chars, hence the 200 character estimate), unless it is a case statement. In case statements, each case/when clause get its own line, so you'll have: case when condition then result \ when condition_2 then result_2 \ ...\ when condition_n then result_n end as co…
My personal experience when I tried to clump things together is that I lost track of what columns were where. My eyes scan vertically really fast. And once I was used to it for large queries, well, what works for large queries works for small as well if you're used to it. Honestly I don't think about formatting that much anymore, as I've started storing most of my statements as "metrics" which can be easily repurpose…
I work mainly in MySQL and Teradata (which has tmp tables which are called volatile tables), and I do exactly what you describe when creating complex queries. My metric system is just a way to build those temp tables more rapidly.
I use two main functions to manipulate metrics:
create_metric(metric_name,{cols_added},join_src,{join_cols},{extra_sql},{indices});
add_metric(metric_name, my_other_table, {my_join_conditions});
CREATE VOLATILE MULTISET TABLE add_metric_name AS (
SELECT a.*, b.cols_added_1, b.cols_added_2,...,b.cols_added_n
FROM my_other_table a
LEFT JOIN join_src b
ON a.my_join_condition_1 = b.join_col_1
AND a.my_join_condition_2 = b.join_col_2
...
AND a.my_join_condition_n = b.join_col_n
[If there is extra sql, like where a.condition = X, or group by's like group by 1, 2, it would show up here. SQL here can reference the join columns and table name in an add_metric stmt]
) WITH DATA PRIMARY INDEX(indice_1, indice_2) ON COMMIT PRESERVE ROWS;
It also allows me to search all of my metrics on a number of different dimensions: the common name of the information I am adding (metric name), column names, tables, join conditions (particularly useful - It helps you map how you'll get from one metric to another), indices, or any combination of the above. For example I can find all metrics that have the word phone in the metric name and are joinable on user_id.
I'm aware of Oracle's lack of tmp tables. My fiancée has to use Oracle SQL at work, and I quickly discovered its lack of tmp tables when trying to help her solve a SQL issue.