How to Format a SQL String into a Readable Block of Code That Is Well Formatted and More Easy to Read

How to format a SQL string into a readable block of code that is well formatted and more easy to read

I have used this technique in the past, is this the best method of doing this?

The Key to it is:

A. using the following "aa" procedure which make reading so much easier.

B. If you build the SQL string using a query builder (like in MS Access), it is good to configure a utility to take the built SQL and reformat it so that the resulting SQL looks pretty much exactly like that below. (I use www.sqlinform.com but there are others, or you can do it manually)

C. By adding vbCrLf beofre each line, when the SQL string is built in VBA the resulting string can be output to the immediate window an easily read as it will have line brakes and line up nicely. (It's not rocket science)

Public Sub aa(ByRef a As String, ByVal b As String)
    ' This is deliberately not a function, although it behaves a bit like one
    a = a & vbCrLf & b

End Sub

' The function is called in code like this:

Dim a as string 

a = ""
aa a, "    SELECT CUR.txtLevel      AS [Current]  "
aa a, "         , NLPMi.strFullName AS [Full Name]  "
aa a, "         , NLPMi.DOB         AS [Birthday]  "
aa a, "         , NLPMi.Surname     AS [Surname Name]  "
aa a, "         , TOOLS.txtWCMTool "

aa a, "      FROM ( ( ( tblPeopleWCMSKILLSByYear AS SKILLS"
aa a, "                 LEFT JOIN tblSkillLevels AS CUR  "
aa a, "                        ON SKILLS.bytCurrentID = CUR.atnSkillLevelID
aa a, "               ) "
aa a, "              INNER JOIN [qrylstNames-LPMi] AS NLPMi  "
aa a, "                      ON SKILLS.intPeopleID = NLPMi.atnPeopleRecID
aa a, "             )"
aa a, "            INNER JOIN tblWCMTools AS TOOLS "
aa a, "                    ON SKILLS.intWCMSkillID = TOOLS.atnWCMToolID"
aa a, "           ) "

aa a, "     WHERE ( ( (SKILLS.bytYearID) = YEAR(DATE())-2012 )  "
aa a, "           AND CUR.txtLevel   >= " & MyVariable1 & "  "
aa a, "           AND TOOLS.txtWCMTool = '" & MyVariable2 & "'"
aa a, "           )  "

aa a, "  ORDER BY NLPMi.strFullName"
aa a, "        ", " & MyVariable3 & ""

aa a, "  ;"

Note that:

1. the brackets for the table joins line up

2. I use uppercase aliases to re-inforce they are aliases and so that they stand out

3. Sometimes I add spaces to separate the key areas (SELECT, WHERE, GROUP BY, FROM etc), usually when it is along bit of code and possible is very dynamic (ie when it has a lot of variables that change how it is built )

4. In the SELECT, ORDER BY and GROUP BY clauses, I favour putting the commas that are needed between columns in front of the column name on the same line.

5. I don't like database tables have the type in their name ie strFullName. However, this is not my schema!

Harvey

Python SQL query string formatting

Sorry for posting to such an old thread -- but as someone who also shares a passion for pythonic 'best', I thought I'd share our solution.

The solution is to build SQL statements using python's String Literal Concatenation (http://docs.python.org/), which could be qualified a somewhere between Option 2 and Option 4

Code Sample:

sql = ("SELECT field1, field2, field3, field4 "
       "FROM table "
       "WHERE condition1=1 "
       "AND condition2=2;")

Works as well with f-strings:

fields = "field1, field2, field3, field4"
table = "table"
conditions = "condition1=1 AND condition2=2"

sql = (f"SELECT {fields} "
       f"FROM {table} "
       f"WHERE {conditions};")

Pros:

1. It retains the pythonic 'well tabulated' format, but does not add extraneous space characters (which pollutes logging).
2. It avoids the backslash continuation ugliness of Option 4, which makes it difficult to add statements (not to mention white-space blindness).
3. And further, it's really simple to expand the statement in VIM (just position the cursor to the insert point, and press SHIFT-O to open a new line).

Formatting Clear and readable SQL queries

With large queries I tend to rely a lot on named result sets using WITH. This allows to define the result set beforehand and it makes the main query simpler. Named results sets may help to make the query plan more efficient as well e.g. postgres stores the result set in a temporary table.

Example:

WITH 
  cubed_data AS (
     SELECT 
        dimension1_id,
        dimension2_id,
        dimension3_id,
        measure_id,
        SUM(value) value
     FROM
        source_data
     GROUP BY
        CUBE(dimension1, dimension2, dimension3),
        measure
  ), 
  dimension1_label AS(
     SELECT 
        dimension1_id,
        dimension1_label
     FROM 
        labels 
     WHERE 
        object = 'dimension1'
  ), ...
SELECT 
  *
FROM  
  cubed_data
  JOIN dimension1_label USING (dimension1_id)
  JOIN dimension2_label USING (dimension2_id)
  JOIN dimension3_label USING (dimension3_id)
  JOIN measure_label USING (measure_id)

The example is a bit contrived but I hope it shows the increase in clarity compared to inline subqueries. Named result sets have been a great help for me when I've been preparing data for OLAP use. Named results sets are also must if you have/want to create recursive queries.

WITH works at least on current versions of Postgres, Oracle and SQL Server

SQL formatting standards

I am of the opinion that so long as you can read the source code easily, the formatting is secondary. So long as this objective is achieved, there are a number of good layout styles that can be adopted.

The only other aspect that is important to me is that whatever coding layout/style you choose to adopt in your shop, ensure that it is consistently used by all coders.

Just for your reference, here is how I would present the example you provided, just my layout preference. Of particular note, the ON clause is on the same line as the join, only the primary join condition is listed in the join (i.e. the key match) and other conditions are moved to the where clause.

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT on 
    JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT on 
    ST.SourceTableID = SJT.SourceTableID
where
        ST.SourceTableID = X
    and JT.ColumnName3 = Y
    and JT.Column3 = SJT.Column4

One tip, get yourself a copy of SQL Prompt from Red Gate. You can customise the tool to use your desired layout preferences, and then the coders in your shop can all use it to ensure the same coding standards are being adopted by everyone.

Converting a function from SQL Server to MS Access

I'm interested to know why you are converting this to be in access VBA.

I would build the query SQL as a string in VBA and then create a QueryDef object from the SQL string.

When building the SQL string you will find the technique outlined in this answer helps you.

Some more practical points show in the SQL below:

Remove the cartesian join, explicitly in your code as below.

All column aliases and table aliases need an "AS"

Instead of IIF you could use

nz(MyFieldWithNulls, AnotherfieldToBeUsedWhenItisNull)

Replace datediff as shown

SELECT Nz(i.MarkDate, r.MarkDate) as MarkDate
     , a.MaturityDate
     , Nz(i.ZeroRate 
        , r.startRate + Cint(a.MaturityDate - r.begindate) * rateChange
         ) as ZeroRate
    , i.*
    , r.*
FROM (  ( SELECT allDates.* 
            FROM allDates   
                 INNER JOIN (SELECT MIN(MaturityDate) AS minDate
                                  , MAX(MaturityDate) AS maxDate
                               FROM Inter
                            ) AS Inter
                         ON ( allDates.MaturityDate >= Inter.minDate
                              AND 
                              allDates.MaturityDate <= Inter.maxDate
                            )
        ) AS a
       LEFT JOIN Inter AS I 
              ON a.MaturityDate  = I.MaturityDate
     )
     LEFT JOIN ( SELECT , I1.id
                        , I1.[MarkDate] 
                        , I1.[MaturityDate] AS begindate
                        , I2.[MaturityDate] AS enddate, 
                        , I1.[ZeroRate]     AS startRate,
                        , (I2.ZeroRate - I1.ZeroRate) * 1.0 
                          / DATEDIFF ( day , I1.[MaturityDate], I2.[MaturityDate] )  
                          AS rateChange        
                   FROM Inter I1
                        INNER JOIN Inter I2 
                                ON I1.id = (I2.id - 1)
                ) AS r 
            ON ( a.MaturityDate > r.[begindate]
                 AND 
                 a.MaturityDate < r.[enddate] 
               )     
    ;

Cleanest way to build an SQL string in Java

First of all consider using query parameters in prepared statements:

PreparedStatement stm = c.prepareStatement("UPDATE user_table SET name=? WHERE id=?");
stm.setString(1, "the name");
stm.setInt(2, 345);
stm.executeUpdate();

The other thing that can be done is to keep all queries in properties file. For example
in a queries.properties file can place the above query:

update_query=UPDATE user_table SET name=? WHERE id=?

Then with the help of a simple utility class:

public class Queries {

    private static final String propFileName = "queries.properties";
    private static Properties props;

    public static Properties getQueries() throws SQLException {
        InputStream is = 
            Queries.class.getResourceAsStream("/" + propFileName);
        if (is == null){
            throw new SQLException("Unable to load property file: " + propFileName);
        }
        //singleton
        if(props == null){
            props = new Properties();
            try {
                props.load(is);
            } catch (IOException e) {
                throw new SQLException("Unable to load property file: " + propFileName + "\n" + e.getMessage());
            }           
        }
        return props;
    }

    public static String getQuery(String query) throws SQLException{
        return getQueries().getProperty(query);
    }

}

you might use your queries as follows:

PreparedStatement stm = c.prepareStatement(Queries.getQuery("update_query"));

This is a rather simple solution, but works well.

Is there any way I can write (copy-paste) nicely-formatted SQL queries in Java string literals using Eclipse?

In Eclipse, in Window > Preferences under Java > Editor > Typing check the "Escape text when pasting into a string literal" checkbox.

It will format the plain text:

line1
line2
line3

to:

private final String TEXT = "line1\r\n" + 
            "line2\r\n" + 
            "line3";

This is the output on Eclipse Helios.
A similar question: Surround with quotation marks

Hints to determine which combo boxes were selected?

Dim where_condtion as String
Dim sqlquery as String

where_condtion = ""

IF combo1 <>"" then
    where_condtion = where_condtion + "~fieldname~ = " & combo1
End IF
IF combo2 <>"" then
    where_condtion = where_condtion + "AND ~fieldname~ = " & combo2
End IF
*
*
*
IF combo8 <>"" then
    where_condtion = where_condtion + "AND ~fieldname~ =" & combo8
End IF

IF where_condtion <> "" then
    sqlquery = "Select * from ~table name~ where" + where_condtion
ELSE
    sqlquery = "Select * from ~table name~
End IF

sqlquery = Replace(sqlquery, "where AND ", "where ")

DoCmd.OpenQuery "sqlquery", acViewNormal, acEdit

OR

CurrentDb.OpenRecordset("sqlquery")

Algorithm for neatly indenting SQL statements (Python implementation would be nice)

You can try sqlparse. It's a Python module that provides simple SQL formatting. A online demo is available here.

---

Related Topics