Example of cast operator - sql, PL-SQL Programming

Example of Cast Operator

So long as CAST is used as shown, we could obtain the total marks for each exam in similar fashion, using SUM (Mark) AS TotalMarks. However, this gives NULL, instead of zero, for the courses whose exams nobody sat. That problem can be addressed by using COALESCE, as shown in Example.

Example: Give the total of marks for each exam

WITH C_ER AS (

SELECT CourseId,

CAST (

TABLE (SELECT DISTINCT StudentId, Mark

FROM EXAM_MARK AS EM

WHERE EM.CourseId = C.CourseId )

AS ROW ( StudentId SID, Mark INTEGER) MULTISET)

AS ExamResult

FROM COURSE AS C)

SELECT CourseId, (SELECT SUM (Mark)

FROM TABLE(ER (ExamResult)) AS t) AS n

FROM C_ER

Explanation

  • COALESCE((SELECT SUM(Mark) FROM TABLE(ER(ExamResult)) AS t),0) yields the value of the scalar subquery whenever ExamResult is nonempty, otherwise zero. SQL's COALESCE is an n-adic operator that takes a commalist of expressions of the same declared type and yields the result of the first of those expressions, in the order in which they are written, that does not evaluate to NULL. When there is no such operand, it yields NULL anyway, of course.
Posted Date: 1/18/2013 6:38:29 AM | Location : United States







Related Discussions:- Example of cast operator - sql, Assignment Help, Ask Question on Example of cast operator - sql, Get Answer, Expert's Help, Example of cast operator - sql Discussions

Write discussion on Example of cast operator - sql
Your posts are moderated
Related Questions
Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

Example of Tables within a Table - SQL Example: Obtaining C_ER from COURSE and EXAM_MARK SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM

Declaring Exceptions The Exceptions can be declared only in the declarative part of the PL/SQL subprogram, block, or package. By introducing its name, you can declare an excep

Built-In Functions The PL/SQL provides a lot of powerful functions to help you to manipulate the data. These built-in functions fall into the categories as shown below: error r

Semijoin and Composition - SQL For semijoin, the dyadic relational operator MATCHING, defined thus: r1 MATCHING r2, where r1 and r2 are relations such that r1 JOIN r2 is de

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check

Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

Table Represents an Extension - SQL It describes how each tuple in a relation represents a true instantiation of some predicate and each true instantiation is represented by s

Parameter and Keyword Description: type_name: This identifies a user-defined type specifier that is used in the subsequent declarations of the objects. AUTHID Clause: