all_vw is a merge table with three tables all with identical structure

 

intraday

history

currentyear

---------------------------------------------------------------------------------------------

 

My merge tables do not return consistent results

 

I have tried to refresh statistics

 

ANALYZE sys. intraday (asofdate) MINMAX

ANALYZE sys. history (asofdate) MINMAX

ANALYZE sys. currentyear (asofdate) MINMAX

 

still same issue

 

the issue exists for a few dates that I checked, there are many days that do work on the merge table , all dates work on the view and the individual tables.

I thought it might be an issue with statistics then the conversion function made it work on the merge table , so now at a loss.

 

 

 

the following query on the merge table return no rows

---------------------------------------------------------------------------------------------

 

select fundid,sum(curdlttot)   as fxt

from vw  where asofdate='2017-10-02' 

group by fundid

 

The following does return data from the merge table albeit, much slower – after converting date value

---------------------------------------------------------------------------------------------

select fundid,sum(curdlttot)   as fxt

from vw  where asofdate='2017-10-02D' 

group by fundid

 

select fundid,sum(curdlttot)   as fxt

from vw  where asofdate= date_to_str(asofdate, '%Y-%m-%d') = '2017-10-02'

group by fundid

 

 

the followings query on the subtable of the merge table returns data with no issues or performance

---------------------------------------------------------------------------------------------

select fundid,sum(curdlttot)   as fxt

from currentyear where asofdate='2017-10-02' 

group by fundid

 

 

create view test_all as

select * from intraday

union all

select * from history

union all

select * from currentyear

 

 

select fundid,sum(curdlttot)   as fxt

from test_all  where asofdate='2017-10-02' 

group by fundid

 

 

 

 

 

Warren S. Master

Chief Technology Officer, CISO

TRG Management LP

280 Park Avenue - 30th floor/West - New York, NY  10017

Phone  212.984.3504

Email      warren.master@rohatyngroup.com 

Web       www.rohatyngroup.com

Linkedin www.linkedin.com/in/warrenmaster