Right align code by pattern

Y

You may have noticed I like to use a lot of horizontal spacing in my code. Aligning code to multiple colums for better readability. It doesn't slow you down when you get used to it and it will save you a lot of time in a long run. You spend much more time on reading code then writing it so it is a good investment.

Example of well formatted code (subjective):

CREATE OR REPLACE VIEW p200_projects AS
WITH t AS (
    SELECT
        t.project_id,
        COUNT(*)                                                    AS tasks,
        SUM(CASE WHEN t.status = 'READY'        THEN 1 ELSE 0 END)  AS tasks_ready,
        SUM(CASE WHEN t.status = 'IN-PROGRESS'  THEN 1 ELSE 0 END)  AS tasks_in_progress,
        SUM(CASE WHEN t.status = 'COMPLETE'     THEN 1 ELSE 0 END)  AS tasks_complete,
        SUM(CASE WHEN t.resource_id IS NOT NULL THEN 1 ELSE 0 END)  AS resources
    FROM tasks t
    LEFT JOIN sprints s
        ON s.sprint_id              = t.sprint_id
        AND s.is_active             = 'Y'
    GROUP BY t.project_id
)
SELECT
    p.*,
    NVL(t.tasks, 0)                 AS tasks,
    NVL(t.tasks_ready, 0)           AS tasks_ready,
    NVL(t.tasks_in_progress, 0)     AS tasks_in_progress,
    NVL(t.tasks_complete, 0)        AS tasks_complete,
    NVL(t.resources, 0)             AS resources
FROM projects p
LEFT JOIN t
    ON t.project_id                 = p.project_id;

To help you reformat existing blocks you can use this script:

WITH t AS (
    SELECT '
        fn.table_name AS foreign_table,
        fc.column_name AS foreign_column,
        pc.table_name AS parent_table,
        pc.column_name AS parent_column,
        DECODE(rc.position, NULL, ''Y'', ''N'') AS is_root
        ' AS text,
        --
        '([=<>:!]|\sIN\s|\sIS\s|\sNOT\s|\sAS\s)'    AS pattern,
        --'([;]+)'                                  AS pattern,  -- split by pattern
        --'(\s*\W+)'                                AS pattern,  -- split by words (and occurence)
        1                                           AS start_at,
        1                                           AS occurence
    FROM DUAL
),
s AS (
    -- split string to lines
    SELECT DISTINCT
        l.COLUMN_VALUE AS line,
        REGEXP_SUBSTR(t.text, '[^' || CHR(10) || ']+', 1, l.COLUMN_VALUE) AS text,
        t.pattern,
        t.start_at,
        t.occurence
    FROM t, TABLE(CAST(MULTISET(
        SELECT LEVEL FROM DUAL
        CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(t.text, '[^' || CHR(10) || ']+')) + 1
    ) AS SYS.ODCINUMBERLIST)) l
),
x AS (
    -- split lines by selected pattern
    SELECT s.*,
        REGEXP_INSTR(s.text, s.pattern, s.start_at, s.occurence)                                                    AS split,
        RTRIM(SUBSTR(s.text, 1, REGEXP_INSTR(s.text, s.pattern, s.start_at, s.occurence) - 1))                      AS text_left,
        SUBSTR(s.text, REGEXP_INSTR(s.text, s.pattern, s.start_at, s.occurence))                                    AS text_right,
        MAX(LENGTH(RTRIM(SUBSTR(s.text, 1, REGEXP_INSTR(s.text, s.pattern, s.start_at, s.occurence) - 1)))) OVER () AS rpad
    FROM s
)
SELECT
    s.line,
    RTRIM(NVL(RPAD(x.text_left, CEIL((x.rpad + 1) / 4) * 4) || LTRIM(x.text_right), s.text)) AS text
FROM s
LEFT JOIN x
    ON x.line           = s.line
    AND NVL(x.split, 0) > 0
ORDER BY 1;