Can You Define "Literal" Tables in SQL

Can you define literal tables in SQL?

I suppose you could do a subquery with several SELECTs combined with UNIONs.

SELECT a, b, c, d
FROM (
    SELECT 1 AS a, 2 AS b, 3 AS c, 4 AS d
    UNION ALL 
    SELECT 5 , 6, 7, 8
) AS temp;

Are there Table Literals in Transact-SQL?

If you have SQL Server 2008, you can use it anywhere a derived table is allowed, although it only lets you have up to 1000 rows: http://msdn.microsoft.com/en-us/library/dd776382(SQL.100).aspx

Here's an example from the documentation ( http://msdn.microsoft.com/en-us/library/ms177634(SQL.100).aspx ):

SELECT *
FROM (
    VALUES (1, 2),
           (3, 4),
           (5, 6),
           (7, 8),
           (9, 10)
) AS MyTable(a, b)

Note the parentheses around the VALUES clause.

How to give a type to a literal?

No, there is not, if you don't count implicit conversions (i.e. DECLARE @x TINYINT = 3). Some types do have separate ways of writing literals (numeric, floating-point, binary, etc.), but this is not the case for the various integral types. The only way to have them distinguished is to explicitly convert constants (which would otherwise by default be of either INT or NUMERIC type).

You are correct that the documentation is lacking in this regard. Without a decimal point, the type depends on whether it'll fit in an INT:

SELECT SQL_VARIANT_PROPERTY(CONVERT(SQL_VARIANT, 0), 'BaseType')
-- int

SELECT SQL_VARIANT_PROPERTY(CONVERT(SQL_VARIANT, 2147483648), 'BaseType')
-- numeric

There is no way to get BIGINT out of this other than a conversion.

How to write a table literal in Oracle?

You can use collection type and TABLE operator, for example (works in Oracle 10g):

SQL> SELECT column_value FROM TABLE(SYS.ODCIVARCHAR2LIST('abc', 'def', 'ghi'));

COLUMN_VALUE
--------------------------------------------------------------------------------
abc
def
ghi

How to write an array literal in SQL?

if you wanta flexible list, y an use a type like this

create type t_inttable as table of int
/

create table tab_mydata (
  id         number(10),
  intlist   t_inttable
)
nested table intlist store as ntab_intlist

insert into tab_mydata values(
  1, 
  t_inttable(1,2,3)
)
/

select * from tab_mydata
/

ID | INTLIST
-: | :------

select t.id, x.column_value from tab_mydata t, table(intlist) x
/

ID | COLUMN_VALUE
-: | -----------:
 1 |            1
 1 |            2
 1 |            3

select t.id, x.column_value from tab_mydata t, table(intlist) x
where x.column_value like 3
/

ID | COLUMN_VALUE
-: | -----------:
 1 |            3

db<>fiddle here

How do you put literals into the result a sql result set based on which table was joined to create the row?

You can add:

CASE IF S1.ID IS NULL THEN 'SUBTYPE_1' ELSE 'SUBTYPE_2' END AS DISCRIMINATOR, 

at the start of your SELECT clause.

create a table from another and a literal

Use CAST(). "AS" means something else. Example:

CREATE TABLE TEST(ID INT, NAME VARCHAR);
INSERT INTO TEST VALUES(1, '10');
CREATE TABLE TEST2 AS 
SELECT CAST(ID AS VARCHAR) A, CAST(NAME AS INT) X FROM TEST;

MySQL multiple row literal select

SELECT 1,2,3
UNION ALL
SELECT 4,5,6;

---

Related Topics