CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2005t-sql
kingrichard2005
kingrichard2005

Reputation: 7269

SQL - correctly use constants instead of table name in FROM clause

I'm working with soem code to find the product of two numbers in sql. I'm following this tutorial's example. What I would like to know is, what is the correct syntax to use when a list of constants is used in a select statement's FROM clause instead of a table name?

E.g.

SELECT EXP(SUM(LOG(num))) as product
FROM (VALUES (2),(3),(5),(7),(11),(13)) as NUMS(num)

This example keeps throwing the error

'Incorrect syntax near the keyword 'VALUES'.'

Again, I'm assuming this is possible, I'm just not sure what the correct syntax is. Thanks very much.

Upvotes: 1

Views: 229

Answers (4)

alun
alun

Reputation: 3441

There may be some shorter way but this certainly works:

SELECT EXP(SUM(LOG(num))) as product
from
(
select 2 as num
union all
select 3
union all
select 5
union all
select 7
union all
select 11
union all
select 13
) NUMS

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294367

Table Value Constructors are new to SQL Server 2008. If you're trying on SQL Server 2005, they won't work.

Upvotes: 0

mtazva
mtazva

Reputation: 1015

I think you've got to define your NUMS table using a UNION

Such as:

    SELECT EXP(SUM(LOG(NUMS.num))) as product
    FROM
    (
    select 2 as num
    UNION
    select 3
    UNION
    select 5
--you get the idea...
    ) as NUMS

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135838

That syntax is valid for SQL Server 2008+. You must be running against an earlier version.

Try it as:

SELECT EXP(SUM(LOG(num))) AS product
FROM (SELECT 2 
      UNION ALL
      SELECT 3
      UNION ALL
      SELECT 5
      UNION ALL
      SELECT 7
      UNION ALL
      SELECT 11
      UNION ALL
      SELECT 13) AS NUMS(num)

Upvotes: 3

Related Questions