development:ohdsi_code_style_for_sql
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision | |||
|
development:ohdsi_code_style_for_sql [2018/02/19 09:10] schuemie |
development:ohdsi_code_style_for_sql [2020/04/06 13:40] (current) schuemie |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ====== OHDSI code style for SQL ====== | ====== OHDSI code style for SQL ====== | ||
| - | The OHDSI code style for SQL is heavily inspired by the [[http://architectshack.com/PoorMansTSqlFormatter.ashx|Poor Man's T-SQL Formatter]], which is available as a [[https://notepad-plus-plus.org/|NotePad++]] plug-in. The only difference with the default settings is that in OHDSI, commas are trailing. You can automatically format your SQL correctly by using the [[http://poorsql.com/|Poor Man's T-SQL Formatter Online Tool]] (but don't forget to set Trailing Commas). | + | This has moved here: https://ohdsi.github.io/MethodsLibrary/codeStyle.html#ohdsi_code_style_for_sql |
| - | + | ||
| - | + | ||
| - | ===== Case ===== | + | |
| - | Because several database platforms are case-insensitive and tend to convert table and field names to either uppercase (e.g. Oracle) or lowercase (e.g. PostgreSQL), we use snake_case. All names should be in lowercase. Reserved words should be in upper case. | + | |
| - | + | ||
| - | **Good** | + | |
| - | + | ||
| - | ''SELECT COUNT(*) AS person_count FROM person'' | + | |
| - | + | ||
| - | **Bad** | + | |
| - | + | ||
| - | ''SELECT COUNT(*) AS personCount FROM person'' | + | |
| - | + | ||
| - | ''SELECT COUNT(*) AS Person_Count FROM person'' | + | |
| - | + | ||
| - | ''SELECT COUNT(*) AS PERSON_COUNT FROM person'' | + | |
| - | + | ||
| - | ''select count(*) as person_count from person'' | + | |
| - | + | ||
| - | ===== Commas ===== | + | |
| - | Commas should be trailing. | + | |
| - | + | ||
| - | **Good** | + | |
| - | <code> | + | |
| - | SELECT COUNT(*) AS person_count, | + | |
| - | condition_concept_id, | + | |
| - | condition_type_concept_id | + | |
| - | FROM condition_era | + | |
| - | GROUP BY condition_concept_id, | + | |
| - | condition_type_concept_id | + | |
| - | </code> | + | |
| - | + | ||
| - | **Bad** | + | |
| - | <code> | + | |
| - | SELECT COUNT(*) AS person_count | + | |
| - | ,condition_concept_id | + | |
| - | ,condition_type_concept_id | + | |
| - | FROM condition_era | + | |
| - | GROUP BY condition_concept_id | + | |
| - | ,condition_type_concept_id | + | |
| - | </code> | + | |
| - | + | ||
| - | ===== Indentation and new lines ===== | + | |
| - | Indentation is done using tabs. Field definitions are followed by a new line. | + | |
| - | + | ||
| - | **Good** | + | |
| - | <code> | + | |
| - | SELECT COUNT(*) AS person_count, | + | |
| - | condition_type_concept_id | + | |
| - | FROM ( | + | |
| - | SELECT * | + | |
| - | FROM condition_era | + | |
| - | WHERE condition_concept_id = 123 | + | |
| - | ) tmp | + | |
| - | GROUP BY condition_type_concept_id; | + | |
| - | </code> | + | |
| - | + | ||
| - | **Bad** | + | |
| - | <code> | + | |
| - | SELECT COUNT(*) AS person_count, condition_type_concept_id | + | |
| - | FROM (SELECT * FROM condition_era WHERE condition_concept_id = 123) tmp | + | |
| - | GROUP BY condition_type_concept_id; | + | |
| - | </code> | + | |
development/ohdsi_code_style_for_sql.1519031454.txt.gz · Last modified: 2018/02/19 09:10 by schuemie
Page Tools
