tag:blogger.com,1999:blog-11516221595515889542023-11-16T09:48:53.257-08:00Lantz Technology CaveBill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.comBlogger51125tag:blogger.com,1999:blog-1151622159551588954.post-21719583080758943862013-08-08T14:30:00.000-07:002013-08-08T14:30:50.123-07:00Reloading a Form in C#I'm been doing some work recently in C# on a Windows Form I'm creating for a pet project. I'm creating a form that presents customer information and allows the user to scroll through each customer using "Previous" and "Next" buttons.<br />
<br />
While working through the form, I was able to get my data into an array and then present the first customer in the form. I then figured out how to update the index of the array for the next customer, but I couldn't figure out how to present the next data into the form. Through my research, I found the following post:<br />
<br />
<a href="http://social.msdn.microsoft.com/Forums/en-US/5a27d0ee-5c28-41f5-aeae-edef9d6a871d/reloads-form-in-c" target="_blank">Reloads Form in C#</a><br />
<br />
Scrolling through the answers, one answer says this:<br />
<br />
<div style="background-color: white; border: none; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 14px; line-height: 20px; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
try this</div>
<div style="background-color: white; border: none; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 14px; line-height: 20px; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
this.formName_Load(this, null);</div>
<hr class="sig" style="background-color: white; border-style: solid none none; border-top-color: rgb(187, 187, 187); color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 14px; height: 1px; line-height: 20px; list-style-type: none; margin: 0.5em 0px; padding: 0px; width: 241.15625px;" />
<div style="background-color: white; border: none; color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 14px; line-height: 20px; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
Kapul</div>
I then put this code into my program right after I've updated the index of my array for the next customer. Magically (not really) the form reloaded with my new data.<br />
<br />
I wanted to discuss the different portions of the above code and what it does:<br />
<br />
<ul>
<li><span style="color: blue;">this </span>- refers to the current instance of the class. For example, you could have multiple classes that have the same form names and each could be referenced by other classes. "this" makes sure that you are referencing the current class.</li>
<li><span style="color: blue;">formName </span>- the name of your form. For example, my form is called "customerForm", so I would replace formName with customerForm.</li>
<li><span style="color: blue;">_Load </span>- tells the form to load. To read more on Load event, go here: <a href="http://msdn.microsoft.com/en-us/library/system.windows.forms.form.load.aspx" target="_blank">Form.Load Event</a></li>
<li><span style="color: blue;">(this, null)</span> - The "this" again refers to the current class. null is used in place of the EventArgs class (which is used to provide data for events). Since this is an event that doesn't contain any data, we then pass a null, which is an empty field. To read more on the EventArgs class, go here: <a href="http://msdn.microsoft.com/en-us/library/system.eventargs.aspx" target="_blank">EventArgs Class</a></li>
</ul>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-2998995096996694002013-08-07T14:16:00.000-07:002013-08-07T14:16:15.717-07:00Calculating a Remainder in SQLWhen doing math problems using SQL, it is a good idea to consider what type of data you are working with. In SQL, you will often deal with a data type called Integer. With integers, if you do any division, all your answers will come out in whole numbers (i.e. you won't get any decimal numbers back). For example, if you run the following in SQL, the answer will be 2 instead of 2.5:<br />
<br />
<span style="color: blue;">select </span>5 / 2<br />
<br />
Clearly you can start to lose data if you are expecting decimal places while dividing integers. It is often better to convert your integers to a data type that allows for decimals. For example, you can convert your integer data types to numeric and then perform the division.<br />
<br />
<span style="color: blue;">select </span><span style="color: #ea9999;">cast</span>(5 as <span style="color: blue;">numeric</span>(2,1)) / 2<br />
<br />
If you don't want to cast your data to another data type because of <a href="http://technet.microsoft.com/en-us/library/ms191530.aspx" target="_blank">possible loss of data</a>, you can also use the Modulo operator (sometimes called Modulus in other programming languages). To use the Modulo operator, replace the divide sign (/) with the percent sign (%). Using the same example from above, we will use the Modulo operator:<br />
<br />
<span style="color: blue;">select </span>5 % 2<br />
<br />
The answer to the above would then be 1, which is the remainder of 5 / 2.<br />
<br />
We could then display our data using the following query:<br />
<br />
<span style="color: blue;">select </span><span style="color: red;">' 5 / 2 is '</span> + <span style="color: #ea9999;">cast</span>(5 / 2 <span style="color: blue;">as varchar</span>) +<span style="color: red;"> ' with a remainder of ' </span>+ cast(5 % 2 <span style="color: blue;">as varchar</span>)<br />
<br />
Which gives us the results of<br />
<br />
5 / 2 is 2 with a remainder of 1<br />
<br />
To read more on Modulo, go here: <a href="http://msdn.microsoft.com/en-us/library/ms190279.aspx" target="_blank">Modulo</a>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-54707598478731026622013-08-06T13:46:00.000-07:002013-08-06T13:46:20.362-07:00Arithmetic Operators in SQLYesterday we talked about <a href="http://lantztechknowledge.blogspot.com/2013/08/sql-variables.html" target="_blank">variables</a>. Today we will talk about an arithmetic operators that can be applied to your variable.<br />
<br />
An arithmetic operator is one that applies some type of math to a value.<br />
<br />
Let's first look at an example of code. For instance, if you wanted to add a 1 onto an integer variable, the most basic way to do this is by doing the following:<br />
<br />
<span style="color: blue;">set </span><span style="color: #93c47d;">@integer</span> = <span style="color: #93c47d;">@integer</span> + 1<br />
<br />
Since you want to add 1 onto the original value of your integer, you have to set the variable equal to your current value plus 1. Using the above, however, requires unnecessary retyping. A better way to approach this would be to use the Add Equal operator, like so:<br />
<br />
<span style="color: blue;">set </span><span style="color: #93c47d;">@integer</span><span style="color: lime;"> </span>+= 1<br />
<br />
The above will add one onto the current value of the variable @integer, while only using our variable once. This gives us less of a chance of a spelling mistake. The same works for the Subtract Equals (-=), Multiply Equals (*=) and Divide Equals (/=) operators.<br />
<br />
If we create different integer variables, we can see how each works<br />
<br />
<span style="color: blue;">declare </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">1</span> int = 1<br />
<span style="color: blue;">declare</span><span style="color: blue;"> </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">2</span> int = 1<br />
<span style="color: blue;">declare</span><span style="color: blue;"> </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">3</span> int = 2<br />
<span style="color: blue;">declare</span><span style="color: blue;"> </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">4</span> int = 2<br />
<br />
<span style="color: blue;">set </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">1</span> += 1<br />
<span style="color: blue;">print </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">1</span><br />
<br />
<span style="color: blue;">set </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">2</span> -= 1<br />
<span style="color: blue;">print </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">2</span><br />
<br />
<span style="color: blue;">set </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">3</span> *= 2<br />
<span style="color: blue;">print </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">3</span><br />
<br />
<span style="color: blue;">set </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">4</span> /= 2<br />
<span style="color: blue;">print </span><span style="color: #93c47d;">@</span><span style="color: #93c47d;">integer</span><span style="color: #93c47d;">4</span><br />
<br />
We would get the following results:<br />
<br />
2 (1 + 1 = 2)<br />
0 (1 - 1 = 0)<br />
4 (2 * 2 = 4)<br />
1 (2 / 2 = 1)<br />
<br />
As a programmer, it is always better not to have to retype code or variables. In this case, you are saving yourself from retyping variables.<br />
<br />
<br />
To read more on Arithmetic Operators, go here: <a href="http://msdn.microsoft.com/en-us/library/ms187716.aspx" target="_blank">Arithmetic Operators</a>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-6477923229799752052013-08-05T13:46:00.000-07:002013-08-05T13:46:33.268-07:00SQL VariablesIt's been a while since the last time I blogged. After a family reunion and a busy week at work, I've been slow to get back into the blogging side of life.<br />
<br />
Today I've decided to start off with a subject that is rather easy. <br />
<br />
In SQL, as with most programming languages, you will find something called a variable (it may be called something else in other programming languages). A variable is a set of data that you give a name to and then can assign values to. For example, in SQL you could have a variable called @pi (the @ symbol tells SQL that this is a variable) and assign it a value of 3.14. If you need to use the 3.14 value throughout your program, you can then call @pi instead of 3.14 each time.<br />
<br />
You might be thinking to yourself "why would I want to create/use a variable instead of just entering 3.14 every time I need it?" Well, there are many ways to answer this question, but the best answer is that what would happen if you accidentally entered 3.15 in one instance instead of 3.14? You could potentially throw off the rest of your program. A variable allows you to use the same value over again without having to re-type it and therefore not leaving yourself open for mistakes.<br />
<br />
There are two steps to creating variables:<br />
<br />
<ol>
<li>Declare the variable. You have to let SQL know that the variable exists and give the variable a data type (int, varchar, datetime, etc.)</li>
<li>Set a value to the variable.</li>
</ol>
<div>
So, if I wanted to create my @pi variable, I would do it like this:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">DECLARE </span>@pi <span style="color: blue;">numeric</span>(5,2)</div>
<div>
<span style="color: blue;">SET </span>@pi = 3.14<br />
<br />
You can also declare and set the variable on the same line:<br />
<br />
<div>
<span style="color: blue;">DECLARE </span>@pi <span style="color: blue;">numeric</span>(5,2) = 3.14</div>
</div>
<div>
<br /></div>
<div>
We can then take that variable and use it:</div>
<div>
<br /></div>
<div>
@Circumference = 2 * @pi * radius</div>
<div>
<br /></div>
<div>
As you dive more into using variables, you will see they are quite useful.</div>
<div>
<br /></div>
<div>
To read more on SQL variables, go here: <a href="http://technet.microsoft.com/en-us/library/ms188927.aspx" target="_blank">Local Variables</a></div>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-79416510290344154332013-07-15T13:55:00.000-07:002013-07-15T13:55:42.474-07:00Using Multiple Databases in SQLIn SQL Server, you can have multiple databases on the same instance of SQL Server. In SQL Server 2012, you can have over 32,000 (go <a href="http://msdn.microsoft.com/en-us/library/ms143432.aspx" target="_blank">HERE</a> to read up more maximum capacity specifications for SQL Server) databases in one SQL Server instance. So, the question is, how do you use/reference all these databases in the same query.<br />
<br />
There are two main ways to accomplish this:<br />
<br />
<ol>
<li>The use of USE</li>
<li>Reference the database in your FROM statement</li>
</ol>
<div>
First, we can use the word USE. The general syntax for this is:</div>
<div>
<br /></div>
<div>
USE <i>database</i></div>
<div>
<i><br /></i></div>
<div>
Where <i>database </i>is the database you are referencing. If our Customer table was in the Billing database, it would look something like this:</div>
<div>
<br /></div>
<div>
USE Billing</div>
<br />
<div>
<br /></div>
<div>
SELECT *</div>
<div>
FROM Customer</div>
<br />
<div>
<br /></div>
<div>
Using USE leaves you rather limited, though, because only the one database can be referenced at a time using USE.</div>
<div>
<br /></div>
<div>
Our other option is to specify the database in our FROM statement. Let's say, for example, that we have our Customer table in the Billing database, while the Orders table is in the Shipping database. If we wanted to use both tables in the same query, it would look like this</div>
<div>
<br /></div>
<div>
SELECT *</div>
<div>
FROM Billing.dbo.Customer</div>
<div>
LEFT OUTER JOIN Shipping.dbo.Orders</div>
<div>
on Customer.CustomerNumber = Orders.CustomerNumber</div>
<div>
<br /></div>
<div>
In the above, we put the database, a dot (.), the user (in this case dbo, which means database owner), another dot (.) and then the table name. Using this method, we can query multiple databases at the same time within the same query.</div>
<div>
<br /></div>
<div>
To read more on USE, go here: <a href="http://msdn.microsoft.com/en-us/library/ms188366.aspx" target="_blank">USE</a></div>
<div>
To read more on selecting your database, go here: <a href="http://msdn.microsoft.com/en-us/library/ms180770(v=sql.105).aspx" target="_blank">Select a Database</a></div>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-37472362108599755462013-07-11T14:00:00.001-07:002013-07-11T14:00:41.165-07:00Wildcards while Searching Text in SQL ContinuedOne part of using wildcards that wasn't covered yesterday was the ability to use the keyword NOT in conjunction with LIKE. Using an example from yesterday, this returns all customers that start with the letter L:<br />
<br />
<span style="background-color: white; color: blue; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">SELECT </span><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">*</span><br style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;" /><span style="background-color: white; color: blue; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">FROM </span><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">Customer</span><br style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;" /><span style="background-color: white; color: blue; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">WHERE </span><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">CompanyName LIKE </span><span style="background-color: white; color: red; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">'L%'</span><br />
<span style="background-color: white; color: red; font-size: 15px; line-height: 20px;"><span style="font-family: inherit;"><br /></span></span>
<span style="font-size: 15px; line-height: 20px;"><span style="font-family: inherit;">If we put the word NOT before LIKE, we will get all customers that do not start with an L:</span></span><br />
<br />
<span style="background-color: white; color: blue; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">SELECT </span><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">*</span><br style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;" /><span style="background-color: white; color: blue; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">FROM </span><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">Customer</span><br style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;" /><span style="background-color: white; color: blue; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">WHERE </span><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">CompanyName NOT LIKE </span><span style="background-color: white; color: red; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">'L%'</span><br />
<div>
<span style="background-color: white; font-size: 15px; line-height: 20px;"><span style="font-family: inherit;"><br /></span></span></div>
<div>
<span style="background-color: white; font-size: 15px; line-height: 20px;"><span style="font-family: inherit;">Likewise, we can exclude any customer that has an L in their name:</span></span></div>
<div>
<br /></div>
<div>
<span style="background-color: white; color: blue; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">SELECT </span><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">*</span><br style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;" /><span style="background-color: white; color: blue; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">FROM </span><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">Customer</span><br style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;" /><span style="background-color: white; color: blue; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">WHERE </span><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">CompanyName NOT LIKE </span><span style="background-color: white; color: red; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">'%L%'</span></div>
<div>
<span style="background-color: white; font-size: 15px; line-height: 20px;"><span style="font-family: inherit;"><br /></span></span></div>
<div>
<span style="background-color: white; font-size: 15px; line-height: 20px;"><span style="font-family: inherit;">The same works with another one of our examples:</span></span></div>
<div>
<span style="background-color: white; color: red; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;"><br /></span></div>
<div>
<div style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">
<span style="color: blue;">SELECT </span>*<br /><span style="color: blue;">FROM </span>Customer</div>
<div style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">
<span style="color: blue;">WHERE </span>FirstName LIKE <span style="color: red;">'_ean'</span></div>
</div>
<div>
<span style="color: red;"><br /></span></div>
<div>
<span style="font-family: inherit;">The above returns all customers that have a name that is four digits and ends in "ean". The following returns any customer not with four digits AND does not end with "ean". It could still be four digits, but cannot end with "ean".</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
<div>
<div style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">
<span style="color: blue;">SELECT </span>*<br /><span style="color: blue;">FROM </span>Customer</div>
<div style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">
<span style="color: blue;">WHERE </span>FirstName NOT LIKE <span style="color: red;">'_ean'</span></div>
</div>
</div>
<div>
<span style="color: red;"><br /></span></div>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-45846295527367120082013-07-10T14:16:00.000-07:002013-07-10T14:16:22.447-07:00Wildcards while Searching Text in SQLLet's say you are looking for some text in SQL, but not exactly sure what the text is. For example, you are looking for any customer where their last name starts with an L. How would you do that in SQL?<br />
<br />
There are two ways you can accomplish this. The first is by using substring like the following:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer<br />
<span style="color: blue;">WHERE Substring</span>(CompanyName, 1, 1) = <span style="color: red;">'L'</span><br />
<br />
This would certainly work, but makes your search NonSARGable (to read up more on SARGable queries, go to this post <a href="http://lantztechknowledge.blogspot.com/2013/05/non-sargable-queries.html" target="_blank">Non-SARGable Queries</a>)<br />
<br />
Luckily, SQL has a logical operator called LIKE (which keeps your query SARGable), that allows you to search for text that matches a specific pattern, as follows:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer<br />
<span style="color: blue;">WHERE </span>CompanyName like <span style="color: red;">'L%'</span><br />
<br />
In this instance, the percent sign (%) allows for any characters before or after it, depending on where the % is placed. The above would look for customers that started with an L. The following would look for all customers that ended with an L:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer<br />
<span style="color: blue;">WHERE </span>CompanyName like <span style="color: red;">'%L'</span><br />
<br />
This would look for all customers that had an L anywhere in its name:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer<br />
<span style="color: blue;">WHERE </span>CompanyName like <span style="color: red;">'%L%'</span><br />
<br />
The % is a wild card option in SQL. There are other wild card options to consider:<br />
<br />
<br />
<ul>
<li>_ (underscore)</li>
<ul>
<li>The underscore represents a single value. For instance, if you are looking for a four letter name that ends in "ean", you could run the following:</li>
</ul>
</ul>
<div>
<br /></div>
<div>
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer</div>
<div>
<span style="color: blue;">WHERE </span>FirstName like<span style="color: red;"> '_ean'</span></div>
<div>
<br /></div>
<div>
<ul>
<li>[ ]</li>
<ul>
<li>A single character within a range or given set. For instance:</li>
</ul>
</ul>
<div>
<br /></div>
<div>
<div>
--Range of letters</div>
<div>
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer</div>
<div>
<span style="color: blue;">WHERE </span>FirstName like <span style="color: red;">'[D-S]ean'</span></div>
</div>
</div>
<div>
<br /></div>
<div>
<div>
--Set of letters</div>
<div>
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer</div>
<div>
<span style="color: blue;">WHERE </span>FirstName like <span style="color: red;">'[DEFGHS]ean'</span></div>
</div>
<div>
<br /></div>
<div>
<ul>
<li>[^]</li>
<ul>
<li>A single character not within a range or give set</li>
</ul>
</ul>
<div>
<br /></div>
<div>
<div>
<div>
--Not in this range of letters</div>
<div>
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer</div>
<div>
<span style="color: blue;">WHERE </span>FirstName like <span style="color: red;">'[^D-S]ean'</span></div>
</div>
<div>
<br /></div>
<div>
<div>
--Not in this set of letters</div>
<div>
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer</div>
<div>
<span style="color: blue;">WHERE </span>FirstName like <span style="color: red;">'[^DEFGHS]ean'</span></div>
</div>
</div>
</div>
<div>
<br /></div>
<div>
To read more on LIKE and different wild card options, go here: <a href="http://msdn.microsoft.com/en-us/library/ms179859.aspx" target="_blank">LIKE</a></div>
<div>
<br /></div>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-46802126451086587872013-07-09T14:51:00.001-07:002013-07-10T08:50:18.834-07:00WHILE Statements in SQLPreviously I've spoken about control of flow statements in SQL (see the following: <a href="http://lantztechknowledge.blogspot.com/2013/06/ifelse-in-sql.html" target="_blank">IF...ELSE</a>, <a href="http://lantztechknowledge.blogspot.com/2013/06/goto-in-sql.html" target="_blank">GOTO</a>). Today I will talk about another control of flow statement: the <span style="color: blue;">WHILE </span>statement.<br />
<br />
A <span style="color: blue;">WHILE </span>statement allows you to specify a statement to check and then SQL statement(s) to run while that expression is still true.<br />
<br />
The general outline for a WHILE statement is as follows:<br />
<br />
<span style="color: blue;">WHILE </span>Boolean_expression<br />
{ sql_statement }<br />
<br />
<ul>
<li>A boolean expression is one that is either true or false</li>
<li>The SQL statement(s) are the statements to run while the WHILE statement is still true</li>
<li>If your SQL statement is more than one statement, you will have to begin and end your WHILE statement with BEGIN and END, as seen below.</li>
</ul>
<div>
<span style="color: blue;">WHILE </span>Boolean_expression</div>
<div>
<br /></div>
<div>
<span style="color: blue;">BEGIN</span></div>
<div>
<span style="color: blue;"><br /></span></div>
<div>
SQL Statement Number 1</div>
<div>
SQL Statement Number 2</div>
<div>
<br /></div>
<div>
<span style="color: blue;">END</span></div>
<div>
<br /></div>
<div>
Following is an example of when a WHILE statement may be useful:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">WHILE </span>(<span style="color: blue;">SELECT </span>count(*) <span style="color: blue;">FROM </span>Customers <span style="color: blue;">Where </span>Balance < 50) > 0</div>
<div>
<br /></div>
<div>
<span style="color: blue;">BEGIN</span></div>
<div>
<br /></div>
<div>
<span style="color: blue;">UPDATE </span>Customers</div>
<div>
<span style="color: blue;">SET </span>Balance = Balance * 2</div>
<div>
<span style="color: blue;">Where </span>Balance < 50</div>
<div>
<br /></div>
<div>
<span style="color: blue;">END</span></div>
<div>
<br /></div>
<div>
The above will update the balance of a customer to their balance times 2 until that balance is above $50. For example, if we have a customer whose balance is $5, it will go through the WHILE statement 4 times until the balance is $80.</div>
<div>
<br /></div>
<div>
Keep in mind that when <span style="color: blue;">WHILE </span>statements are used, it is possible to run into an infinite loop (a loop that never stops) if you aren't incrementing your values correctly. Using the same query, if I change it a little bit, I can get an infinite loop:</div>
<div>
<span style="color: blue;"><br /></span></div>
<div>
<span style="color: blue;">WHILE </span>(<span style="color: blue;">SELECT </span>count(*) <span style="color: blue;">FROM </span>Customers <span style="color: blue;">Where </span>Balance > 50) > 0</div>
<div>
<span style="color: blue;"><br /></span></div>
<div>
<span style="color: blue;">BEGIN</span></div>
<div>
<span style="color: blue;"><br /></span></div>
<div>
<span style="color: blue;">UPDATE </span>Customers</div>
<br />
<div>
<div>
<span style="color: blue;">SET </span>Balance = Balance * 2</div>
<div>
<span style="color: blue;">Where </span>Balance > 50</div>
<div>
<span style="color: blue;"><br /></span></div>
<div>
<span style="color: blue;">END</span></div>
</div>
<div>
<span style="color: blue;"><br /></span></div>
<div>
The above will never stop if I have one customer that has a balance over $50. Because they are over $50, our boolean expression is and will always be true.</div>
<div>
<br /></div>
<div>
To read up more on WHILE statements, go here: <a href="http://msdn.microsoft.com/en-us/library/ms178642.aspx" target="_blank">WHILE</a></div>
<div>
<br /></div>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com2tag:blogger.com,1999:blog-1151622159551588954.post-65031814001950670992013-07-08T14:56:00.001-07:002013-07-08T14:57:49.682-07:00Creating a Child Form in C#I spent some time this weekend working in C# on a personal project. One of the subjects I came across was creating a child form. A child form opens a new form within your current window.<br />
<br />
During my research, I came across the following help document:<br />
<br />
<b><a href="http://msdn.microsoft.com/en-us/library/7aw8zc76(v=vs.80).aspx" target="_blank">How to: Create MDI Child Forms</a></b><br />
<br />
I have a program I created at work that has different tabs for specific tasks that need to be run. I will be working on migrating from the tabbed format to the child form format, as this seems to be less confusing and the layout looks better.<br />
<br />
A few key points for creating a child form:<br />
<br />
<ol>
<li>Create a new windows form in your project for each new window.</li>
<li>Create an event handler for when you click on the button/menu item that will open the new form (<a href="http://msdn.microsoft.com/en-us/library/zwwsdtbk(v=vs.80).aspx" target="_blank">Create Event Handler</a>)</li>
<li>Paste the following in your event handler:</li>
</ol>
<span style="color: blue; font-family: Consolas, Courier, monospace; font-size: 13px; line-height: 17px;">protected</span><span style="font-family: Consolas, Courier, monospace; font-size: 13px; line-height: 17px;"> </span><span style="color: blue; font-family: Consolas, Courier, monospace; font-size: 13px; line-height: 17px;">void</span><span style="font-family: Consolas, Courier, monospace; font-size: 13px; line-height: 17px;"> MDIChildNew_Click(</span><span style="color: blue; font-family: Consolas, Courier, monospace; font-size: 13px; line-height: 17px;">object</span><span style="font-family: Consolas, Courier, monospace; font-size: 13px; line-height: 17px;"> sender, System.EventArgs e)</span><br />
<span style="font-family: Consolas, Courier, monospace; font-size: 13px; line-height: 17px;">{</span><br />
<pre style="font-family: Consolas, Courier, monospace; font-size: 13px; line-height: 17px; overflow: auto; padding: 5px; word-wrap: normal;"> Form2 newMDIChild = <span style="color: blue;">new</span> Form2();
<span style="color: green;">// Set the Parent Form of the Child window.</span>
newMDIChild.MdiParent = <span style="color: blue;">this</span>;
<span style="color: green;">// Display the new form.</span>
newMDIChild.Show();
}</pre>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-80385227276339763292013-07-01T17:31:00.000-07:002013-07-01T17:31:00.800-07:00Subqueries in SQLA very helpful feature of SQL is the use of subqueries. These subqueries allow you to nest one query inside of another query, thus giving you more flexibility with your data. There are two areas in which a subquery can be used. The first of these areas is in the SELECT statement. For example, if we wanted to see how many purchases a customer has made, we can run a query like this:<br />
<br />
SELECT CustomerNumber, CustomerName,<br />
(SELECT SUM(Quantity)<br />
FROM Orders<br />
WHERE Orders.CustomerNumber = Customer.CustomerNumber) as Quantity<br />
FROM Customers<br />
<br />
In the above example, we used a subquery on the Orders table to get the total quantity (number of purchases) and then the Customers table to get our customer information.<br />
<br />
The second instance where a subquery can be used is in the WHERE clause. This gives us the ability to look for a particular set of data. As another example, let's say that we want to look for customers that have an order greater than $50:<br />
<br />
SELECT *<br />
FROM Customers<br />
WHERE CustomerNumber in<br />
(<br />
SELECT CustomerNumber<br />
FROM Orders<br />
WHERE TotalPrice >= 50.00<br />
)<br />
<br />
The subquery returns all CustomerNumbers that have an order of $50 or greater. We then tell SQL to look for all those (in) CustomerNumbers in our Customers table. We could also tell SQL to give us CustomerNumbers that are not in the list by putting the word "not" in front of "in, like so:<br />
<br />
SELECT *<br />
FROM Customers<br />
WHERE CustomerNumber not in<br />
(<br />
SELECT CustomerNumber<br />
FROM Orders<br />
WHERE TotalPrice >= 50.00<br />
)<br />
<br />
This would return all customers that did not have an order of $50 or more. The word "in" in this situation is called a "logical operator", which we will discuss in my next blog.<br />
<br />
To read more on subqueries, go here: <a href="http://msdn.microsoft.com/en-us/library/ms189575(v=sql.105).aspx" target="_blank">Subquery Fundamentals</a>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-49635412157625408442013-06-27T13:58:00.004-07:002013-06-27T13:58:44.679-07:00SQL Joins Part 3I was doing some research on the CROSS join since I haven't put this join into practice. I'm still having a hard time imagining when this would be useful.<br />
<br />
For purposes of completeness, I will explain how a CROSS join works.<br />
<br />
Unlike other joins, a CROSS join does not have an "on" statement where you provide columns for comparisons. You can insert comparison columns into the where clause, but when this occurs, you are basically running an inner join.<br />
<br />
Following is an example of CROSS join, using two tables that are not related in any way.<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customers<br />
<span style="color: #999999;">CROSS JOIN</span> Vendors<br />
<br />
If our Customers table has ten rows and our Vendors table has ten rows, this query will return 100 rows. Each row from the first table will show with each row in the second table.<br />
<br />
Following are two tables that do have comparison columns, with which we will run a CROSS join:<br />
<br />
SELECT *<br />
FROM Customers<br />
CROSS JOIN Orders<br />
WHERE Customers.CustomerNumber = Orders.CustomerNumber<br />
<br />
We've essentially just run an inner join.<br />
<br />
To read more on Cross Joins, go here: <a href="http://msdn.microsoft.com/en-us/library/ms190690(v=sql.105).aspx" target="_blank">Using Cross Joins</a><br />
<br />
<b>What situations do you think a CROSS join could be useful?</b><br />
<b><br /></b>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-11218634328024488202013-06-26T13:34:00.002-07:002013-06-26T13:34:39.073-07:00SQL Joins Part 2Yesterday we spoke about OUTER joins. Today we will talk about INNER joins. INNER joins will return all rows from both tables, as long as the values in the columns you are joining on match. This differs from OUTER joins in the fact that OUTER joins could in fact return NULL values for one table. INNER joins only return those values that match within both tables.<br />
<br />
Going back to our example of yesterday, we will use the Customers and Orders tables as examples. First, lets look at an OUTER join:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customers<br />
<span style="color: #999999;">LEFT JOIN</span> Orders <span style="color: #999999;">on </span>Customers.CustomerNumber = Orders.CustomerNumber<br />
<br />
The above would return all Customers and any matching orders in the Orders table, even if no orders exist. Next, we will use the INNER join, as follows:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customers<br />
<span style="color: #999999;">INNER JOIN</span> Orders <span style="color: #999999;">on </span>Customers.CustomerNumber = Orders.CustomerNumber<br />
<br />
This will only return data where it exists in both tables. If a customer doesn't have any orders, no data will be returned for that customer.<br />
<br />
As part of your column matching, you can also use math operators ( >, <, <>). For example, if we want only orders that are over a certain amount, you would run a query like so:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customers<br />
<span style="color: #999999;">INNER JOIN</span> Orders <span style="color: #999999;">on </span>Customers.CustomerNumber = Orders.CustomerNumber and Orders.Amount > 10.00<br />
<br />
This will return only those orders where the amount is greater than $10.<br />
<br />
To review, OUTER joins will return data for both tables, even if data doesn't exist (it will return NULLs for non-existent data). INNER joins, on the other hand, will only return data that exists, based upon your matching columns.<br />
<br />
To read more on INNER joins, go here: <a href="http://msdn.microsoft.com/en-us/library/ms190014(v=sql.105).aspx">Using Inner Joins</a><br />
<br />Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-89521391982720691892013-06-25T13:27:00.000-07:002013-06-25T13:27:36.880-07:00SQL Joins Part 1In SQL, there are three different types of JOINs, which, if mastered, can be extremely helpful in querying your data. The three different joins are:<br />
<ol>
<li>OUTER JOIN</li>
<li>INNER JOIN</li>
<li>CROSS JOIN</li>
</ol>
<div>
First, with any JOIN statement, you need to specify the columns you will join on. This tells SQL which columns to use as comparison. After our second table, we put the word "on" and follow that with our columns comparisons. As we jump into this post, we will see the "on" used in our FROM clause.<br />
<br />
Today I will focus on the OUTER Joins.<br />
<br />
Within the OUTER Joins there are the LEFT, RIGHT and FULL JOINs. These joins are pretty easy to understand. A query containing a LEFT or RIGHT JOIN will return all rows in the described table (left or right) and matched rows in the other table. This is defined by which table is on the left (the first table in your FROM clause) or on the right (the second table). Here is an example if we are using our Customers and Orders tables:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">SELECT </span>*</div>
<div>
<span style="color: blue;">FROM </span>Customers </div>
<div>
<span style="color: #999999;">LEFT JOIN</span> Orders on Customers.CustomberNumber = Orders.CustomerNumber</div>
<div>
<br /></div>
<div>
In this example, Customers is on the left, while Orders is on the right. This will return all rows in the Customers table and any matching data in the Orders table.</div>
<div>
<br /></div>
<div>
We can then reverse it for another example:</div>
<br />
<div>
<div>
<span style="color: blue;">SELECT </span>*</div>
<div>
<span style="color: blue;">FROM </span>Customers </div>
</div>
<div>
<div>
<span style="color: #999999;">RIGHT JOIN</span> Orders on Customers.CustomberNumber = Orders.CustomerNumber</div>
</div>
<div>
<br /></div>
<div>
The above will return all rows in the Orders table and any matching rows in the Customers table. You can also get the same results by running the following:</div>
<div>
<br /></div>
<div>
<div>
<span style="color: blue;">SELECT </span>*</div>
<div>
<div>
<span style="color: blue;">FROM </span>Orders</div>
<div>
<span style="color: #999999;">LEFT JOIN</span> Customers on Customers.CustomberNumber = Orders.CustomerNumber</div>
</div>
</div>
<div>
<br /></div>
<div>
Notice that Orders is the "left" table and Customers is the "right" table. It may become more evident why you would use LEFT or RIGHT when we reference more than one table. Let's take our example and add OrderDetails into the mix:</div>
<div>
<br /></div>
<div>
<div>
<div>
<span style="color: blue;">SELECT </span>*</div>
<div>
<span style="color: blue;">FROM </span>Customers </div>
</div>
<div>
<span style="color: #999999;">LEFT JOIN</span> Orders on Customers.CustomberNumber = Orders.CustomerNumber</div>
</div>
<div>
<span style="color: #999999;">LEFT JOIN</span> OrderDetails on Orders.OrderNumber = OrderDetails.OrderNumber</div>
<div>
<br /></div>
<div>
This query gives us all Customers with matching rows in the Orders table and then all OrderDetails matching with the Orders table. We could accomplish the same by running the following:</div>
<div>
<br /></div>
<div>
<div>
<div>
<span style="color: blue;">SELECT </span>*</div>
<div>
<span style="color: blue;">FROM </span>OrderDetails </div>
<div>
<span style="color: #999999;">RIGHT JOIN</span> Orders on Orders.OrderNumber = OrderDetails.OrderNumber</div>
</div>
<div>
<span style="color: #999999;">RIGHT JOIN</span> Customers on Customers.CustomberNumber = Orders.CustomerNumber<br />
<br />
We could also mix it up:<br />
<br />
<div>
<div>
<span style="color: blue;">SELECT </span>*</div>
<div>
<span style="color: blue;">FROM </span>OrderDetails </div>
<div>
<span style="color: #999999;">RIGHT JOIN</span> Orders on Orders.OrderNumber = OrderDetails.OrderNumber</div>
</div>
<div>
<span style="color: #999999;">LEFT JOIN</span> Customers on Customers.CustomberNumber = Orders.CustomerNumber<br />
<br />
This would give all rows in the Orders table with matching rows in the OrderDetails and Customers table.</div>
</div>
</div>
<div>
<br /></div>
<div>
A FULL Join will return all rows from both tables. Any row from one table that doesn't have a match in the other table will return all NULLS for the other table.</div>
<div>
<br /></div>
<div>
<span style="color: blue;">SELECT </span>*</div>
<div>
<span style="color: blue;">FROM </span>Customers</div>
<div>
<span style="color: #999999;">FULL JOIN</span> Orders on Customers.CustomberNumber = Orders.CustomerNumber</div>
<div>
<br /></div>
<div>
To read up more on SQL Join, go here: <a href="http://msdn.microsoft.com/en-us/library/ms187518(v=sql.105).aspx" target="_blank">Using Outer Joins</a></div>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-40287710285791888042013-06-20T14:49:00.000-07:002013-06-20T14:49:10.927-07:00GOTO in SQLI've previously talked about <a href="http://lantztechknowledge.blogspot.com/2013/06/ifelse-in-sql.html" target="_blank">control-of-flow language in SQL</a>. Another way to control the flow of your programs is using the <span style="color: blue;">GOTO</span>. <span style="color: blue;">GOTO </span>allows you to automatically jump to a particular portion of your code, while skipping other portions.<br />
<br />
Here is a good example of what GOTO would do:<br />
<br />
<span style="color: blue;">DECLARE </span>@Counter <span style="color: blue;">int</span>;<br />
<span style="color: blue;">SET </span>@Counter = 4;<br />
<span style="color: blue;">WHILE </span>@Counter < 10<br />
<span style="color: blue;">BEGIN </span><br />
<span style="color: blue;">SELECT </span>@Counter<br />
<span style="color: blue;">SET </span>@Counter = @Counter + 1<br />
<span style="color: blue;">IF </span>@Counter = 4 <span style="color: blue;">GOTO </span>Branch_One<br />
<span style="color: blue;">IF </span>@Counter = 5 <span style="color: blue;">GOTO </span>Branch_Two<br />
<span style="color: blue;">END</span><br />
<span style="color: blue;">Branch_One:</span><br />
<span style="color: blue;">SELECT</span><span style="color: #cc0000;"> 'Jumping To Branch One.'</span><br />
<span style="color: blue;">GOTO </span>Branch_Three;<br />
<span style="color: blue;">Branch_Two:</span><br />
<span style="color: blue;">SELECT </span><span style="color: #cc0000;">'Jumping To Branch Two.'</span><br />
<span style="color: blue;">GOTO </span>FINISH;<br />
<span style="color: blue;">Branch_Three:</span><br />
<span style="color: blue;">SELECT </span><span style="color: #cc0000;">'Jumping To Branch Three.'</span><br />
<br />
<span style="color: blue;">FINISH:</span><br />
<span style="color: blue;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT </span><span style="color: #cc0000;">'FINISH'</span><br />
<span style="color: blue;"><br /></span>
You declare your GOTO section by giving it a name and then following it with a ":". After you specify your section, you can then put in what you want to happen in that section.<br />
<br />
You can specify in your code that you want to go to that section by saying "GOTO" and then the section name. The above query gives the following results:<br />
<br />
4<br />
Jumping To Branch Two.<br />
FINISH<br />
<br />
Keep in mind that the program will continue to run from the current <span style="color: blue;">GOTO </span>onward if no other GOTO's are specified. If we remove the reference to "GOTO FINISH" in Branch_Two, it will then move to Branch_Three and then FINISH. Also, you can go to sections before or after the current section. For example, from Branch_Two, you can then jump back to Branch_One and from Branch_Three you can go to Branch_One or Branch_Two. Be careful, because if you aren't paying attention, you could put yourself in an infinite loop.<br />
<br />
To read more on <span style="color: blue;">GOTO</span>, go here: <a href="http://msdn.microsoft.com/en-us/library/ms180188.aspx" target="_blank">GOTO</a>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-83684137588463525002013-06-19T13:48:00.001-07:002013-06-19T13:48:50.742-07:00Question of the Day 2I've had my second Question of the Day presented on SQLServerCentral.com. To visit the question of the day, go here: <a href="http://www.sqlservercentral.com/questions/CHOOSE/99561/" target="_blank">SQL CHOOSE</a><br />
<br />
To re-read my post on SQL CHOOSE, go here: <a href="http://lantztechknowledge.blogspot.com/2013/05/sql-choose.html" target="_blank">SQL CHOOSE Post</a><br />
<br />
You can also join the discussion on the question by going here: <a href="http://www.sqlservercentral.com/Forums/Topic1464935-3381-1.aspx" target="_blank">SQL CHOOSE Discussion</a><br />
<br />
I'm certainly learning a lot about what needs to be considered when putting together these questions. With this question of the day, I've found out that people outside of the U.S. are answering. Because of this, they are using a different language for SQL Server than me, which apparently affects how different data is presented. In my case, dates are presented differently depending on the language you use and this caused more people to get unexpected errors. This is a good thing to consider in case you are working with people overseas.Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-9624683477084937092013-06-18T13:25:00.000-07:002013-06-18T13:25:27.263-07:00Using APIsI've been spending much of my time recently using APIs (application programming interface) at work, so I haven't had much time to research SQL topics. For any of you who don't know, APIs are used as a way into an application without providing the code/table diagrams underneath.<br />
<br />
For example, if I, as a programmer, want to give people access into my database, but not allow them in directly, I would give them access to an API. APIs are used for inserting data into the database or retrieving information without giving away what's in your database.<br />
<br />
Anyway, I've been spending time recently trying to use some of the APIs from our billing vendor to see if I can increase my work performance. I'll keep you informed of anything I think worthy of note.<br />
<br />
Thanks.Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-53078436311875006952013-06-13T14:57:00.000-07:002013-06-13T14:57:11.357-07:00SQL ViewsI've briefly spoken about SQL Views in prior posts (see <a href="http://msdn.microsoft.com/en-us/library/ms190174.aspx" target="_blank">here</a>), so I wanted to take this opportunity to talk a little more about SQL Views.<br />
<br />
First, what is a SQL view?<br />
<br />
A view is just exactly what it sounds like: it allows you to "view" information. Views can and are usually created to present data in a certain way. It is what we may call a virtual table, meaning that the table doesn't actually reside in the database the way it is presented in the view.<br />
<br />
A view allows you to take a T-SQL query, spanning across multiple tables if needed, and present it as a view. Then, someone can write a query using the view and get all the data they need. This helps to avoid writing the same query multiple times. The query is already written as a view.<br />
<br />
An example might help us better understand the concept. Let's say that we have three different tables: Customer, Orders and OrderDetails. We use the following query to get information for an order:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer<br />
<span style="color: #cccccc;">join </span>Orders <span style="color: blue;">on </span>Customer.CustomerId = Orders.CustomerId<br />
<span style="color: #cccccc;">join </span>OrderDetails <span style="color: blue;">on </span>Orders.OrderId = OrderDetails.OrderId<br />
<br />
It can sometimes be difficult to remember the relationship between each table, so instead of creating the same query over and over again, we create a view. We create a view using the following syntax:<br />
<br />
<span style="color: blue;">CREATE VIEW</span> [dbo].[vOrderDetail]<br />
<br />
<span style="color: blue;">AS</span><br />
<span style="color: blue;"><br /></span>
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>Customer<br />
<span style="color: #cccccc;">join </span>Orders <span style="color: blue;">on </span>Customer.CustomerId = Orders.CustomerId<br />
<span style="color: #cccccc;">join </span>OrderDetails <span style="color: blue;">on </span>Orders.OrderId = OrderDetails.OrderId<br />
<br />
Now, when you want to get the information for a customer order, your query can look like this:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">FROM </span>vOrderDetail<br />
<br />
To avoid confusion, it is a good practice to name your views differently from your tables. In my example, I put a lowercase "v" at the beginning of the view name so I know I am using a view.<br />
<br />
Once you've created your view, now you can give people access to views. These are used for just viewing data, users can't update or delete these records. The deletion or updating of records has to be done on the tables the view refers to.<br />
<br />
To read more on views, go here: <a href="http://msdn.microsoft.com/en-us/library/ms190174.aspx" target="_blank">SQL Views</a>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-73418581765120271902013-06-12T13:47:00.001-07:002013-06-12T13:49:42.551-07:00Querying NULL Values in SQLWhen querying a SQL database, you need to be aware of the presence of NULLs. In SQL, a NULL represents an unknown, non-existent or empty value. Sometimes the NULL value is put in explicitly or no values was specified for the column, so a NULL was entered. The reason we need to be careful is we cannot query NULLs the way we do for data types.<br />
<br />
For example, let's say that you wanted to query a customer's balance where it is zero (either the customer has paid his balance or he hasn't made any purchases). Since this would be a money or other number type, you could run a query, like so, to get all your customers:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">From </span>CustomerTable<br />
<span style="color: blue;">where </span>Balance = 0<br />
<br />
But, depending on how you set up your table, new customers may not have a value in the Balance field. Therefore, this value could be a NULL. The above query would not return a NULL value, so you aren't getting all the results you require.<br />
<br />
There are two ways you can update your query. Here is the first one:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">From </span>CustomerTable<br />
<span style="color: blue;">where </span>Balance = 0 or Balance IS NULL<br />
<br />
To query on NULL values, you need to specify IS NULL or IS NOT NULL. This is the only way to query NULLs directly.<br />
<br />
The other way to query NULLs is the following. Keep in mind that this will work, while also making your query <a href="http://lantztechknowledge.blogspot.com/2013/05/non-sargable-queries.html" target="_blank">NonSARGable</a>:<br />
<br />
<span style="color: blue;">SELECT </span>*<br />
<span style="color: blue;">From </span>CustomerTable<br />
<span style="color: blue;">where </span><span style="color: #e06666;">ISNULL</span>(Balance, 0) = 0<br />
<br />
<span style="color: #e06666;">ISNULL</span>() is a function that allows you to convert NULLs in your field to a specified value. <span style="color: #e06666;">ISNULL</span>() is used like this:<br />
<br />
<span style="color: #e06666;">ISNULL</span>( column, value to replace NULL)<br />
<br />
We replaced our NULLs with the value of 0. We can then query all 0s using one simple clause.<br />
<br />
<b>To read more on NULL values, go here: <a href="http://msdn.microsoft.com/en-us/library/ms191504(v=sql.105).aspx" target="_blank">NULL Values</a></b>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-82453313829026277172013-06-11T14:50:00.000-07:002013-06-11T15:40:13.445-07:00Index Design TipsI've written previously about <a href="http://lantztechknowledge.blogspot.com/2013/05/sql-indexes-and-their-benefits.html" target="_blank">SQL Indexes</a>. I've been reviewing tips on Index Design and came across the following Microsoft site:<br />
<br />
<a href="http://msdn.microsoft.com/en-us/library/ms191195(v=sql.105).aspx" target="_blank">General Index Design Guidelines</a><br />
<br />
A few things I thought were interesting in the article:<br />
<br />
<ul>
<li>A large number of indexes on one table can affect your Update, Insert and Delete statements.</li>
<ul>
<li>Keep the number of indexes or index columns small on these tables.</li>
<li>It may be a good idea to create a view and create indexes on the view rather than on the table that will receive Update, Insert of Delete. This would allow you to modify your original table, but view the data while using a cluster.</li>
</ul>
<li>Indexing small tables is not always optimal, since it may take longer to search through the indexes than to find the records.</li>
<li>Create nonclustered indexes on columns that are referenced frequently in your queries.</li>
<ul>
<li>Order the columns in your index based upon how often those columns are used. For example, if column c is used more than b and b is used more than a, then the column order in the index should be: c, b, and a.</li>
</ul>
<li>Columns that are referenced in query using exact matches could be a good candidate for clustered or nonclustered indexes.</li>
</ul>
<div>
These are all good pointers and should get any person inexperienced in creating indexes started. From there, you can usually play around with different indexes until you find the right ones.</div>
<div>
<br /></div>
<div>
<b>What types have you found while creating indexes on your database?</b></div>
<div>
<b><br /></b></div>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-31663181673330491372013-06-06T14:12:00.000-07:002013-06-20T13:37:00.278-07:00IF....ELSE in SQLEvery programming language has flow of control ability. What I mean by this is the language allows you to check your data and make a decision based on what is returned. One of the most popular ways to control the flow of your program is an IF/ELSE statement. An IF/ELSE statement allows you to check a value and IF it matches a certain criteria, do something, ELSE do something else.<br />
<br />
The basic look of an IF/ELSE statement looks like this:<br />
<br />
<span style="color: blue;">IF </span>expression<br />
do this<br />
<span style="color: blue;">ELSE</span><br />
do this<br />
<br />
Let's say that we are building a table of customer data. Before we continue, we want to check and see if we have any customers in our table. If we do, print one message or else we print another message:<br />
<br />
<span style="color: blue;">IF </span>(<span style="color: blue;">select </span><span style="color: #e06666;">count</span>(*) <span style="color: blue;">from </span>CustomerTable) > 0<br />
<span style="color: blue;">PRINT</span><span style="color: red;"> 'We have customers'</span><br />
<span style="color: blue;">ELSE</span><br />
<span style="color: blue;">PRINT </span><span style="color: red;">'We dont have customers'</span><br />
<span style="color: red;"><br /></span>
If your block statement after the IF or ELSE does more than one thing, you will need to insert a BEGIN and END at the beginning of each block. Below is an example:<br />
<br />
<span style="color: blue;">IF </span>(<span style="color: blue;">select </span><span style="color: #e06666;">count</span>(*) <span style="color: blue;">from </span>CustomerTable) > 0<br />
<span style="color: blue;">BEGIN</span><br />
<span style="color: blue;">PRINT</span><span style="color: red;"> 'We have customers'</span><br />
<span style="color: red;"> </span><span style="color: blue;">PRINT </span><span style="color: red;">'That is great'</span><br />
<span style="color: blue;">END</span><br />
<span style="color: blue;">ELSE</span><br />
<span style="color: blue;">PRINT </span><span style="color: red;">'We dont have customers'</span><br />
<span style="color: red;"> </span><span style="color: blue;">PRINT </span><span style="color: red;">'Isnt that sad?'</span><br />
<span style="color: blue;">END</span><br />
<span style="color: blue;"><br /></span>
These are very simple examples. You can do much more than print messages to the screen. You can also update data, delete data, select data, and much more. Below is one example:<br />
<span style="color: blue;"><br /></span>
<span style="color: blue;">IF (select </span><span style="color: #e06666;">count</span>(*)<span style="color: blue;"> from </span>CustomerTable <span style="color: blue;">) > 0</span><br />
<span style="color: blue;">BEGIN</span><br />
<span style="color: blue;"> delete from </span>CustomerTable<br />
<span style="color: blue;"> PRINT </span><span style="color: red;">'We just deleted our customers'</span><br />
<span style="color: blue;">END</span><br />
<span style="color: blue;">ELSE</span><br />
<span style="color: blue;">BEGIN</span><br />
<span style="color: blue;"> PRINT </span><span style="color: red;">'We dont have customers'</span><br />
<span style="color: blue;"> PRINT </span><span style="color: red;">'Isnt that sad?'</span><br />
<span style="color: blue;"></span><br />
<span style="color: blue;">END</span><br />
<span style="color: blue;"><br /></span>
To read more on IF/ELSE statements, go here: <a href="http://msdn.microsoft.com/en-us/library/ms182717.aspx" style="color: blue;" target="_blank">SQL IF/ELSE</a>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-13996197722755474562013-06-04T13:37:00.001-07:002013-06-04T13:37:05.386-07:00Using CONCAT in SQLIn my line of work, we regularly put different fields together for representation to our customers. There are two ways we can approach this in SQL:<br />
<ul>
<li>Concatenate the fields together using the "+" sign</li>
<li>Using the <span style="color: #e06666;">CONCAT</span>() function</li>
</ul>
<div>
Both of these approaches give the same results in most cases, so why should you use one solution over another? I would first say because of how it looks. For example, if we want to concatenate the first name and last name together, here is what our two solutions would look like:</div>
<div>
<br /></div>
<div>
Using "+" sign:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">SELECT </span>FirstName + <span style="color: red;">' '</span> + LastName <span style="color: blue;">from </span>Customers</div>
<div>
<br /></div>
<div>
Using <span style="color: #e06666;">CONCAT</span>() function:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">SELECT </span><span style="color: #e06666;">CONCAT</span>(FirstName, <span style="color: red;">' '</span>, LastName) <span style="color: blue;">from </span>Customers</div>
<div>
<br /></div>
<div>
This is a pretty simplistic example. If we use something with more fields, it may become more evident. For example, let's say that we have three different fields for a customer's phone number, we could put the three fields together like so:</div>
<div>
<br /></div>
<div>
Using "+" sign:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">SELECT </span><span style="color: red;">'('</span> + AreaCode + <span style="color: red;">')'</span> + CityCode + <span style="color: red;">'-'</span> + Suffix <span style="color: blue;">from </span>Customers</div>
<div>
<br /></div>
<div>
Or</div>
<div>
<br /></div>
<div>
Using <span style="color: #e06666;">CONCAT</span>() function:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">SELECT </span><span style="color: #e06666;">CONCAT</span>( <span style="color: red;">'('</span>, AreaCode, <span style="color: red;">')'</span>, CityCode, <span style="color: red;">'-'</span>, Suffix) <span style="color: blue;">from </span>Customers</div>
<div>
<br /></div>
<div>
Another reason to use the CONCAT() function is that integer values are automatically converted to string. If, for example, you put numbers together when using your "+" sign, SQL will add the two values together instead of concatenating them:</div>
<div>
<br /></div>
<div>
This returns the value of 3:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">SELECT </span>1 + 2</div>
<div>
<br /></div>
<div>
This returns the value of 12:</div>
<div>
<br /></div>
<div>
<span style="color: blue;">SELECT </span><span style="color: #e06666;">CONCAT</span>(1, 2)</div>
<div>
<br /></div>
<div>
In the first example, you will need to make sure you are converting integer values to a string, which could be quite cumbersome.</div>
<div>
<br /></div>
<div>
To read more on the <span style="color: #e06666;">CONCAT</span>() function, go here: <a href="http://msdn.microsoft.com/en-us/library/hh231515.aspx">SQL CONCAT</a></div>
Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-348257225429344292013-06-03T12:54:00.001-07:002013-06-03T12:55:53.910-07:00Rounding Up in SQLWorking in the telecommunications industry, you run into situations not always found in the real world. For example, when billing a call or applying taxes to an account, you always round up. We can't charge a customer anything less than 1¢ (since they can't pay less than 1¢), so you have to round up.<br />
<br />
SQL has a function that allows you to perform rounding on your values. This function is called <span style="color: #e06666;">ROUND</span>(), but only does true rounding (below 5 = round down, above 5 = round up). So using this function, how do we always round up?<br />
<br />
First, <b>we have to figure out how many decimal places we will be billing our customer</b>. Let's say, for example, that our customer's calls are rounded to the nearest two decimals. Once we know how many decimals we need to round, we can apply our logic. The logic is then to add 49 one digit past the decimal place you are trying to round. In this case, since we want to round to the nearest two decimal places, we will add $0.0049 to our value and then round. If we wanted to go three decimal places, we would add $0.00049, $0.000049 to four decimal places, etc.<br />
<br />
The following will round to $0.01:<br />
<br />
<span style="color: blue;">select </span><span style="color: #e06666;">ROUND</span>(0.013, 2)<br />
<br />
But if we want to always round up, we need to add $0.0049:<br />
<br />
<span style="color: blue;">select </span><span style="color: #e06666;">ROUND</span>(0.013+0.0049, 2)<br />
<br />
The above rounds to $0.02.<br />
<br />
Now, you might be asking yourself: <b>why do we add $0.0049 instead of $0.005</b>? Well, adding $0.005 might inadvertently makes us round higher than we want to go. Look below for an example, which will round up to $0.03:<br />
<br />
<span style="color: blue;">select </span><span style="color: #e06666;">ROUND</span>(0.020+0.005, 2)<br />
<br />
When we actually want it to stay at $0.02:<br />
<br />
<span style="color: blue;">select </span><span style="color: #e06666;">ROUND</span>(0.020+0.0049, 2)<br />
<br />
I've found it is more productive to add $0.0049 to the number and then round, as opposed to trying to figure out a way for SQL to round up only those values that need it.<br />
<br />
To read up more on rounding, go here: <a href="http://msdn.microsoft.com/en-us/library/ms175003.aspx">SQL ROUNDING</a>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-5777841540788012632013-05-29T14:06:00.002-07:002013-05-29T14:06:24.728-07:00Sending Email Using C#I thought I would change things up a bit today and actually talk about a little bit of C#. I currently use Visual C# at work for some projects and some of those include sending email. First, you need to make sure to add the <a href="http://msdn.microsoft.com/en-us/library/system.net.mail.aspx">System.Net.Mail namespace</a> to your C# project. This namespace gives you a list of different classes you can use to send email. Once the namespace has been added, here is the code I use to create a method to send email. It allows you to provide the body, subject and an attachment to the method:<br />
<br />
<span style="color: blue;">public static string</span> SendEmail(<span style="color: #6fa8dc;">String </span>body, <span style="color: #6fa8dc;">String </span>subject, <span style="color: #6fa8dc;">Attachment </span>attachment)<br />
{<br />
<span style="color: #6fa8dc;">SmtpClient </span>smtp = <span style="color: blue;">new </span><span style="color: #6fa8dc;">SmtpClient</span>(<span style="color: red;">"mail.companyname.com"</span>);<br />
<br />
<span style="color: blue;">string </span>fromEmail = <span style="color: red;">"my.email@companyname.com"</span>;<br />
<br />
<span style="color: #6fa8dc;">MailMessage </span>objEmail = <span style="color: blue;">new </span><span style="color: #6fa8dc;">MailMessage</span>();<br />
<br />
objEmail.IsBodyHtml = <span style="color: blue;">true</span>;<br />
<br />
objEmail.To.Add(<span style="color: red;">"to.email@companyname.com"</span>);<br />
<br />
objEmail.From = <span style="color: blue;">new </span><span style="color: #6fa8dc;">MailAddress</span>(fromEmail, <span style="color: red;">"My Name"</span>);<br />
<br />
objEmail.Subject = subject;<br />
<br />
objEmail.Body = body;<br />
<br />
objEmail.Attachments.Add(attachment);<br />
<br />
<span style="color: blue;">try</span><br />
{<br />
<br />
smtp.Send(objEmail);<br />
<br />
<span style="color: blue;">return </span><span style="color: red;">"true"</span>;<br />
<br />
}<br />
<br />
<span style="color: blue;">catch </span>(<span style="color: #6fa8dc;">Exception </span>exc)<br />
<br />
{<br />
<span style="color: blue;">return </span>exc.Message;<br />
}<br />
<br />
}<br />
<br />
If you wanted to create a method that sends an email, but doesn't include an attachment, use the following:<br />
<br />
<span style="color: blue;">public static string</span> SendEmail(<span style="color: #6fa8dc;">String </span>body, <span style="color: #6fa8dc;">String </span>subject)<br />
{<br />
<span style="color: #6fa8dc;">SmtpClient </span>smtp = <span style="color: blue;">new </span><span style="color: #6fa8dc;">SmtpClient</span>(<span style="color: red;">"mail.companyname.com"</span>);<br />
<br />
<span style="color: blue;">string </span>fromEmail = <span style="color: red;">"my.email@companyname.com"</span>;<br />
<br />
<span style="color: #6fa8dc;">MailMessage </span>objEmail = <span style="color: blue;">new </span><span style="color: #6fa8dc;">MailMessage</span>();<br />
<br />
objEmail.IsBodyHtml = <span style="color: blue;">true</span>;<br />
<br />
objEmail.To.Add(<span style="color: red;">"to.email@companyname.com"</span>);<br />
<br />
objEmail.From = <span style="color: blue;">new </span><span style="color: #6fa8dc;">MailAddress</span>(fromEmail, <span style="color: red;">"My Name"</span>);<br />
<br />
objEmail.Subject = subject;<br />
<br />
objEmail.Body = body;<br />
<br />
<span style="color: blue;">try</span><br />
{<br />
<br />
smtp.Send(objEmail);<br />
<br />
<span style="color: blue;">return </span><span style="color: red;">"true"</span>;<br />
<br />
}<br />
<br />
<span style="color: blue;">catch </span>(<span style="color: #6fa8dc;">Exception </span>exc)<br />
<br />
{<br />
<span style="color: blue;">return </span>exc.Message;<br />
}<br />
<br />
}<br />
<br />
These methods are pretty basic, since the email addresses are hard-coded. You will want to create your own method if your "to" email is dynamic or you want to pass the email values into the method.Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-75418530409707210862013-05-28T15:05:00.000-07:002013-05-28T15:05:30.803-07:00SQL CHOOSESQL Server has a list of logical functions that can be used on data. The first of these logical functions is the <span style="color: #e06666;">CHOOSE</span>() function. This function will return the value identified at the specified index. Here is what the syntax for the <span style="color: #e06666;">CHOOSE</span>() function looks like:<br />
<br />
<span style="color: #e06666;">CHOOSE</span>(index value, value 1, value 2, value 3, etc.)<br />
<br />
A pretty simple example of the CHOOSE() function looks like this:<br />
<br />
SELECT <span style="color: #e06666;">CHOOSE</span>(3, 'Executive', 'Senior Management', 'Vice President')<br />
<br />
The above would return 'Vice President', since the index for lookup is 3 and the third value is 'Vice President'.<br />
<br />
So how would a function like this be useful? Well, we could use it to list out the months of the year for an employee's birthday, like so:<br />
<br />
SELECT <span style="color: #e06666;">CHOOSE</span>(<span style="color: #e06666;">Month</span>(Birthday), <span style="color: red;">'January'</span>, <span style="color: red;">'February'</span>, <span style="color: red;">'March'</span>, <span style="color: red;">'April'</span>, <span style="color: red;">'May'</span>, <span style="color: red;">'June'</span>, <span style="color: red;">'July'</span>, <span style="color: red;">'August'</span>, <span style="color: red;">'September'</span>, <span style="color: red;">'October'</span>, <span style="color: red;">'November'</span>, <span style="color: red;">'December'</span>)<br />
FROM Employee<br />
<br />
How does this differ from what you might see using the <a href="http://lantztechknowledge.blogspot.com/2013/04/using-case-statements-in-your-select.html">CASE()</a> function? There isn't a difference. <span style="color: #e06666;">CHOOSE</span>() is just a short hand version of <span style="color: #e06666;">CASE</span>(). It definitely looks neater than <span style="color: #e06666;">CASE</span>(), since you don't have to do so much coding. Using the example above, <span style="color: #e06666;">CASE</span>() would look something like this:<br />
<br />
<span style="color: blue;">SELECT CASE </span><span style="color: #e06666;">Month</span>(Birthday)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>1 <span style="color: blue;">THEN </span><span style="color: red;">'January'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>2 <span style="color: blue;">THEN </span><span style="color: red;">'February'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>3 <span style="color: blue;">THEN </span><span style="color: red;">'March'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>4 <span style="color: blue;">THEN </span><span style="color: red;">'April'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>5 <span style="color: blue;">THEN </span><span style="color: red;">'May'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>6 <span style="color: blue;">THEN </span><span style="color: red;">'June'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>7 <span style="color: blue;">THEN </span><span style="color: red;">'July'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>8 <span style="color: blue;">THEN </span><span style="color: red;">'August'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>9 <span style="color: blue;">THEN </span><span style="color: red;">'September'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>10 <span style="color: blue;">THEN </span><span style="color: red;">'October'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>11 <span style="color: blue;">THEN </span><span style="color: red;">'November'</span><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><span style="color: blue;">WHEN </span>12 <span style="color: blue;">THEN </span><span style="color: red;">'December'</span><br />
<span style="color: blue;">FROM </span>Employee<br />
<br />
As you can see, much mode coding needs to be done, while also adding the value you are looking for. <span style="color: #e06666;">CHOOSE</span>() already knows the value you are looking for and selects it based on the order in the provided value list. Any value searched for that is not contained in the value list (such as 13 for our months) will return a NULL value.<br />
<br />
<b>How do you think you could use the <span style="color: #e06666;">CHOOSE</span>() function at your organization?</b><br />
<br />
To read up more on SQL <span style="color: #e06666;">CHOOSE</span>(), go here: <a href="http://msdn.microsoft.com/en-us/library/hh213019.aspx">SQL CHOOSE</a>Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0tag:blogger.com,1999:blog-1151622159551588954.post-88685908798586335922013-05-22T13:50:00.000-07:002013-05-22T13:50:08.294-07:00SQL SUBSTRINGThere may be reasons why you may only need part of the text and not the whole thing. For example, if you wanted to organize your customer list by first initial of their company name. How would you do this? SQL Server has a function called <span style="color: #e06666;">SUBSTRING</span>(). This function allows for you to specify your starting position and the number of digits you want to take. If you want the first digit of a customer's company name, you would write your query like this:<br />
<br />
<span style="color: blue;">SELECT </span><span style="color: #e06666;">SUBSTRING</span>(CompanyName, 1, 1), *<br />
<span style="color: blue;">from </span>CustomerList<br />
<br />
You could also use it to find out the Area Code of your customers, by getting the first 3 digits of their phone number:<br />
<br />
<br />
<span style="color: blue;">SELECT </span>SUBSTRING(PhoneNumber, 1, 3), *<br />
<span style="color: blue;">from </span>CustomerList<br />
<br />
<br />
Unlike other programming languages, SQL's <span style="color: #e06666;">SUBSTRING</span>() function is 1-based, while other programming languages are 0-based. This means that if you want the first digit in SQL, you use 1. With C#, for example, if you wanted the first digit, you would use 0.<br />
<br />
You can also run a query to see how many customers you have for each letter in the alphabet. This could be done by combining the <span style="color: #e06666;">SUBSTRING</span>() function with a group by, like so:<br />
<br />
<br />
<span style="color: blue;">SELECT </span><span style="color: #e06666;">SUBSTRING</span>(CompanyName, 1, 1), <span style="color: #e06666;">count</span><span style="color: #999999;">(*)</span><br />
<span style="color: blue;">from </span>CustomerList<br />
<span style="color: blue;">group by</span> <span style="color: #e06666;">SUBSTRING</span>(CompanyName, 1, 1)<br />
<span style="color: blue;">order by</span> <span style="color: #e06666;">SUBSTRING</span>(CompanyName, 1, 1)<br />
<br />
<br />
<a href="http://technet.microsoft.com/en-us/library/ms187748.aspx">SQL SUBSTRING()</a><br />
<br />
<b>What data could you see using the <span style="color: #e06666;">SUBSTRING</span>() function on?</b><br />
<br />Bill Lantzhttp://www.blogger.com/profile/03609596530936880915noreply@blogger.com0