Oracle SQL producing NULL values

[vaisgaard]

Hi,

I have an issue with an SQL used in an Oracle database. When I use it in the Data Loader on Qlik Sense it doesn't produce any values, it just produces NULL values. The SQL works fine on the Oracle database.

I hope some of you fine people here can help me.

LOAD *

;

SQL select  FIRST_CHANNEL_GROUP,

        HOVED_PRODUKT_GRUPPE,

        nvl(BUDGET_PRODUCT, 'No Budget Product') as BUDGET_PRODUCT,

        sum(CASE WHEN report_type = 'Gained' THEN antal ELSE 0 END) as GROSS_INTAKE,

        sum(CASE WHEN report_type = 'Churn' AND report_type_kube = 'Churn' AND report_type_segm = 'Churn before 31 days' THEN antal ELSE 0 END) as ANNULLMENTS,

        sum(CASE WHEN report_type = 'Churn' AND report_type_kube = 'Churn' AND report_type_segm = 'Churn' THEN antal ELSE 0 END) as CHURN,

        sum(CASE WHEN report_type = 'Migrering TIL' THEN antal ELSE 0 END)-sum(CASE WHEN report_type = 'Migrering FRA' THEN antal ELSE 0 END) AS MIGRATIONS,

        (select nvl(budget, 0) from MPR_BUDGET where BUDGET_KPI = 'Activations (budget)' and YEAR_MONTH = '01-06-2017' and PRODUCT_SEGMENT = MPR_F0063_CUSTOMER.HOVED_PRODUKT_GRUPPE and PRODUCT_SUBGROUP = MPR_F0063_CUSTOMER.BUDGET_PRODUCT and CHANNEL = MPR_F0063_CUSTOMER.FIRST_CHANNEL_GROUP) as BUDGET,

        (select nvl(budget, 0) from MPR_BUDGET where BUDGET_KPI = 'Activations (forecast)' and YEAR_MONTH = '01-06-2017' and PRODUCT_SEGMENT = MPR_F0063_CUSTOMER.HOVED_PRODUKT_GRUPPE and PRODUCT_SUBGROUP = MPR_F0063_CUSTOMER.BUDGET_PRODUCT and CHANNEL = MPR_F0063_CUSTOMER.FIRST_CHANNEL_GROUP) as FORECAST_BUDGET,

        (select nvl(budget, 0) from MPR_BUDGET where BUDGET_KPI = 'Activations (reported)' and YEAR_MONTH = '01-06-2017' and PRODUCT_SEGMENT = MPR_F0063_CUSTOMER.HOVED_PRODUKT_GRUPPE and PRODUCT_SUBGROUP = MPR_F0063_CUSTOMER.BUDGET_PRODUCT and CHANNEL = MPR_F0063_CUSTOMER.FIRST_CHANNEL_GROUP) as REPORTED_BUDGET

from MPR_F0063_CUSTOMER

where YYYYMM = '201706'

group by FIRST_CHANNEL_GROUP, HOVED_PRODUKT_GRUPPE, BUDGET_PRODUCT

order by FIRST_CHANNEL_GROUP, HOVED_PRODUKT_GRUPPE, BUDGET_PRODUCT

It is BUDGET, FORECAST_BUDGET and REPORTED_BUDGET that only comes out as NULL

[sunny_talwar]

Are you seeing 0 rows come through or seeing multiple rows of data, but all with null values? Also, are you using any variables for the where clause?

[vaisgaard]

Hi Sunny,

I get 216 rows as expected, but the 3 columns are NULL in Qlik Sense.

[sunny_talwar]

Which 3?

[dwforest · Accepted solution]

My guess is Qlik is not properly handling nvl, could try using COALESCE or CASE to test/replace the nulls

[vaisgaard]

Hi Sunny,

These:

It is BUDGET, FORECAST_BUDGET and REPORTED_BUDGET that only comes out as NULL

[vaisgaard]

Hi David,

Thank you for the suggestion, I will try that.