Archive for the 'SQL' Category



SQL Function “cheat sheet” for writing commands

Sunday 8 October 2017 @ 5:44 pm

In recent years, I have been doing lots of SQL command-based reports, which means writing lots of queries in every imaginable flavor of SQL. It was initially frustrating trying to keep the syntax changes straight and I got tired of doing web searches for the same functions over and over.

My solution was to create a “cheat sheet” grid to store my most commonly used SQL functions and the correct syntax to use in each of the database platforms. Once I looked up a function, I would add it to the grid and now the grid answers most of my questions. There are about 40 functions listed and I have most of them completed for the 7 flavors of SQL I see most (plus Crystal Reports formula syntax):

  • Microsoft SQL Server
  • Oracle
  • MySQL
  • Pervasive
  • Progress
  • PostGre SQL
  • MS Access

I have empty columns for Providex and DB2. They are empty because I haven’t had any recent projects on those platforms and don’t have an environment where I can experiment.

I hope some of you will find this grid useful. You can also share the file with others as long as you leave the heading in place. If any of you want to fill in some of the gaps or suggest an improvement, your contributions would be appreciated.  And thanks to John Pelot of Skyward, Inc for filling in many of the progress functions.

 




Using NotePad++ for formulas or SQL Commands

Thursday 24 August 2017 @ 5:50 pm

If you have to write SQL commands or complicated Crystal formulas, it helps to have a good text editor. My favorite is NotePad++. Not only is it free, but it can do many helpful things beyond what Notepad can do:

  • Number each line.
  • Highlight the corresponding paren/bracket whenever you select a paren/bracket
  • Highlight all the instances of any word that you select
  • Record keystroke macros to automatically repeat a series of commands
  • Select a vertical strip of characters from within of a larger block of text (called “column mode”). (e.g. highlight the 5th and 6th characters in all rows at once)
  • Find all of the differences between two text blocks or two lists, using the “compare” plugin.
  • “Fold” (collapse and expand) sections between parentheses or keywords

It can even format the code for 80 different programming languages, including SQL, showing reserved words and comments in different colors. Within a week I will have a UDL (User Defined Language) for Crystal Reports formula syntax. That will allow NotePadd++ to format a Crystal formula to look the same as it would in Crystal, with the comments in green and the functions in blue.

If you want to try out NotePad++ the best place to get it is through Ninite.com.  I have written about Ninite before. The advantage of Ninite is that it lets you select multiple items from a list of 80+ freeware apps. Then it gives you a tiny exe that installs all the applications you chose (and nothing else) with one click. No questions, no toolbars no ‘bonus’ apps.  It is one of the few places left where you can get freeware without junk.




Today’s date in SQL Server

Thursday 9 March 2017 @ 9:31 pm

I haven’t written many posts on SQL topics. But recently it seems that much of my work involves writing reports based on complex SQL queries. Part of this involves converting Crystal formula logic into SQL syntax. The fun part is that the syntax varies from one flavor of SQL to another, especially for date calculations.

So today’s post is specific to SQL Server syntax, one of the most common flavors. I often need date calculations that are relative to today’s date. In a Crystal formula I would use CurrentDate. In SQL Server syntax the closest equivalent function is GetDate(). I have used it for quite a while assuming that it is the same as CurrentDate. But I just recently discovered that GetDate() includes both the date and the time, which changes things. If you run a report at 2pm on March 8th and the WHERE clause says:

WHERE orders.Date >= GetDate() -2

you might expect to get all the records on March 6th, but you probably wouldn’t. If your Orders.Date field doesn’t store times you would not get any records from the 6th. If that field does have times you would get records, but only those after 2pm on the 6th. So if you want the calculation above to behave like the CurrentDate function in Crystal you have to remove the time from GetDate().

I found two ways to strip off the time off any DateTime value. The one I see listed most often is this one:

DateDiff(d, 0, GetDate())

or this one submitted by Ralph Wahlert:

Cast(GetDate() as Date)

These works great in a WHERE or ON clause, but have one flaw. If you include them in your SELECT clause so that you can show the date on the report the first will appear as a number in Crystal and the second will appear as a string. To use them in the SELECT and have them as dates, you have to convert them to DateTimes:

cast(DateDiff(d, 0, GetDate()) as DateTime)
cast(cast(GetDate() as Date) as DateTime)

These two work in the WHERE/ON clauses as well as the SELECT. In each case they will return a DateTime value but with the time portion set to 12:00am.




Getting MAS 90 to run reports with SQL commands

Sunday 10 April 2016 @ 8:48 pm

I recently learned how to fool MAS 90 into running a custom report based on a SQL command. This might only apply to older versions but I thought it was worth sharing just in case.

I created a report for a customer based on a command. It ran fine within Crystal but when added as a custom report in the MAS custom reports folder it generated an error.  We learned that MAS does a conversion step with custom reports and has to verify that all the tables exist. Our command obviously wouldn’t be found as a table so the conversion would fail.

After some web searching I found an old forum thread that described a workaround for using external tables in a custom report.  A few experiments confirmed that the same technique works for reports based on a SQL command.

The steps were to:
1) Use a similar report that uses only standard tables, and let SAGE do the conversion step on that report.
2) Open the real report (the one that uses a command) and go to File > Summary Info.
3) Modify the “keywords” section to say “Converted to version 4.40” (or your version) and save the real report.
4) Replace the first report that Sage converted with the real one.

Apparently SAGE marks the converted reports in the keywords section and skips that step if it has been done. So by adding that phrase we cause SAGE to skip the conversion, and the report seems to run fine.

My customer is using an MAS 90 4.4 which was replaced with 4.5 in 2012, but maybe this will help someone else. And if this works for you in a newer version, please let me know.




Reset ‘locked’ SQL in Crystal Reports.

Sunday 10 January 2016 @ 5:15 pm

I was helping a customer troubleshoot a misbehaving report. He could add criteria to the report, but when he removed the original criteria from the selection formula the it was still applied. So I looked at the SQL that CR was generating and found that it was not being updated based his our changes.  After a few more tries my customer asked me about an unfamiliar button below the “SHOW SQL QUERY” window. Only then did I noticed the extra button that said RESET.   I had not seen that button in about 10 years, but I now knew the problem with this report.

This report was originally created in an early version of Crystal, probably v8.x. In these early versions you were allowed to tweak the FROM and WHERE clauses in the automatically generated SQL.  This is no longer allowed because now we can create SQL Commands.  Back then, when you modified the SQL it became ‘locked’ and Crystal would no longer update it. If you added more selection criteria the new criteria would not be incorporated into the SQL. Instead it would be applied after the data came back from the database.  When you wanted to revert to automatically generated SQL you would hit the RESET button.

In current versions of CR the RESET button is not needed so it doesn’t normally appear.  But it will appear automatically if you open an old report that has locked SQL.  This allows you to eliminate the use of an obsolete feature.  So I checked the SQL to see if the original tweaks were still essential.  There were no UNION queries, subqueries or filters built into outer joins. So I reset the SQL and the RESET button disappeared. After that the report behaved like a normal report.




More efficient SQL for returning the last record

Saturday 21 November 2015 @ 4:57 pm

I wrote up a method I called the “wormhole” years ago. Normally when you calculate the maximum of a column you can refer to that maximum in any formula in the report. The wormhole technique takes advantage of this behavior to transport other values from the same row as the maximum value. For instance, I can move several values from the last record of the report and show them at the top of the report. To do this I append the other fields to the original, take the maximum of the combined string and then split the maximum value back into pieces. Recently I realized that this method can also be used in SQL expressions to return multiple values, and in SQL commands to simplify certain queries.

Say you have a table of transactions by customer. You want to generate a list that shows the customer name and three fields from their last order (date, order ID and amount). This is simple to do in Crystal without resorting to SQL, but it requires that Crystal bring back ALL of the orders. You then either sort and display only the group footer, or you do a group selection to select the maximum order date for each group.

But if you only want the maximum record to come back from the database then you need to do the work in SQL. There are several methods to return just the last record. The most common involves running two queries and joining them. The first query would pull a list of ALL of the orders for each customer and would return the four fields. The second query would use a “Group By” on the customer and return only the last or Max() date, for each customer. Then you would inner join these two results on both the Customer ID and the Order Date to have all the fields from the first query but limited to the date from the second query. Here are is an example of this query that can be run as a SQL command in the Xtreme sample database:

SELECT `Customer`.`Customer ID`, `Customer`.`Customer Name`, `Orders`.`Order ID`, 
`Orders`.`Order Amount`, `Orders`.`Order Date`
FROM (`Customer` `Customer` 
INNER JOIN `Orders` `Orders` ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
INNER JOIN ( 
   SELECT `Customer`.`Customer ID`, max(`Orders`.`Order ID`) as LastOrder
   FROM `Customer` `Customer` INNER JOIN `Orders` `Orders` 
   ON `Customer`.`Customer ID`=`Orders`.`Customer ID`
   Group By `Customer`.`Customer ID`) LastOrd
ON `Orders`.`Order ID`=`LastOrd`.`LastOrder` 
and `Customer`.`Customer ID`=`LastOrd`.`Customer ID`

This works fine, but it has to do two separate queries.  This can slow things down, especially if you have to do this several different times. The alternate approach would be:

SELECT `Customer`.`Customer ID`, `Customer`.`Customer Name`, 
max ( 
Format (`Orders`.`Order Date`, 'yyyy/mm/dd') & '-' & 
format (str(`Orders`.`Order ID`) , '00000') & '-' & 
format (str(`Orders`.`Order Amount`), '000000.00') 
) as MaxStr
FROM (`Customer` `Customer` 
INNER JOIN `Orders` `Orders` ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
Group by `Customer`.`Customer ID`, `Customer`.`Customer Name`

Lines 2 to 6 calculate the maximum of a combined string for each customer. The string includes the Date, the ID and the Amount of the transaction. The string starts with the date formatted as Year-Month-Day. The maximum of that string will grab the last order, and will also include the values of the other three fields. It returns the same values as the fist query and only takes one pass. The only downside is that you get your values inside a combined string.  To split them apart will take a few extra formulas in Crystal, but you might find this worth the extra effort because the query should run faster.

This technique is especially useful when you do a subquery inside a SQL Expression field. SQL Expression fields can only ever return a single “value”, but with this method that one value can include a combination of several fields from the same row of data.




SQL expression pulls values from a code table

Wednesday 21 October 2015 @ 4:22 pm

SQL expressions don’t get used very often. After all, if you know how to write a SQL expression, why not write the whole query as a command?  But this week a customer asked me if using a SQL expression could help them look up values in a master code table.  It was a great idea and we developed a handy SQL expression that they can use in many different areas.

This customer’s database stores all of their code descriptions in a single master lookup table. I see this quite often.  The code table will have different groups of records for different code fields.  There might be one set of records to decode the product category, another set to decode the customer type, and maybe even a set to decode the state field for addresses.  When you have multiple code sets in the same table you will usually have three fields:

  • {Code} – the code value
  • {Desc} – the description that goes with that code
  • {Field} – the name of the field that is being decoded (e.g. State, ProdCat , CusType)

The last field is necessary when the codes are not unique. For instance a code of 007 might represent Ohio in the “State” list, while further down code 007 might represent ‘filters’ in the ProdCat list.  So once you link on the code you also have to add a record selection filter to specify the field.  Things get complicated if the code field is optional because Crystal doesn’t support filters on outer join fields.  But even when the code field is a required field, retrieving a description involves five steps for each code:

1) add a new instance of a table
2) link the new instance to the correct code field
3) change the alias of the table to make it clear which field is being decoded
4) add the appropriate filter for that field in the selection formula
5) place that field on the report.

With a SQL expression all of the above is contained within the expression. Here is an example:

(SELECT max(code.desc)
FROM code
WHERE code.field = 'ProdCat'
and po.ProdCat = code.code)

Once you verify that it works you can simply copy the object from the layout of one report and paste it in another report and you are done.  All the steps above are accomplished in that one step. And if you need another field decoded you just duplicate the field and change the value ‘ProdCat’ to another code.  Best of all you can use it with optional codes, because the filter is in the expression, not in the overall report query.

And, thanks to Laurie Weaver, a developer at Wyse Solutions, for initiating this idea.

Note –  in certain reports and in certain versions of Crystal a SQL Expression will error with:

“The multi-part identifier [your field] could not be bound.”

This is a Crystal bug and here is the work around.




Working with binary fields in SQL

Tuesday 11 November 2014 @ 4:43 pm

Jared uses a web-based forms application that collects and stores signature images as Base64-encoded PNG files. The Base64 data is stored in a nvarchar(MAX) column in a SQL Server database. He was looking for a way to render the data as images in his Crystal Report and couldn’t find a way to put all the pieces together and contacted me.

As it happens, I have a report that I created that reports on Email messages in my Goldmine database. The body of those messages is stored in my database using a binary field (data type ‘image’). To display the message body on a report I had to write a SQL Command to convert this binary into text.

CONVERT(VARCHAR(8000),CONVERT(VARBINARY(8000),RFC822)) AS message

This converts the first 8000 characters of the binary field called “RFC822” back into a readable text column called “message”. It sounded to me like Jared had the opposite problem with an image stored as Continue Reading »
Working with binary fields in SQL




Converting Crystal formula logic into SQL queries

Friday 16 August 2013 @ 6:04 am

I recently had to convert a complex Crystal Report into a SQL Query, including all the calculations. I eventually found ways to convert everything that was needed and learned some valuable SQL skills. But I also learned to appreciate the things I take for granted in Crystal syntax, things that have no equivalent in SQL syntax. Here are some examples just in case someone else has to do a similar conversion:

1) One huge difference is how simple it is in Crystal to reference one expression in another. Crystal will automatically calculate a series of dependent formulas in the right order, based on which formulas refer to others. In a SQL query, if you write a calculation for column A and you want to use that as part of the calculation for the next column, you can’t simply refer to column A by name. Instead you have to repeat the entire calculation.

The exception is Continue Reading »
Converting Crystal formula logic into SQL queries




SQL union vs cross join

Friday 10 May 2013 @ 6:08 am

I often have reports that require me to include multiple copies of the same records. Normally this means using a command object in Crystal, rather than going directly to the tables. And in most cases I write a UNION query which appends one set of records to itself multiple times. But last month I had a report that required 24 copies of the same 2-year dataset.

I started testing the performance and found that getting a single copy of the data took nearly 5 minutes, partly because the data was coming from a view. I then found that adding a UNION of a second copy of the data added another 4 minutes to the query, and each additional UNION added 4 more minutes. It would take nearly 2 hours to get 24 copies of the data, so I had to scrap the UNION idea.

Then I wondered if using a CROSS JOIN might be faster. A CROSS JOIN is when you add a table to a report, but you leave it unlinked. Normally this is a very bad thing to do but it is handy when you need duplicate data.

So I found an unrelated table that had a column of consecutive numbers and wrote a query that selected the numbers from 1 to 7. Then I added the SQL for that small query as a cross-join in my command. The result was 7 copies of every record in the dataset, retrieved in about 5 minutes. Even when I scaled it up to generate all 24 copies of the data, it only took about 7 minutes total. Obviously much better than 2 hours for the UNION.

Now maybe this would be obvious to someone who works primarily writing SQL, but the magnitude of the difference was a surprise to me. I will be using CROSS JOINS whenever possible, now.




Next Posts »» «« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server