7.30.6 When the scalar function CONVERT is added to an internal derived table

This section shows the cases in which the scalar function CONVERT is added to an internal derived table. This has the effect of increasing the number of scalar operations by one, and increasing the nesting of scalar operations by one.

When a set operation is specified in an internal derived table

The scalar function CONVERT is added to the selection expression so that the result will have the data type of the result of the set operation.

Example

Query using derived tables

SELECT "SN","KI"*1.08 AS "TAX" FROM 
(SELECT "NAME","PRICE","ORIGIN" FROM "PRODUCTS_A" 
  WHERE "PRICE">10000
 UNION ALL
 SELECT "NAME","PRICE"*0.8,"ORIGIN" FROM "PRODUCTS_B" 
  WHERE "PRICE">20000) AS "X"("SN","KI","GE")
WHERE "GE" IN('Tokyo','Osaka')

Expansion of derived tables

SELECT CONVERT("NAME",VARCHAR(100)) AS "SN",
       CONVERT("PRICE"*1.08,DEC(23,2)) AS "TAX" 
  FROM "PRODUCTS_A" 
 WHERE "PRICE">10000 AND
       "ORIGIN" IN('Tokyo','Osaka) 
 UNION ALL
SELECT CONVERT("NAME",VARCHAR(100)),
       CONVERT("PRICE"*0.8*1.08,DEC(23,2))
 FROM "PRODUCTS_B" 
 WHERE "PRICE">20000 AND
       "ORIGIN" IN('Tokyo','Osaka')

When all of the following conditions are met:
- An internal derived table that meets Condition 2 in Note #7 in 7.30.5 Summary of when derived table expansion is performed is expanded.
- The data type of the set function included in the query specification that operates the derived query is different from the data type of the result of the set function specified in the derived query.
The scalar function CONVERT whose result will have the same data type as the result of the set function included in the query specification that operates the derived query is added to the set function specified in the derived query.
Example:
- Query using a derived table (in the case where the data type of the "TEMPERATURE" column is DECIMAL(10,2))
```
SELECT "POINT","DATE",AVG("TEMPERATURE") AS "TEMPERATURE"
  FROM (SELECT "POINT","DATE",MAX("TEMPERATURE") AS "TEMPERATURE" 
          FROM "SENSOR_DATA" 
            GROUP BY "POINT","DATE") "V1"
    WHERE "DATE" BETWEEN DATE'2018-01-01' AND DATE'2018-12-31'
      GROUP BY "POINT","DATE"
```
- Expansion of derived tables
```
SELECT "POINT","DATE",CONVERT(MAX("TEMPERATURE"),DECIMAL(38, 30)) AS "TEMPERATURE"
  FROM "SENSOR_DATA"
    WHERE "DATE" BETWEEN DATE'2018-01-01' AND DATE'2018-12-31'
      GROUP BY "POINT","DATE"
```

To Page Top