Need help with SQL SELECT

[#1]

Hi all,

I need help with a SQL SELECT statement - here it is:

SQL SELECT creationdate as "Order created",
 id_case,
 model as "WL_model",
 "model_name_long",
 "model_name_short",
 service,
 status,
 "subsidiary_registered",
 repairdate

FROM "_lordwh_global".dbo."v_case";

Now i want to replace the id_case with something like that:

IF(ISNULL(Sum(id_case)),0,Sum(id_case))

How can I implement such a function in the SQL select?
I want to replace all NULL-values with a 0 from the id_case.

Thanks for help!

[#2]

It depends on the database, in Oracle you can use nvl(). Also if you use sum() you will need a group by as well

[#3 · Accepted solution]

Hello,

2 ideas:

a) What is your database? For example Oracle offers a function (nvl) which does exactly what you want: (SELECT nvl(id_case,0) ID from ... ).
So check the SQL functions for your data.

b) combine the SQL select with a load statement:

LOAD
if(isnull(id_case),0,id_case) as id_case,
<... other stuff ...>
;
SQL SELECT
id_case,
<... other stuff ...>
FROM ...;

hth,
Thilo

[#4]

Thank you so much!

That's exactly what i needed 🙂

[jonathandienst]

MS SQL Server has the COALESCE function, as in

COALESCE (id_case, 0)

which will return 0 is id_case is null, or id_case if it is not.

But if you want to get SUM(id_case) you will probably want some sort of GROUP BY clause (no matter what database server you're using).

Jonathan

[#6]

Now I'm having another Problem.

Because of the Data from column SR_Number, wheres no Data in column id_case, QlikView is joining the two tables and fills in the emtpy space where column id_case has no values where SR_Number has.

I did this if-clause in the Load script:

if(ISNULL(id_case),0,1) as id_case_null

I can only select the 1 (which means the id_case column has values and is NOT NULL), but i cant select the 0 (which should show only the data when theres no value (NULL) in id_case).

So, I didn't solve my problem 😞

[#7]

Hi HofbauerAn,

Try this and let me know the outcome:

isnull(Sum(id_case),0) as id_case

[#8]

Hi Rikab,

here's what I wrote in the script:

load
 *,
 ISNULL(SUM(id_case),0) AS id_case,
 1;
SQL SELECT creationdate as "Order created",
 id_case,
 model as "WL_model",
 "model_name_long",
 "model_name_short",
 service,
 status,
 "subsidiary_registered",
 repairdate

FROM "_lordwh_global".dbo."v_case";

I get the following error:

Error in expression:
IsNull takes 1 parameter

load
*,
ISNULL(SUM(id_case),0) AS id_case,
1

Thank you very much for your help.

[Miguel_Angel_Baeyens]

Hello,

You probably mean

load If(Len(id_case) > 0, 1, 0) AS id_case, // EDIT: ended correctly with ',' model as "WL_model", "model_name_long", "model_name_short", service, status, "subsidiary_registered", repairdate;SQL SELECT creationdate as "Order created", id_case, model as "WL_model", "model_name_long", "model_name_short", service, status, "subsidiary_registered", repairdateFROM "_lordwh_global".dbo."v_case";

I used "Len(id_case) > 0" on purpose, as I've found that sometimes isnull doesn't behave as expected.

If you only want to load those records where id_case is not null, you may do something like

load *WHERE Len(id_case) > 0;SQL SELECT creationdate as "Order created", id_case, model as "WL_model", "model_name_long", "model_name_short", service, status, "subsidiary_registered", repairdateFROM "_lordwh_global".dbo."v_case";

Hope that helps.

[#10]

Another Script Error again:

Syntax error, missing/misplaced FROM:
load
 *,
 IF(Len(id_case) > 0, 1, 0)) AS id_case,
 1
load
 *,
 IF(Len(id_case) > 0, 1, 0)) AS id_case,
 1

Like I said before - I only want to show the data in the table, where's no value in id_case.
Thank you very much for your help.
I really appreciate it.