A few years ago I started documenting the syntax differences between the main flavors of SQL. This, eventually, turned into the SQL Functions cheat sheet I have made available on my site (and still refer to regularly).

The main challenge I had creating the cheat sheet was that I didn’t have all these databases installed to experiment with. I had to ask volunteers who worked in specific environments to provide syntax examples for their flavor of SQL.

I was recently researching SQL syntax when a poster linked to a “SQL Fiddle” he had created.  SQL Fiddle is a site that allows you to ‘fiddle’ with SQL statements online, using a variety of common SQL languages. In many cases you can test statements using multiple versions of the same language. That led me to a handful of other sites that do the same thing with a different mix of languages. Here are three of the most complete ones I found, along with the languages they support:

http://sqlfiddle.com
SQL Server, Oracle, MySQL, Postgre, SQLLite

https://dbfiddle.uk
SQL Server, Oracle, MySQL, Postgre, MariaDB, SQLLite, DB2, Firebird, TimeScaleDB, YugabyteDB

https://sqlize.online
SQL Server, Oracle, MySQL, Postgre, MariaDB10, SOQOL

Note, these databases don’t always come with sample tables. You might need to run a statement to create the table(s) you want. Or you may be able to run your queries against the system tables.

I have been writing lots of SQL commands for Crystal Reports recently. One of them required me to link a sub query to itself, repeatedly, to get multiple levels. Fortunately I had recently read about Common Table Expressions (CTEs) which simplify SQL commands in cases like this.
A CTE allows you to write a query, give it a name, and then refer to it throughout your SQL command as if it were a table. You would start your command with something like this:

with trans as (
select Item, Date, Amount, CustName
from Items
inner join customers on Items.CustID = Customers.CustID
Where Items.Type = 3)

You put your subquery in the parens and Trans becomes the name of the results.
After that you start your SELECT and use Trans as if it were a table with those 4 columns.

This is especially helpful when you have to use the same results multiple times, because you don’t have to repeat the SQL like you normally would with a normal subquery. If you have to change that subquery you can make the change in one place, so the SQL is more efficient to write and read.

While CTEs make writing more efficient, they don’t make the SQL processing any more efficient. Each time you reference a CTE in your SQL it has to repeat the CTE query to get results. If the subquery is complex you want to have it processed only once and then be able to refer to the results several times without having the database repeat the query. For that you switch from a CTE to a Temp table.

With a temp table the results of the query are written to an actual table (in a tempDB). Temp tables are deleted when you are done with your DB session. And because temp tables are written to tempDB you don’t even need to have “write” permissions for the main database. You can even add an index to the temp table if it is large enough to need one.

With a temp table your SQL command can read that table as many times as necessary without having to generate the data again. This can make a significant difference in the processing time of a query, especially when you have to refer to those results multiple times. It is a good idea to start the SQL by checking to see if the temp table already exists and deleting it. Otherwise you will get an error when you try to create it.

Below is an example of a command that shows how to do the CTE above as a temp table. First it drops the temp table and then it recreates it:

if Object_ID (N'tempdb..#TempItems') IS NOT NULL
BEGIN
DROP TABLE #TempItems
END;

select Items.Item, Items.Date, Items.Amount, customers.CustName
Into #TempItems
from Items Items
inner join customers customers on Items.CustID = Customers.CustID
Where Items.Type = 3 ;

If you start your command with the SQL above you can follow that with a SELECT that references the table #TempItems (all temp tables start with #). You treat a temp table just like any other table. Note that there are semicolons after each step which we didn’t need with the CTE.

If you think your reports might benefit from these techniques, call to schedule a session.

This week I had to do something new in SQL.  I needed to take a list of multiple route records for each driver and append the routes into single string for that driver.  This is relatively simple in a Crystal report, but not so simple in a SQL Query.  After I realized how complex this would be in a normal SQL query I searched to see if there was a simpler way.  What I found was that several databases have a function specifically designed for this task.

In SQL Server and Postgre SQL it is called String_Agg().
In Oracle and DB2 it is called ListAgg().
In MySQL it is called Group_Concat().

Even though SQL is considered a “standard” language, some function names and syntax will vary from one database to another. That is why I created a “cheat sheet” a few years ago that lists the most common SQL functions (with their correct syntax) for the 8 SQL flavors I encounter the most:

• • • SQL Server
    • MySQL
    • Oracle
    • MS Access
    • Pervasive
    • Progress
    • PostgeSQL
    • DB2

Today I added these “aggregate” functions to the cheat sheet along with a few other updates.  The cheat sheet is now also available on the library page of my web site.

If you want your parameter’s list of values to be pulled from the database you can use a dynamic parameter. But one of the down sides of a dynamic parameter is that you can’t type additional values to include in the list.  A dynamic parameter can only show values pulled from the data source. So if, for instance, you are pulling in a list of products and you want the list to have an “All” option at the top, you can’t simply add the word “All” to the list like you could with a static parameter.

My preferred way for adding an “All” option to a dynamic parameter is to use a SQL command as the source for the dynamic parameter. Using a SQL command gives you several other advantages as well, such as allowing you to filter your the list of values. Here is an example of a SQL command that will add an “All” option to the list of values (incorporating suggestions from MHurwood below):

Select Items.ID, Items.Desc
From Items
Where Items.Status = ‘A’


UNION ALL


Select ‘…All’, ‘…All’

The part above the UNION creates a list of all the active items, showing both the ID and the description of the items. The part below the UNION adds one row to the results of the query with the “All” option. Notice that “All” entry has several periods in front of it. This is one way to sort that value to the top of the list. You can use this method to add several values to your dynamic list, if needed.

Note that you wan to avoid using the fields from this command in other parts of the report. It should be used only for the dynamic parameter.

One of my colleagues, Angela Meharg of Datisfy, reminded me that you can use optional parameters to do something similar.  Instead of explicitly selecting a word like “All”, you can skip over the parameter.  Then you can program the selection formula to say that when the users doesn’t select a value they get all values.  The formula would look something like this in the Crystal selection formula:

and (if not (HasValue({?Items})) then True else {Table.Item} = {?Items})

In English this means, if there are no values in the items parameter, then every record qualifies. Otherwise the items that qualify are the ones that match the parameter.

If you have trouble with one of these options, you can schedule a short consult and I can give you a hand.

I have written several articles about using Notepadd++ for writing long formulas or SQL statements. I just found out that one of the features I use in Notepad++ has been (partially) available in Crystal Reports forever.  I never noticed. The feature is called “column mode” and allows you to select text in a column without selecting the entire row.

Lets say you start a formula with DateTime variables like this:

WhilePrintingRecords;
DateTimeVar DateA;
DateTimeVar DateB;
DateTimeVar DateC;
DateTimeVar DateD;
DateTimeVar DateE;

Then you realize that you want them to be Date instead of DateTime. You can highlight a “column” made up of the word “Time” on all 5 rows at once, and then hit delete. To select a column you hold down the ALT key and then click the mouse in the upper left corner of rectangle and drag to the lower right corner. In this case you would start just before the “T” in Time in the first row, and drag down and across until your cursor was just after the “e” in Time in the last row. By using the ALT key you will highlight a rectangle of 4 characters across and 5 rows down. Then you hit delete and just those 20 characters are deleted.

You can also copy and paste a rectangle. Say you have to write a formula that is something like this:

If month({Trans.Date}) = 01 then {Balance.Pd01} else
If month({Trans.Date}) = 02 then {Balance.Pd02} else
If month({Trans.Date}) = 03 then {Balance.Pd03} else
If month({Trans.Date}) = 04 then {Balance.Pd04} else
If month({Trans.Date}) = 05 then {Balance.Pd05} else
If month({Trans.Date}) = 06 then {Balance.Pd06} else
If month({Trans.Date}) = 07 then {Balance.Pd07} else
If month({Trans.Date}) = 08 then {Balance.Pd08} else
If month({Trans.Date}) = 09 then {Balance.Pd09} else
If month({Trans.Date}) = 10 then {Balance.Pd10} else
If month({Trans.Date}) = 11 then {Balance.Pd11} else
If month({Trans.Date}) = 12 then {Balance.Pd12}

Normally I would start by typing the first row and then copy it 11 more times. Then I would change each row to use a different number from 1 to 12.  Once I have changed the column of values after the ‘=’ sign I could copy that 2-digit column and past it over the values in the other 2-digit column in one step.

To do this you select the 2 digit column as a rectangle using the ALT key. You can right clock in the column to copy (or use Ctrl-C). Then you select the other 2-digit column and right-click to paste (or use Ctrl-V).

Note, in Crystal you should select these rectangles starting in the upper left corner, especially if you plan to copy and paste.  You can get inconsistent results if you start in one of the other corners.

NotePad++ has a much more sophisticated column mode, allowing you to:

1. Paste a single word or character value into multiple rows at once.
2. Select a column and start typing. The new text is added to ALL the rows at the same time.
3. Use Shift-Alt to mark the column using arrow keys , instead of the mouse.
4. Select the column of text starting in any corner.

Unfortunately, these don’t work in Crystal.

I have written several times about using inflation tables to force duplicate data. I even posted some SQL queries that generate inflation tables in Microsoft SQL Server. Today I needed an inflation table for an Oracle based report and didn’t have one in my library. So I did a bit of research and found this one mentioned frequently. It worked well for my needs today:

SELECT ROWNUM FROM DUAL
CONNECT BY ROWNUM <= 100;

The “100” value can be replaced with whatever number you need.

So when do we use inflation tables? I use them whenever I need to turn a single record into multiple records. Here are the common uses I have seen:

• Print multiple labels for a single row based on the quantity value in that row.
• Splitting some orders into multiple records for shared commissions.
• Creating a series of dates from a single date record.
• Separating a multi-value field into separate single value records.
• To repeat all records several times, grouped differently each time.

If you have a task like this and want some help, give me a call.

Today I had to use a SQL Command to create a report. This isn’t unusual since a query of even moderate complexity will often require that you use a SQL command. But, the extra challenge today was that the database was DB2 and I don’t often work with that flavor of SQL.

Even though SQL is considered a “standard” language, every database has different function names and different syntax. A few years ago I created a “cheat sheet” that lists the most common SQL functions for the 7 SQL flavors I encounter the most:

• SQL Server
• MySQL
• Oracle
• MS Access
• Pervasive
• Progress
• PostgeSQL

I added an 8th column for DB2 a while ago, but then I didn’t get any opportunities to use DB2 SQL so that column remained empty. Today while I was testing functions and syntax for my new command I decided to see how much of that DB2 column I could fill in.  I did some and then Greg Nash from Australia provided some more. It is now fairly complete and I have posted the updated sheet on my site. Please download a copy if you think it will be useful.

I was recently trying to create a report and was having trouble finding a specific table. I knew the field name, but it wasn’t in any of the tables where I expected it to exist. Lets just say the table names in this database are cryptic.

Fortunately, most of the mainstream databases allow you to query the system tables to list all the tables and fields. I found some great SQL online and created a report to read the table structures and search for the field I needed. The query allowed me to create a searchable data dictionary report, or schema, for this database.

The SQL example I found is on a site that lists similar SQL for other databases as well:

This example is for MS SQL Server:
https://dataedo.com/kb/query/sql-server/list-table-columns-in-database

This example is for Oracle:
https://dataedo.com/kb/query/oracle/list-table-columns-in-database

This example is for MySQL:
https://dataedo.com/kb/query/mysql/list-table-columns-in-database

This example is for Pervasive:
https://communities.actian.com/s/question/0D53300003xbnTrCAI/how-to-query-schema

So if you want to generate some quick searchable documentation for databases in any of these formats you can use the links above.

I was asked about this feature today and noticed that I had never mentioned it in my blog. It is a strange feature because you can turn it on in most reports and yet in most reports it will do absolutely nothing. But when you get it to work it can greatly improve the performance of the report.  You will find this option in the database menu.

So what does it do? It tells the database to do the grouping and subtotals. The database will only return one summary record for each group. Check the SQL statement and you will see a GROUP BY clause at the end.  This is useful when you have huge amounts of data to process and when you don’t need to show any detail level values.  But, this feature only works in reports that meets some very specific criteria.

Things you must do:

Group on a database field or a SQL expression (not a formula)
Hide or suppress the details
Make all of the visible fields either a group field or a summary field
Limit the summaries used to those supported by your Database
(Sum, count, minimum and maximum should be safe in all databases)

Things you must NOT do:

Use specified order grouping.
Add running totals using detail fields.
Add summaries using formulas fields.

If your report meets these criteria, Crystal should be able to add the GROUP BY to your SQL statement, get the server to group the records  and calculate all of the summary fields. Use the Show SQL option to confirm the GROUP BY appears.

I have been writing lots of SQL commands lately for my reports.  I am doing this so much now that I regularly tap into my SQL cheat sheet which shows the most commonly used SQL functions and calculations for the seven SQL flavors that I see the most.

Recently a customer needed several date range functions to be used in the WHERE clause of a SQL Command. The list was quite similar to the date range function list available in Crystal. He wanted the following functions: YearToDate, MonthtoDate, LastFullMonth, LastFullYear, LastFullWeek, Last7days, PreviousDay.

So I did some research and found some excellent discussions on the best approach to take. For instance, I read that you don’t want to use any functions on the database field itself. I had started to use the Oracle TRUNC() function to strip the time off of the database field, but I read that this can prevent the query engine from using an index. However functions don’t affect the index when used to calculate the literal dates that you are comparing. I also read that when you don’t strip the time from the DB field it gets harder to use a BETWEEN comparison. The most knowledgeable posts I read avoid Between and instead used the raw DB field in two separate comparisons like this:

db.field >= CalculatedRangeStart and
db.field < CalculatedRangeEnd

The CalculatedRangeEnd is always the day AFTER your range ends, so you can use < to get all times on the last day.

This is what i came up with for SQL Server:

Previous Day
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) -1,0) and
X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

LastFullWeek
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)
- DATEPART(dw, CURRENT_TIMESTAMP) -6,0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)
- DATEPART(dw, CURRENT_TIMESTAMP) +1,0)

Last 7 days(to yesterday)
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)-7 ,0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

Month to Date(to yesterday)
X.DATE >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

YearToDate(to yesterday)
X.DATE >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

LastFullMonth
X.DATE >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP)-1, 0)
and X.DATE < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

LastFullYear
X.DATE >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP)-1, 0)
and X.DATE < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)

And this is what i came up with for for Oracle:

Previous Day
X.DATE >= TRUNC(sysdate) - 1 and X.DATE < TRUNC(sysdate)

LastFullWeek
X.DATE >= trunc(sysdate - 7,'WW') and X.DATE < trunc(sysdate,'WW')

Last 7 days (to yesterday)
X.DATE >= TRUNC(sysdate) - 7 and X.DATE < TRUNC(sysdate)

Month to Date(to yesterday)
X.DATE >= TRUNC(Sysdate,'MM') and X.DATE < TRUNC(sysdate)

YearToDate(to yesterday)
X.DATE >= TRUNC(Sysdate,'Year') and X.DATE < TRUNC(sysdate)

LastFullMonth
X.DATE >= TRUNC(TRUNC(Sysdate,'MM') - 1, 'MM') and X.DATE < TRUNC(Sysdate,'MM')

LastFullYear
X.DATE >= TRUNC(TRUNC(Sysdate,'Year') - 1,'Year') and X.DATE < TRUNC(Sysdate,'Year')