topic SQL Select and Case in QlikView

SQL Select and Case

Tue, 28 Sep 2010 20:52:39 GMT

Hello:

I have one question, is possible use in qlikview "sql select" with "case"? Today i test this script without the "case" and work all perfect, when i add the "case" and reload the data, nothing happend, don't return data or errors.

Any idea?

Thanks in advance
Regards

César Estrada

SQL select
case
when len(month(fch1)) = '1' then cast(year(fch1) as char(4))+'0'+cast(month(fch1) as varchar(2))
when len(month(fch1)) = '2' then cast(year(fch1) as char(4))+cast(month(fch1) as varchar(2))
else '1234'
end,
year(fch2) YEAR, month(fch2) MONTH,
year(fch1) YEARVNC, month(fch1) MONTHVNC, h.mod,
h.cod, h.nro, fch2 as DATE, nrop as NUMPRO,
nomp as NAMPRO, text2 as TEXT
from I
left join h
on h.mod = i.mod
and h.cod = i.cod
and h.nro = i.nro
where i.accoun = '123456'
and h.fch = '09/28/2010';

SQL Select and Case

Tue, 28 Sep 2010 21:02:14 GMT

Did you try giving the field a name?

SQL select
case
when len(month(fch1)) = '1' then cast(year(fch1) as char(4))+'0'+cast(month(fch1) as varchar(2))
when len(month(fch1)) = '2' then cast(year(fch1) as char(4))+cast(month(fch1) as varchar(2))
else '1234'
end As Code,
...

EDIT: It looks like QlikView will create a name for the field if you don't add it. I verified that a case should work and I did one similar to yours that works fine. I only have Oracle DBs here, so I can't test the T-SQL syntax.

SQL Select and Case

Tue, 28 Sep 2010 21:11:54 GMT

Thanks!!!

SQL Select and Case

Tue, 28 Sep 2010 21:14:10 GMT

Oops, I just edited to suggest that the name wasn't the problem. Perhaps, your case was too long or had a QlikView reserved word or something that prevented QlikView from giving it a default name. Interesting.