Oracle SQL producing NULL values

vaisgaard — Mon, 19 Jun 2017 14:23:28 GMT

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

Re: Oracle SQL producing NULL values

sunny_talwar — Mon, 19 Jun 2017 15:37:08 GMT

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?

Re: Oracle SQL producing NULL values

vaisgaard — Mon, 19 Jun 2017 16:11:12 GMT

Hi Sunny,

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

Re: Oracle SQL producing NULL values

sunny_talwar — Mon, 19 Jun 2017 16:19:16 GMT

Which 3?

Re: Oracle SQL producing NULL values

dwforest — Mon, 19 Jun 2017 20:04:47 GMT

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

Re: Oracle SQL producing NULL values

vaisgaard — Tue, 20 Jun 2017 08:29:42 GMT

Hi Sunny,

These:

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

Re: Oracle SQL producing NULL values

vaisgaard — Tue, 20 Jun 2017 08:30:13 GMT

Hi David,

Thank you for the suggestion, I will try that.

topic Oracle SQL producing NULL values in App Development

Oracle SQL producing NULL values

Re: Oracle SQL producing NULL values

Re: Oracle SQL producing NULL values

Re: Oracle SQL producing NULL values

Re: Oracle SQL producing NULL values

Re: Oracle SQL producing NULL values

Re: Oracle SQL producing NULL values