0

is it possible to use an array of elements as a select statement? I know it is possiible to get rows based on static elements like this:

SELECT 405, CAST('4D6178' AS VARCHAR(32)), CAST('2017-01-01 00:00:00' AS TIMESTAMP) FROM rdb$databas

That will give you a table select with one row. Now I would like to get this as table with n rows, but I don't know how to achieve this. Due to the fact that firebird doesn't allow multiple select statements I cannot only append n times a selec.

Info : Firebird 2.1

Arioch 'The
  • 15,799
  • 35
  • 62
Franki1986
  • 1,320
  • 1
  • 15
  • 40
  • In your example every column (array item) has different type (int, varchar, timestamp) but all values in a column must be of the same type. So it is impossible to represent this "array" as a table with single column. – ain Jan 30 '18 at 08:44
  • Ah no, sorry for the confusion. This is not what I wanted to show. I wanted to show an example for getting values as row. For the single column it should be always the same type, this is clear to me. So in my example it would be a column of integers or a column of Timestamps. – Franki1986 Jan 30 '18 at 09:04

1 Answers1

2

Use UNION ALL clause.

https://en.wikipedia.org/wiki/Set_operations_(SQL)#UNION_operator

Select x,y,z From RDB$DATABASE
    UNION ALL
Select a,b,c From RDB$DATABASE
    UNION ALL
Select k,l,m From RDB$DATABASE

Notice however that this should only be used for small data. Firebird query length is limited to 64KB and even if that would not be so - abusing this method to inject lots of data would not be good.

If you really need to enter lot of similar (same structure) data rows - use Global Temporary Tables

The following discussion hopefully would give you more insights: https://stackoverflow.com/a/43997801/976391

Arioch 'The
  • 15,799
  • 35
  • 62
  • Ahhhh that's what I wanted!! Thanks another time for your investigation Arioch! Do you know if there is a limit for 'union all', I mean how many unions can I make with one request? – Franki1986 Jan 30 '18 at 09:09
  • @Franki1986 I already stated the limit in my answer. There also is official text about implementation limits on FB site. But - if you think about limits then you already do it wrong. This method not to be used for data of any significant size. If you need big data - use GTT or something else. Maybe External Tables, depends on your real task. – Arioch 'The Jan 30 '18 at 09:12
  • With Firebird 3, the query length is max 10MB, assuming either the new API is used, or if you're using an alternative driver that directly implements the wire protocol. – Mark Rotteveel Jan 30 '18 at 09:38
  • Could you explain the point with an alternative wire protocol? I mean how does it work in this situation? – Franki1986 Feb 01 '18 at 06:52
  • @Franki1986 in this case it does not - you're not using Firebird 3. Also, even if it would I would still suggest to not using this feature. If your data exceed 64KB - then plan proper GTT and use it – Arioch 'The Feb 01 '18 at 08:24