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

Ask an Expert

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

Write your message here..

Related Questions

Assignment2, How do I display usernames for students from a student table, ... How do I display usernames for students from a student table, assigning each student a username initials001 (initials is the actual student initials), and if the students initials

Fetching with a cursor, Fetching with a Cursor The FETCH statements re... Fetching with a Cursor The FETCH statements retrieve the rows in the result set one at a time. After each and every fetch, the cursor advance to the next row in the result set

Best practices/Data Warhousing, What are 3 good practices of modeling and/o... What are 3 good practices of modeling and/or implementing data warehouses?

Packages, Packages The package is a schema object which groups logicall... Packages The package is a schema object which groups logically associated to the PL/SQL items, types, and subprograms. The Packages have 2 sections: the specification & the bod

Packaging cursors, Packaging Cursors   You can split a cursor specific... Packaging Cursors   You can split a cursor specification from its body for placement in a package. In that way, you can change the cursor body without changing the cursor spec

Sql queries, SELECT a.child_fname,a.child_lname,concat(b.parent_title,b.par... SELECT a.child_fname,a.child_lname,concat(b.parent_title,b.parent_fname), b.parent_lname,b.parent_tphone FROM child a,parent b WHERE a.parent_id=b.parent_id ORDER BY a.child_fnam

Assigning and comparing collections, Assigning and Comparing Collections ... Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A

Enforce security in the database system, Question: (a) In the context o... Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

Defining and declaring collections, Defining and Declaring Collections T... Defining and Declaring Collections To create the collections, you must define a collection type, and then declare the collections of that type. You can define the VARRAY types a

Scope and visibility- pl/sql, Scope and Visibility The References to an ... Scope and Visibility The References to an identifier are resolved according to its visibility and scope. The scope of an identifier is that area of a program unit (subprogram, b