In ADS I used to be able to get a query result of constants that I would frequently add to another result as shown in the 3rd line of the query.  How would I go about this in EDB?  I mean I can do it but nothing seems as simple as what I had done before in this example.

SELECT GroupID, GroupDesc FROM groups
UNION ALL
SELECT 0,'All' FROM system.iota
ORDER by 1

Hello,

To be able to use the ORDER clause you will have to use a temporary table, like this:


SELECT * FROM
  (
  SELECT GroupID, GroupDesc FROM groups
  UNION ALL
  SELECT 0,'All' FROM system.iota
  ) AS TmpTbl
ORDER BY GroupID


--
Fernando Dias
[Team Elevate]


Às 15:08 de 20/09/2017, gbh100 escreveu:
> In ADS I used to be able to get a query result of constants that I would frequently add to another result as shown in the 3rd line of the query.  How would I go about this in EDB?  I mean I can do it but nothing seems as simple as what I had done before in this example.
>
> SELECT GroupID, GroupDesc FROM groups
> UNION ALL
> SELECT 0,'All' FROM system.iota
> ORDER by 1
>

My real question revolves around the line:

SELECT 0,'All' FROM system.iota

system.iota is not a real table but is simply used to plug constants into the result.  Can I do that in EDB

Please ignore my previous answer.
The correct ways are:

1. If you want  *1 row only* with the constants (0, 'All') :

SELECT * FROM
  (
  SELECT GroupID, GroupDesc FROM groups
  UNION ALL
  SELECT 0,'All'
  ) AS TmpTbl
ORDER BY GroupID


2. If you want one ocurrence of (0, 'All') *for each row* in "system.iota"  :

SELECT GroupID, GroupDesc FROM groups
UNION ALL
SELECT 0,'All' FROM system.iota
ORDER BY GroupID

OR, using temporary tables:

SELECT * FROM
  (
  SELECT GroupID, GroupDesc FROM groups
  UNION ALL
  SELECT 0,'All' FROM system.iota
  ) AS TmpTbl
ORDER BY GroupID


--
Fernando Dias
[Team Elevate]

<< system.iota is not a real table but is simply used to plug constants into the result.  Can I do that in EDB>>

Ok, then the answer is Yes - it's case 1 in my next post.

--
Fernando Dias
[Team Elevate]

Thanks, that's exactly what I was looking for . . .

I've just seen that there is a simpler answer:

SELECT GroupID, GroupDesc FROM groups
UNION ALL
SELECT 0 AS GroupID, 'All' AS GroupDesc
ORDER BY GroupID

--
Fernando Dias
[Team Elevate]