Mar 14, 2019 at 05:15 PM

Unexpected SELECT ... WITH ... result

172 Views Last edit Mar 14, 2019 at 05:22 PM 3 rev

Hi everyone!

I got some .hdbview with a several columns, and one of them is "VALUE" of Integer. Strange things happen when I try to filter that view. Let's say, I request somting like:

SELECT * FROM "SOME_VIEW" WHERE "VALUE" = '20'

As a result I got nothing, even though there a lot of records with "VALUE" = '20'. The problem takes plase with any value from "VALUE", but only with that specific column. It keeps even when I add another conditions not relaved to column "VALUE". And the form "VALUE" IN (20) also makes no sense. It also makes no sense if I use quotes ('20') or not (20). So if I filter column "VALUE" of my view with any single value it contains, HANA acts like there is no such records.

Howewer, when I add another condition to "VALUE" column, everything becames Ok, for example:

... WHERE "VALUE" IN ('20', '21')

returns records with 20 or 21 in "VALUE", and

... WHERE "VALUE" IN ('20', '-1')

returns records with 20 as there is no records with -1 in "VALUE".

The problem isn't appear if I create table as

CREATE COLUMN TABLE "SOME_TABLE" AS (SELECT * FROM "SOME_VIEW")

and then apply condition to "SOME_TABLE" instead of "SOME_VIEW". But I don't need table.

So is it me or HANA or some feature I'm not familiar with?