NOT NULL still retrieves NULL

Database

NOT NULL still retrieves NULL

Hi Friends,

Can somebody help me on the below issue that am facing while executing it. The result of the below query retrieves NULL value even when specified the filter IS NOT NULL.

SELECT VER_ID,MAX_ID

FROM RULE_TABLE

where CURRENT_TIMESTAMP > RULE_START_DT

and CURRENT_TIMESTAMP <= RULE_END_DATE

and MAX_ID IS NOT NULL

and  VER_ID = '8ac88041512601360151276f1c4c3f83'

GROUP BY 1,2 HAVING COUNT(1)>1





VER_ID MAX_ID
8ac88041512601360151276f1c4c3f83 ?
8ac88041512601360151276f1c4c3f83 001A000000ZnKSoIAN

But when i rewrite the query as below with additional filter as LENGTH(MAX) > 1, then is displays correctly.Even LENGTH(MAX_ID) > 0 will still give NULL values.

SELECT VER_ID,MAX_ID

FROM RULE_TABLE

where CURRENT_TIMESTAMP > RULE_START_DT

and CURRENT_TIMESTAMP <= RULE_END_DATE

and MAX_ID IS NOT NULL and LENTH(IMS_NUMBER) > 1

and  VER_ID = '8ac88041512601360151276f1c4c3f83'

GROUP BY 1,2 HAVING COUNT(1)>1





VER_ID MAX_ID
8ac88041512601360151276f1c4c3f83 001A000000ZnKSoIAN

Appreciate if somebody could give the details on why this happens and any query to check this issue. May be i suppose there should be some garbage or junk values in the source data.

Thanks

Sun

2 REPLIES

Re: NOT NULL still retrieves NULL

Try max_id> ''  instead of "MAX_ID is not null". Can you give more details? the table ddl and sample data

N/A

Re: NOT NULL still retrieves NULL

Are you sure that '?' is actually a NULL and not a literal question mark?

I usually display NULLs as <<NUL>> to avoid confusion...