Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vaisgaard
Contributor III
Contributor III

Oracle SQL producing NULL values

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

Best regards
Michael Vaisgaard
1 Solution

Accepted Solutions
dwforest
Specialist II
Specialist II

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

View solution in original post

6 Replies
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
Contributor III
Contributor III
Author

Hi Sunny,

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

Best regards
Michael Vaisgaard
sunny_talwar

Which 3?

dwforest
Specialist II
Specialist II

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

vaisgaard
Contributor III
Contributor III
Author

Hi Sunny,

These:

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

Best regards
Michael Vaisgaard
vaisgaard
Contributor III
Contributor III
Author

Hi David,

Thank you for the suggestion, I will try that.

Best regards
Michael Vaisgaard