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

Procedures - syntax, Procedures The procedure is a subprogram which can... Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

Package specification in pl/sql , Package Specification The package sp... Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

Transaction visibility, Transaction Visibility As the figure shows, th... Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

Why use cursor variables, Why Use Cursor Variables ? Primarily, you use... Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

Parameter and keyword description - packages, Parameter and Keyword Descrip... Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

Oracle PL SQL, I need to write one function and one procedure to query a Or... I need to write one function and one procedure to query a Oracle 10.1 DB using PL SQL. I have the schema and exact queries...along with work Ive started and a template to put the a

Deriving predicates from predicates in sql, Deriving Predicates from Predic... Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv

Keyword and parameter description - cursors, Keyword and Parameter Descript... Keyword and Parameter Description select_statement: This is a query which returns a result set of the rows. Its syntax is such that of select_ into_statement without the IN

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

Indeterminacy in sql, Indeterminacy in SQL Some SQL expressions are ac... Indeterminacy in SQL Some SQL expressions are actually not function invocations at all in the mathematical sense, being indeterminate-invocations operating on identical input