We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


SQL error: Invalid input syntax for integer — Vertica Forum

SQL error: Invalid input syntax for integer

I have the following projection definition:
CREATE PROJECTION fact_stat_adid_man_plat(
 AdvAccountId ENCODING RLE, 
 AdId ENCODING RLE, 
 TimeId ENCODING RLE,
 DateId ENCODING RLE, 
 PlatformId ENCODING RLE, 
 ManufacturerId ENCODING RLE, 
 Requests ENCODING DELTARANGE_COMP, 
 Impressions ENCODING DELTARANGE_COMP, 
 Clicks ENCODING BLOCKDICT_COMP, 
 Conversions ENCODING RLE, 
 AppSiteRevenue ENCODING BLOCKDICT_COMP, 
 Spend ENCODING BLOCKDICT_COMP, 
 AdFalconRevenue ENCODING BLOCKDICT_COMP
)
AS
 SELECT AdvAccountId, 
        DateId, 
TimeId,
        ManufacturerId, 
PlatformId,
AdId,
        Requests, 
        Impressions, 
        Clicks, 
        Conversions, 
        AppSiteRevenue, 
        Spend, 
        AdFalconRevenue
 FROM public.fact_stat 
 ORDER BY AdvAccountId,
        DateId,
        ManufacturerId, 
PlatformId,
        AdId,
TimeId 
SEGMENTED BY HASH (ManufacturerId, PlatformId) ALL NODES OFFSET 0;
When i try to execute the following query, it gives an invalid input syntax for integer "2014-10-01"
select dateid, timeid, adid, manufacturerid, platformid, sum(requests) as requests, 
sum(impressions) as impressions, 
sum(appsiterevenue) as revenue, 
sum(clicks) as clicks,
sum(spend) as spend,
sum(conversions) as conversions 
from fact_stat_adid_man_plat where dateid >= '2014-10-01' and dateid <= '2014-10-03' and advaccountid = 62130
group by  dateid, timeid, adid, manufacturerid, platformid   
order by  dateid, timeid, adid, manufacturerid, platformid   
limit 10 offset 0;
If you change the query to fetch from the anchor table "fact_stat", it uses the super projection and ignores the custom projection "fact_stat_adid_man_plat".. Bear in mind that this only occurs if i have the following super projection with the noted encoding schemes, if i change encoding to RLE in the super projection, everything will work fine, and the above query is executed without errors...
CREATE PROJECTION fact_stat_mysuper_comp(
 AdvAccountId ENCODING COMMONDELTA_COMP, 
 PubAccountId ENCODING COMMONDELTA_COMP, 
 AppSiteId ENCODING RLE, 
 CampaignId ENCODING COMMONDELTA_COMP, 
 AdGroupId ENCODING COMMONDELTA_COMP, 
 AdId ENCODING COMMONDELTA_COMP, 
 CountryId ENCODING COMMONDELTA_COMP, 
 RegionId ENCODING COMMONDELTA_COMP, 
 OperatorId ENCODING RLE, 
 ManufacturerId ENCODING BLOCKDICT_COMP, 
 DeviceId ENCODING BLOCKDICT_COMP, 
 PlatformId ENCODING COMMONDELTA_COMP, 
 DateId ENCODING RLE, 
 TimeId ENCODING COMMONDELTA_COMP, 
 Requests ENCODING DELTARANGE_COMP, 
 Impressions ENCODING DELTARANGE_COMP, 
 Clicks ENCODING BLOCKDICT_COMP, 
 Conversions ENCODING RLE, 
 AppSiteRevenue ENCODING BLOCKDICT_COMP, 
 Spend ENCODING BLOCKDICT_COMP, 
 AdFalconRevenue ENCODING BLOCKDICT_COMP
)
AS
 SELECT AdvAccountId, 
        PubAccountId, 
        AppSiteId, 
        CampaignId, 
        AdGroupId, 
        AdId, 
        CountryId, 
        RegionId, 
        OperatorId, 
        ManufacturerId, 
        DeviceId, 
        PlatformId, 
        DateId, 
        TimeId, 
        Requests, 
        Impressions, 
        Clicks, 
        Conversions, 
        AppSiteRevenue, 
        Spend, 
        AdFalconRevenue
 FROM public.fact_stat 
 ORDER BY 
          AdvAccountId,
          PubAccountId,
 DateId,
 CampaignId,
 AdGroupId,
 AdId,
          AppSiteId,
          OperatorId,
          CountryId,
          Conversions,
          Clicks,
          PlatformId,
          ManufacturerId,
          RegionId,
     TimeId 
SEGMENTED BY HASH (DateId) ALL NODES;
Does any body has an explanation for what is happening?

Thank you!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file