However, I believe one technique you should always use while doing CASE is what I call the “escape” or “research” portion of the CASE (see second bolded below). Comment or remove those fields when you go to finalize the SQL (see first line bolded below). Itis a good idea to include the fields in the CASE when drafting the SQL – helps to identify how things are being handled. PROVIDING A WAY TO IDENTIFY MISSING INFORMATION/CODES. I have dozens of these in a Word Document so I can just copy them into SQL while writing, and that saves a lot of time!!! If you have any CASE you think would be helpful to others as “templates”, please do share with everyone and post it below as a Comment. You can use CAST and CONVERT and ISNULL and many other SQL parts in the CASES for Numeric Value below – I am keeping it simple right now, and am presenting simple CASEs. Some CASEs you write will need to check three or four data fields to come up with the correct displayed value (and it is important how you write that type of CASE in the way it is ordered) – doing three and four data fields is too much to do in this blog as it can get very complicated, depending upon what you are doing. You might need to change some of the CASEs given below, or you might want to modify the SQL to your own preferences. Just a reminder that this blog is to introduce a concept. Very handy when writing any kind of report for the End Users – just copy it in and onwards you go. I have a Word Document, about 8 pages long, with various CASEs used while writing SQL. All Marketing (Sales – A/R and Purchasing – AP) use the same data fields names such as DocStatus, so most of the below CASEs can be used as templates. Many of them are interchangeable with various tables. The CASEs in this blog are some commonly requested types of CASE Statements in the Forum. And you use END to let the SQL know you are finished with your IF/THEN lines. You must tell SQL first that you are about to do the IF/THEN by using the word “CASE”. What a CASE does is group together statements of “IF something is this THEN do this” (display a name, term, perform a calculation, display a value, etc.).
The concept can not only provide better descriptions, it can provide accurate values, handle exceptions correctly, can perform the correct calculation, and can also act as a filter when End Users (or yourself) are “slicing and dicing” the SQL results. Instead of having an “O” under a DocStatus column, you can display the word of “Open”. The CASE function in SQL is a great way to customize the output of your SQL to better suit the needs of your End Users. She has a great start, but we are going to get her up to the next level, and one way is going to be the usage of CASE in her new SQL. WHEN 0 THEN co.DTEntered >= ' 11:56:29.327'ĮLSE co.DTEntered BETWEEN ' 11:56:29.327' AND ' 11:56:29.Am currently working with an individual, hoping to help her improve her SQL skills. WHEN 0 THEN co.personentered = co.personentered LEFT JOIN stores s ON s.StoreNum = tl.StoreNum INNER JOIN transactionlog tl ON tl.transactionlogid=co.transactionlogid INNER JOIN customerstatus cs ON cs.customerstatuscd=ci.customerstatuscd INNER JOIN ccstatustype st ON st.ccstatustypeid=cc.ccstatustypeid INNER JOIN customerinfo ci ON cc.customerinfoid=ci.customerinfoid Can anyone please help me with the correct query? Here is the query:
But SQL Server 2008 is giving some errors while executing it. I am working with a query that contains the "CASE" statement within the "WHERE" clause.
0 kommentar(er)
