Whenever i try to write some sql to retreive a report to display to a business user there comes a Question of Formatting. You can do it in 2 ways either by retreiving the data and then start formatting it respective language before creating report or use Database functions and Constructs to achieve this.
Of the above 2 options i prefer logic to be embeeded in SQL to the maximum extent as possible
- Because it increases the response time.
- It decreases dependency on Programming langugage.
Ofcourse there is a negative side of it . You are not much database agnostic. But i think we can live with it. As changing few hundred lines of SQL isn’t too hard than writing whole logic in new framework.
Listed are Few SQL Constructs We Would Love to Use in our Day to Day Life
- COALESCE, NULLIF
- CASE …. THEN ….ELSE …. END
- CONCAT, SUBSTR
I Would explain each of them with an Example.
The wiki definition of the above construct is “
COALESCE function accepts a list of parameters, returning the first non-Null value from the list”
In RealWorld when we generate reports we wouldn’t like to show business users values like null ..etc. There come the use of this. Lets case scenario where we store Unit_Price of each and every product we sell in a table and our business head requires a report of all products and their corresponding unit prices. But our database might be also containing products which are not yet launched or for which price i not yet determined. Here comes the use of COALESCE.
Here is sample Query SELECT PRODUCT_NAME,COALESCE(UNIT_PRICE,0) FROM PRODUCTS_TABLE;
This displays report where unit price is shown as ‘0’ for all those products whose price tag is not yet decided.
NULLIF :- This does the same function as above statement. But COALESCE is prefered as this is ubiqutious across DB Server
To Be Continued…