Error while performing UNION operation

[akshah1997]

Hi team,

Performing any UNION operation is giving UnsupportedOperationException. We tried using UNION for same and different streams as well as with and without using types. It's giving the same error everytime.

1. select col1 from stream where col2=="abc" union select col1 from stream where col2=="xyz"
2. select col1 type "message1" from stream where col2=="abc" union select col1 type "message1" from stream where col2=="xyz"`

Can you please help with the same?

Thanks a lot!

[alex-karpovich]

select col1 from stream where col2 == 'abc' union select col1 from stream where col2=='xyz'

Character literals goes with single quote

[ankitsoin]

Hi @alex-karpovich ,
I have also tried same it is not working.
Ideally select * from stream union select * from stream this should work?right
Even if I select 2 or 3 columnsfrom stream and union again with same it is giving same exception(with no where clause).

Is giving java.lang.UnsupportedOperationException:

[alex-karpovich]

please use 6.1 version: https://github.com/finos/TimeBase-CE/tree/main-6.1

[ankitsoin]

Actually I am trying to backup/restore stream data but I am getting empty values for custom columns during restoring.
I have checked the .qsmsg file it is storing only symbol.
Only symbol and timestamp columns are getting restored correctly(fall back to Instrument message)

I am getting Warning [main] Bind error: type loader is unable to load class :
java.lang.ClassNotFoundException:

Can you please help me with the way of binding of my custom class and attributes with TimeBase?
(I am using qql for the stream creation)

[alex-karpovich]

1 way is to use "raw" mode to receive messages
2 way to check next sample to bound qql query to the message class
https://github.com/epam/TimeBaseTutorial/blob/main/java/src/main/java/com/epam/deltix/samples/timebase/basics/Step5_QueryData.java

In any case please take a look at whole tutorial
https://github.com/epam/TimeBaseTutorial

[ShivaniSK]

Hello @alex-karpovich ,
I am using UNION operation on 2 group by queries, and its giving compilation error.

select sum{} (volume) as vol from market_vol where SYMBOL == 'EURUSD' AND venue in ('ABC') GROUP BY timestamp UNION select sum{} (volume) as vol from market_vol where SYMBOL == 'EURUSD' AND venue in ('DEF','HIJ') GROUP BY timestamp

>>> Error: CompilationException: 1.133..142: CompilationException: Invalid Select expression type

The UNION works for normal select queries like below is working fine.

select venue, volume_millions from market_volume where SYMBOL == 'EURUSD' UNION select venue, volume_millions from market_volume where SYMBOL == 'EURUSD'

Can you please let us know if we are doing something incorrect here.

Regards,
Shivani

[alex-karpovich]

Hi @ShivaniSK

please use brackets to correctly group selects

(select ... group by ...)
UNION
(select ... group by ...)

to have correct order of timestamps please use reverse

( 
 SELECT sum{} (volume) as vol 
   FROM reverse(market_vol) 
   WHERE SYMBOL == 'EURUSD' AND venue in ('ABC') 
   GROUP BY timestamp 
)
UNION 
( 
SELECT sum{} (volume) as vol 
  FROM reverse(market_vol) 
  WHERE SYMBOL == 'EURUSD' AND venue in ('DEF','HIJ') 
  GROUP BY timestamp 
)

[ShivaniSK]

Hello @alex-karpovich , the above query with UNION and brackets gives below error. I am trying in version 6.1.14
>>> Error: (select: unrecognized command. (Type ? for usage)

[ShivaniSK]

Hi @alex-karpovich can you please help out here. Not sure if we are missing out anything but as pointed the UNION is not working for group by queries even after applying brackets as suggested by you.

[alex-karpovich]

if you are using TimeBase shell CLI

=> ?? ( 
 SELECT count{} () as count
   FROM reverse(coinbase) 
   WHERE SYMBOL == 'BTC/USD'
   GROUP BY timestamp 
)
UNION 
( 
SELECT count{} () as count
  FROM reverse(coinbase) 
  WHERE SYMBOL == 'LTC/USD'
  GROUP BY timestamp 
)
/

[ShivaniSK]

Thanks @alex-karpovich