Hessel de Walle's Blog

While making  a SQL query I found out that selective joining would be the best solution, but I was not sure about using a CASE WHEN in the FROM clause. Two simple examples of the tables I used: tblTermen with some Dutch terms term Maat 01 Maat 02 Maat 03 Matrijs and tblTermenVertaald with Dutch and English alternatives nederlands engels Maat Measure Matrix Die Of course, in the first three cases I only wanted to link the first part of the column form the first table. In the last case all of the field could be used. On the internet I found some indications the CASE WHEN could be used in the FROM clause. It took a little experimenting but finally I cam up with this: SELECT term, nederlands, engels   FROM tblTermen INNER JOIN  tblTermenVertaald ON      CASE WHEN CHARINDEX(' ',term)>0 THEN LEFT(term, CHARINDEX(' ',tblTermen.term)-1)     ELSE ...

The other day I ran in to a problem with Report Builder . I had a number of multi value parameters with a lot of values. And there was a single value parameter for selecting items too. The report rendered fine in design mode. But as soon as I published it, it was no longer rendering properly. The mutli value parameters also contained values which would not give any result at all given a certain item number. But then again, in design node no problem. The solution I finally came up with was to interrelate the multi value parameters to the single value parameter by using a SQL   subquery. Let's look at this example I created based on the NorthWind database. My main dataset: SELECT        Customers.CustomerID, Customers.CompanyName, [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,                          ...

The report I created here contains no data from an external source. The numbers we see, all come from calculations based on the figures given via the input parameters. The expressions look like this: Max Deviation: =Code.BerekeningNorm(Parameters!parNormAfwijking.Value,Parameters!parProfielLengteNorm.Value) (based on the upper two paramaters) Requirement : =Code.Berekening(Parameters!parMaxAfwijkingNorm.Value,  Parameters!parProfielLengteNorm.Value)  (based on lower parameter and the right upper one) The function code behind the report: Function Berekening(MaxAfwijkingNorm as Integer,ProfielLengteNorm as Integer) as Double Berekening = MaxAfwijkingNorm ^2 Berekening = Berekening + (ProfielLengteNorm / 2)^2 Berekening = Berekening / (2 * MaxAfwijkingNorm) Berekening = Berekening - ((Berekening^2) -  250000)^(1/2) End Function Function BerekeningNorm(NormAfwijking as Double,ProfielLengteNorm as Integer) as Double BerekeningNorm=NormAfwijk...

This is based on an article from the SAP site: http://search.sap.com/notes?id=0001217871&boj=/sap/bc/bsp/spn/scn_bosap/notes.do?access=69765F6D6F64653D3939382669765F7361706E6F7465735F6E756D6265723D30303031323137383731 I tried to add some more examples to it. SQL Expression Field definition SQL Expression fields are similar to formula fields, but they are written in Structured Query Language (SQL). They are useful in optimizing report performance because the tasks they execute are performed on the database server. You can use SQL Expression fields to perform pre-defined functions on database fields. The list of available functions depends on the type of database in use. This list is available in the Function Tree of the SQL Expression Editor dialog box. NOTE : If Crystal Reports recognizes the function name it will turn blue in the SQL Expression Editor dialog box. Additional database functions may be available to you, depending on your database, but they ...

1        Basiscursus ANSI SQL In dit cursusmateriaal houd ik rekening met de verschillende varianten van de taal SQL. Ik heb deze cursus verschillende malen gegeven in Access and SQL Server Management Studio. Andere tools zou ook geen probleem zijn. Duur cursus: 2 dagen INHOUDSOPGAVE ANSI SQL 1                 Inleiding                                                                                           ...