0

I'm using Firebird 2.5 or 3.0. I have a query that unions the same fields from two identical subqueries. I want to add a fixed value to a column of the second subquery so that the values of that subquery are guaranteed different from the same column values of the first subquery:

    select Q1.* from (select VDATE, VTIME, STAT, PRICURR, SECCURR from "DevHistory"
                      where DEVID = :ID and VDATE = :DAY1 order by VDATE, VTIME) Q1
    union all
    select Q2.* from (select VDATE, VTIME + 1440, STAT, PRICURR, SECCURR from "DevHistory"
                      where DEVID = :ID and VDATE = :DAY2 order by VDATE, VTIME) Q2;

VDATE is an integer julian date, VTIME is an integer number of minutes since midnight, and the :DAY1 and :DAY2 params are always 2 sequential day numbers, hence the 1440 constant for the second subquery.

The VTIME + 1440 syntax doesn't work, of course, but it's what I would like to accomplish. Is there a way to do this in the query? Or do I have to massage the data after retrieval?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
SteveS
  • 335
  • 1
  • 13
  • If :DAY1 and :DAY2 are different days then the VDATE returned by each SELECT will be different. – Brian Oct 03 '19 at 20:28
  • Yes. But I need the VTIME values to be unique. Raw VTIME values will always be between 0 and 1439; I need the VTIME values from first subquery between 0 and 1439, and from the second subquery between 1440 and 2879, so the value acts as the offset in a 2880-slot array of 2 days worth of data to be plotted on screen. – SteveS Oct 03 '19 at 20:57
  • 1
    `That VTIME + 1440 syntax doesn't work, of course,` why "of course" ? why doesn't it work? What is datatype of the `vtime` column and what if Firebird error message? Also, what about `dateadd` Firebird function ? – Arioch 'The Oct 04 '19 at 08:38
  • 1
    I agree with Arioch 'The. Why _"The VTIME + 1440 syntax doesn't work, of course"_? If `VTIME` is an integer as you say, then `VTIME + 1440` will work. You really need to clarify what you mean with 'doesn't work' (what doesn't work, how doesn't it work and if possible/relevant explain 'why' it doesn't work?). – Mark Rotteveel Oct 04 '19 at 09:56
  • @MarkRotteveel basically it has nothing about `UNION` and all about Derived Tables. See `select * from (select b, c + 50 from t where a = 2 and b = 8 order by b, c) as t2` leading to `Invalid command no column name specified for column number 2 in derived table T2`. IF only the error text was not so tightly withheld from us.... https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=a1d384c4c2775c35da08f71d593b22ad – Arioch 'The Oct 04 '19 at 17:08

1 Answers1

0

For the query as I entered it, the answer turns out to be that the VTIME field needs to be aliased in both subqueries, then the query will work. As entered, I get a -104 error on the VTIME + 1440 part, but if I change the first sub to VTIME as MyTime and the second to (VTIME + 1440) as MyTime, it works fine.

select Q1.* from (select VDATE, VTIME AS MyTime, STAT, PRICURR, SECCURR 
                  from "DevHistory"
                  where DEVID = :ID and VDATE = :DAY1 order by VDATE, MyTime) Q1
union all
select Q2.* from (select VDATE, (VTIME + 1440) AS MyTime, STAT, PRICURR, SECCURR 
                  from "DevHistory"
                  where DEVID = :ID and VDATE = :DAY2 order by VDATE, MyTime) Q2;

The wrapper selects are there because it complains about the order by clauses if I don't.

The 2 :ID params work with FireDAC, both get populated with the single passed device ID set in Params[0]. I haven't tried it in other DB libs (I have UniDAC and will try it there when I get a chance.)

SteveS
  • 335
  • 1
  • 13
  • 1
    Well, this is not answer to the question of yours. This is added detail to your answer, so you better edit your question and add details into the very question. Also, please, quote VERBATIM error as you get it, in its fullness. And.... remove select-from-select, you do not really need that extra redundant nesting :-) If anything, when we talk about `union`, only the first name alias should matter, the second, third and other queries are matched by position and their column names/aliases are ignored – Arioch 'The Oct 04 '19 at 11:46
  • Sorry, but it seems I really do need the nesting. If I remove the nesting, I get "Token unknown, line 3, column 1 UNION" error. If I remove the order by clauses, then the query works without the nesting... but I need the order by. Another question elsewhere on StackOverflow discusses this specific issue, and the answers there confirm that order clauses in subqueries need the nesting for Firebird. – SteveS Oct 04 '19 at 12:56
  • oh, this... but what about no-name order-by? by columns positions, not aliases? `....ORDER BY 1,2` for example? https://stackoverflow.com/a/31006757/976391 – Arioch 'The Oct 04 '19 at 14:55
  • OK, yes, using a column number in the order by clause allows both the nesting to be removed, and the order by to be moved out of the subqueries so there's only one sort operation on the whole union-ed dataset. – SteveS Oct 04 '19 at 16:20
  • it is big pity you did not provided verbatim Firebird error text neither when making original post, not even after two requests... You did not tell us what was the logical cause for the error making as guess... One more potential workaround (worse one, as it would not use indices) would be `....order by ...(vtime)` or even `....order by ...(0+vtime)` https://www.wisdomjobs.com/e-university/firebird-tutorial-210/general-form-of-order-by-clause-7812.html – Arioch 'The Oct 04 '19 at 16:38