SQL: How to Use TO_CHAR in Views

A view in the SQL query of any RDBMS of primary need is to protect BASE TABLE data. The view can be created without using ORDER BY and with the ORDER BY clause.


However, in many other cases, a view usually solves the formatting of DATEs. Let me take an example SQL query:

Create View Order_V5 AS
SELECT    Order_Number, Customer_Number
 ,TO_CHAR(Order_Date , 'MON, DD, YYYY') AS Order_Date
 ,TO_CHAR(Order_Total, '999,999.99') AS Total
FROM Order_Table ;

When you give SELECT Query on the view, you can see converted DATES in the result:

Order_NumberCustomer_NumberOrder_DateTotal
12345622222222MAY, 04, 200012,347.53
12351255555555JAN, 01, 20018,005.91
12355231323134OCT, 01, 20025,111.47
12358587323456OCT, 10, 200315,231.62
12377757896883SEP, 09, 199923,454.84

Real Learnings from the Views

  • A view is a virtual table
  • A view may define a subset of columns
  • A view can even define a subset of rows if it has a WHERE clause
  • A view never duplicates data or stores the data separately
  • Views provide security

Top SQL View Advantages

  • An additional level of security is provided
  • Helps the business user not miss join conditions
  • Help control read and update privileges
  • Unaffected when new columns are added to a table
  • Unaffected when a column is dropped unless it’s referenced in the view

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.