User Tools

Site Tools

A PCRE internal error occured. This might be caused by a faulty plugin

Sidebar

**HPL/SQL is included to Apache Hive since version 2.0** * [[home|Home]]\\ * [[why|Why HPL/SQL]]\\ * [[features|Key Features]]\\ * [[start|Get Started]]\\ * [[doc|HPL/SQL Reference]]\\ * [[download|Download]]\\ * [[new|What's New]]\\ * [[about|About]]

select

====== SELECT Statement ====== SELECT statement allows you to run queries. ===== SELECT TOP n ===== You can specify a SELECT statement with the TOP clause. HPL/SQL automatically converts it to LIMIT clause for Hive. **Example**: <code language="sql"> SELECT TOP 3 name FROM sales; -- SELECT name FROM sales LIMIT 3 is executed </code> **Compatibility**: Microsoft SQL Server. **Version**: HPL/SQL 0.3.7 ===== SELECT Without FROM Clause ===== You can specify a SELECT statement without FROM. HPL/SQL can automatically add the FROM clause using the table name defined by the [[configuration#hplsqldualtable|hplsql.dual.table]] option. By default, HPL/SQL uses //default.dual// table name. Make sure such table exists in the database and contains exactly one row and one column only. **Example**: <code language="sql"> SELECT unix_timestamp(); -- SELECT unix_timestamp() FROM default.dual is executed </code> **Compatibility**: Microsoft SQL Server, PostgreSQL, MySQL and Netezza. **Version**: HPL/SQL 0.3 ===== FROM TABLE (VALUES ... ) Clause ===== You can generate table rows using TABLE (VALUES ...) clause (row constructor). Syntax: <code language="sql"> FROM TABLE (VALUES row, ...) AS table_alias (col, ...) </code> **Example 1**: <code language="sql"> SELECT * FROM TABLE (VALUES 1, 2, 3) AS rn(rownum); rownum ------ 1 2 3 </code> **Example 2**: <code language="sql"> SELECT * FROM (VALUES (1,1), (2,2), (3,3)) AS rn(c1, c2); c1 c2 --------- 1 1 2 2 3 3 </code> Note that HPL/SQL transforms FROM (VALUES ...) clause to SELECT UNION ALL subquery before the execution in Hive: <code language=sql> SELECT * FROM (SELECT 1 AS c1, 1 AS c2 FROM default.dual UNION ALL SELECT 2 AS c1, 2 AS c2 FROM default.dual UNION ALL SELECT 3 AS c1, 3 AS c2 FROM default.dual) rn </code> You can specify the single row, single column dual table using [[configuration#hplsqldualtable|hplsql.dual.table]] option. **Compatibility**: IBM DB2, Microsoft SQL Server **Version**: HPL/SQL 0.3 See also: * [[select-into|SELECT INTO]]