SQL Select and Case

[#1]

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';

[#2 · Accepted solution]

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.

[#3]

Thanks!!!

[#4]

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.