how to use case statement in query using sqlbase database

[mcoolen01]

Hello,

I am trying to carry out a case statement in the SQL section using a SQLBase database but I cannot find the correct syntax.

Can anybody help me finding the correct syntax. What I want to do is concatenate a zero if the period is smaller than 10.

Example: If the periode is 9, I want to use 09.

I tried:

LOAD

     Bookyear,

     Period;

SQL Bookyear,

     Periode

     CASE

          WHEN period < 10

          THEN (0||periode)

          ELSE (period)

          END as Period

FROM CROPEN

Thank you for helping me with this issue.

[maxgro]

try

LOAD

     Bookyear,

     num(Period, '00') as Period

     ;

SQL Bookyear,

     Period

FROM CROPEN

     ;

[Oleg_Troyansky]

Hi Michel,

I'm not too sure about the exact SQLBase solution, but I could offer a QlikView solution that seems to be a lot more elegant. You can simply format the QlikView number to always carry two digits. Your script would look like this:

LOAD

     Bookyear,

     NUM(Period, '00') as Period

;

SQL

     Bookyear,

     Periode as Period

FROM CROPEN

;

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

[mcoolen01]

Hello Oleg,

Thank you for helping me out, I appreciate that.

The thing is that I need to load (sql) data smaller than e.g. year 2016 period 10.

So if I use (e.g.) 'WHERE period <= 10' in the sql section, I miss periods 11 and 12 for previous years.

That's why I thought to load all years and periods (sql) and than filter the loaded data in the load sector.

But I do not know how to do that.

An other solution is to use a if-then-else in my query, but I cannot find the correct syntax (SQLBase).

Greetz,

Michel

[Oleg_Troyansky]

Hi Michel,

I think you should look up the corresponding SQL syntax that will allow you formatting the Year-Month combinations using the SQL syntax. For example, something like this:

SQL FORMAT() Function

Think along the lines of formatting your data in a certain way, NOT along the lines of using CASE or IF statements to determine if the month is less than 10 - it will be way to slow...

cheers,

Oleg Troyansky