Counting NULL values in Oracle

Leave a comment

Howdy,
today I had the challenge to count null values in a orale table. At first I tried something like

SELECT
sum(field1 is null) null_counter
FROM
table

but this did not bring be very far.

also:

SELECT
sum(
case field1 
when null then 1
else 0
end
) null_counter
FROM
table

did not get me very far.

After some internet research I came across this neat little thingie:

SELECT
sum(
case nvl(field1,'null') 
when 'null' then 1 
else 0 
end
) null_counter
FROM
table

Oracle Date Territory

Leave a comment

Hi Folks,

I came across the problem that when using something like:

to_char(my_datefield,'D') as dow

to find out the date, this might behave differently on the pentaho production-server, the report designer and (if applicable) an underlying PDI transformation. When connecting to the Oracle-Server, you can click on “advanced” and set the locale to what you need – for example

ALTER SESSION SET NLS_TERRITORY = BELGIUM;

That way, your transformation/report will behave consistently across servers/environment.

 

Cheers

Andre