• Loading
    • SQL Expressions

      Learning Objectives
      After completing this session, you will be able to:
      • Define simple and complex expressions
      • Define Case expressions
      • Define date time expressions
      • Define function expressions


      Understand the SQL Expressions

      An expression is a combination of one or more values, operators, and SQL functions that evaluates to a value. An expression generally assumes the datatype of its components.
      You can use expressions in:
      • The select list of the SELECT statement
      • A condition of the WHERE clause and HAVING clause
      • The CONNECT BY, START WITH, and ORDER BY clauses
      • The VALUES clause of the INSERT statement
      • The SET clause of the UPDATE statement


      SQL Expressions can be classified as
      • Simple Expressions
      • Compound Expressions
      • CASE Expressions
      • Date time Expressions
      • Function Expressions


      Simple Expressions

      A simple expression specifies a column, pseudo column, constant, sequence number, or null.
      Some valid simple expressions are as follows:
      • Daily_pipeline_loan_fact.loanno
      • 'this is a text string'
      • 10


      Compound Expressions


      A compound expression specifies a combination of other expressions. You can use any built-in function as an expression .

      In a compound expression, some combinations of functions are inappropriate and are rejected. For example, the LENGTH function is inappropriate within an aggregate function.

      Some valid compound expressions are:
      Code:
      ('CLARK' || 'SMITH') 
      LENGTH('MOOSE') * 57 
      SQRT(144) + 72 
      my_fun(TO_CHAR(sysdate,'DD-MMM-YY'))


      CASE Expressions


      CASE expression let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures.
      Code:
      CASE expr WHEN comparision_expr1 THEN return_expr1 
      [WHEN comparision_expr2 THEN return_expr2 
      WHEN comparision_exprn THEN return_exprn 
      ELSE else_expr] 
      END
      In a simple expression the Oracle server searches for the first WHEN…..THEN pair for which expr is equal to comparision_expr1 and returns return_expr1 .If none of the WHEN…..THEN pairs meet this condition and an else clause exists then oracle returns else_expr. Otherwise the Oracle server returns null.
      You cannot specify the literal null for all the return_exprs and else_expr.
      Example : Suppose, the ABC wants to give a interest rate of 15 percent on all personal loans ‘P’, a interest rate of 12 percent on home loan ‘H”, and a interest rate of 16 percent on all other loans. The code to retrieve the data is as follows:
      Code:
      SELECT loan_no,loan_amount, 
      CASE loan_type WHEN ‘P‘ THEN 15 
      WHEN ‘H‘ THEN 12 
      ELSE 16 
      END as interestrate 
      FROM daily_pipeline_loan_fact;

      Date and Time Expressions


      A datetime_value_expr can be a datetime column or a compound expression that yields a datetime value.

      Arithmetic With date:
      As the database stores dates as numbers, you can perform calculation using arithmetic operators such as addition and subtraction.

      Operation Result Description
      Date + number date Adds a number of days to a date
      Date - number date Subtract a number of days to a date
      Date - Date Number of days Subtract one date from another
      Date + number/24 date Adds a number of hours to a date.


      Example:
      Code:
      SELECT loanno,(SYSDATE – update_date)/7 AS weeks FROM daily_pipeline_loan_fact
      The earlier query will display the loanno and the number of week since when the loan is not updated.

      Interval Expression:
      An interval expression yields a value of INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND.

      INTERVAL YEAR TO MONTH:
      Stored as an interval of years and months.
      INTERVAL YEAR [(year_precision)] TO MONTH
      year_precision is the number of digit in the year date time field. The default value of year_precision is 2.
      Example
      Code:
      INTERVAL ‘312-2’ YEAR(3) TO MONTH 
      Indicates an interval of 312 years and 2 months. 
      INTERVAL ‘312’ YEAR(3) 
      Indicates an interval of 312 years and 0 months. 
      INTERVAL ‘300’ MONTH(3) 
      Indicates an interval of 300 months.

      INTERVAL DAY TO SECOND:
      The INTERVAL DAY TO SECOND data type stores a period of time represented as days, hours, minutes and seconds with a fractional part .
      Code:
      INTERVAL DAY [(year_precision)] TO SECOND [(sec_precision)]
      Year_precision is the number of digits in the DAY datetime field. The range of year_precision is 0 to 9. The default value is 2.
      sec_precision is the number of digits in the fractional part of the SECOND datetime field. The range of sec_precision is 0 to 9. The default value is 6.
      Example:
      Code:
      SELECT (SYSTIMESTAMP - update_date) DAY(9) TO SECOND 
      FROM daily_pipeline_loan_fact 
      WHERE loanno = ‘10001’;


      Function Expressions
      You can use any built-in SQL function or user-defined function as an expression. Some valid built-in function expressions are:
      • LENGTH('BLAKE')
      • ROUND(1234.567*43)
      • SYSDATE


      Summary
      • A simple expression specifies a column, pseudo column, constant, sequence number, or null
      • A compound expression specifies a combination of other expressions
      • CASE expression let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures


      Test Your Understanding

      • What are different types of expression?
      • Give an example of compound expression?
      • Give an example of date time expression?
      • Give an example of function expression?
      • What is a pseudo column?
    • Enter your email address:

      Delivered by FeedBurner

    • Categories




    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.