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
Definition of FROM - SQL Recall that the operand of FROM is denoted by a commalist, each element of that commalist being a table expression optionally accompanied by a range v

The Package Body The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the p

COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.

ORIGIN OF EARTH - BIG-BAN G HYPOTHESIS - Origin of life is linked to origin of earth. Cosmos, the Universe originated 10-20 billion years ago by Big Bang (thermonu

Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty

We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

Procedural Constraint Enforcement (Triggers) SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures th

Effects of NULL for UNIQUE Specification When a UNIQUE specification u for base table t includes a column c that is not subject to a NOT NULL constraint, the appearance of sev

Project Description: This is stage 1 of a larger conversion project. We are converting a traditional Server/Client application written in Access 2007 into a web interface with S

Bitmap Join Indexes - This feature will increase the performance and detains the size and format of your databases in data Character Semantics and Globalization -This featur