background preloader

SQL Development

Facebook Twitter

Oracle and SQL Server – Conditions In WHERE Clause vs. JOIN Clause – Why And When It Matters? Must Read! This post is about the various types of JOIN’s and how the conditions affect the results when placed in the JOIN clause versus the WHERE clause.

Oracle and SQL Server – Conditions In WHERE Clause vs. JOIN Clause – Why And When It Matters? Must Read!

Database programmers use OUTER JOINS in their job day in and day out. There are certain “gotchas” that you need to watch out for. One not only has to have a good understanding of when and how to use them but test extensively for various data conditions to make sure they produce the right results. If you are not familiar with OUTER joins, I recommend this article from CodeProject by C. L Moffatt that does a great job of explaining and doing a visual representation of the same. ANSI vs Non-ANSI: The article below applies to both SQL Server and Oracle. In SQL Server, the non-ANSI syntax for outer joins is either the “*=” or the “=*” operator and in Oracle the equivalent operators are “ (+)=” and “=(+)”. In Oracle, besides the fact that non-ANSI standard for the outer join is deprecated, the exact features supported is also different.

Employees: Software vorausschauend entwickeln. Wartet man lang genug, wird jeder Code Legacy.

Software vorausschauend entwickeln

Folglich stellt sich die Frage, wie er sich so schreiben lässt, dass er langfristig wartbar bleibt. Einer der wichtigsten Faktoren ist die Verständlichkeit. Ein geflügeltes Wort unter Entwicklern besagt, dass schwer verständlicher Code ein erstrebenswertes Ziel sei – schließlich werde so deutlich, wie schwer es war, ihn zu schreiben. Da Entwickler allerdings häufig in die Lage kommen, mit "fremdem" Code arbeiten zu müssen, sollte das Streben nach hoher Verständlichkeit im eigenen Interesse liegen. Der US-amerikanische Informatiker Donald E. Allzu oft scheitert der Vorsatz an (zumeist zeitlichen) Vorgaben des Kunden oder des Vorgesetzten. Die Aufgabenstellung verstehen Zunächst gilt es, das eigentliche Problem zu verstehen und zu dessen Kern vorzudringen.

Genügt es, das Format zu kontrollieren oder ist die IBAN auch inhaltlich zu prüfen? Empathie für den Anwender Als Funktionsname bietet sich daher etwas wie ibanUniform an. SQL Formatting. Sql coding standard sqlserver. Calculate the Sum of Salaries in an Employee Tree. In previous articles, I’ve discussed the treatment of specialized structures in SQL Server 2005 called graphs and trees.

Calculate the Sum of Salaries in an Employee Tree

Graphs are data structures that represent relationships between pairs of nodes. A tree is a graph that has a single root node, and you can reach each node in the tree structure by following only one path. An example of a tree is an employee organizational chart, which has many employees but only one CEO, and each employee has only one management chain. T-SQL in SQL Server 2005 lets you solve challenging problems related to the treatment of trees. For example, in "Get in the Loop with CTEs" (May 2004, InstantDoc ID 42072), I use the recursive common table expressions (CTEs) introduced in SQL Server 2005 to analyze employee hierarchies by manipulating data in a tree structure. Calculating the Sum of Salaries in a Tree To solve the challenge, first run the code in Listing 1 to create and populate the Employees table that I'll use in my examples throughout this article.

Introduction to Hierarchical Query using a Recursive CTE – A Primer. This blog post is inspired from SQL Queries Joes 2 Pros: SQL Query Techniques For Microsoft SQL Server 2008 – SQL Exam Prep Series 70-433 – Volume 2.

Introduction to Hierarchical Query using a Recursive CTE – A Primer

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza] What is a Common Table Expression (CTE) A CTE can be thought of as a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table while providing the same benefits to the user. However; a CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query. The basic syntax structure for a CTE is shown below:WITH MyCTEAS ( SELECT EmpID, FirstName, LastName, ManagerIDFROM EmployeeWHERE ManagerID IS NULL )SELECT *FROM MyCTE.