0

I don't want to create tmp tables. How to make this work?

with a as (
340401182,
340401183
)
select * from a
jim
  • 228
  • 4
  • 10

2 Answers2

4

If you want two rows and one column do this

with a as 
(
   Select 340401182 as val from dual
   UNION ALL
   Select 340401183 as val from dual
)
select * from a
Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
Chris Stewart
  • 333
  • 1
  • 8
  • You forgot the `FROM DUAL` in each part of the `UNION ALL` – Matthew McPeak Mar 08 '17 at 16:00
  • Matthew McPeak --I didn't read the question the same way you did. I didn't take "dual" to be a source object and at any rate, when specifying constants, the from clause doesn't add anything-- at least not in Transact-SQL – Chris Stewart Mar 08 '17 at 16:06
  • hmmm. I read "from dual" and assumed Oracle. Sorry, I may have jumped the gun. – Matthew McPeak Mar 08 '17 at 16:11
  • In some SQL dialects specifying a source object that doesn't exist would actually cause an error, as in PostGreSQL – Chris Stewart Mar 08 '17 at 16:13
  • I read dual to be shorthand for dual values, that was intended to fit the question into the subject line – Chris Stewart Mar 08 '17 at 16:14
  • `DUAL` is a table in Oracle that exists specifically for selecting values (constants or expressions) that do not exist in database tables. It is needed because, in Oracle SQL, the `FROM` clause is required. His mention of "dual" made me assume Oracle. I apologize for assuming too quickly. – Matthew McPeak Mar 08 '17 at 16:15
  • OOOOOOOOOOoooooh. You may well be right. I don't think he said what dialect he's using. I work in MS and PostgreSQL and neither of those requires a source object for constants. – Chris Stewart Mar 08 '17 at 16:22
1

Very close....

with a as (
SELECT 
    340401182 AS Col1,
    340401183 as Col2
)
select * from a
Milney
  • 6,253
  • 2
  • 19
  • 33