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
Create a view named CustomerAddresses that shows the shipping and billing addresses for each customer in the MyGuitarShop database. This view should return these columns from the

Use the PLS_INTEGER Datatype When you require to declare an integer variable, use the datatype PLS_INTEGER that is the most efficient numeric type. That is as the PLS_INTEGER

%NOTFOUND The %NOTFOUND is the logical opposite of the %FOUND. The %NOTFOUND yields TRUE when an INSERT, UPDATE, or DELETE statement affected no rows, or the SELECT INTO state

What is Cursors how to use it in Real time application ?

3CX PBX sync Windows Service Project Description: !! You require access to a commercial version of the 3CX PBX system in order to be able to program the API !! !! You requ

Due to an increase in overhead costs, the buying price of all items needs to be increased. Management wants to see a report before deciding how much each product will go up. Add to

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

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