Show DB2 Boolean True/False as Y/N in Qlickview

[#1]

Hello,

I have a Boolean field in my DB2 database that is Null, True or False. When selecting in DB2 I can select the True and False values with the next statement.

case when a.PPCO_IND_MUTTOT=X'01' then 'J' else

case when a.PPCO_IND_MUTTOT=X'00' then 'N' else

1. a.PPCO_IND_MUTTOT end end

How do I convert this statement in de LOAD section in Qlickview?

The Null value is converted by this statement

If ( IsNull ( PPCO_IND_MUTTOT ) = -1 , 'NULL' , 'OTHER' ) AS PPCO_IND_MUTTOTM

Anyone knows how to convert de True and False values?

Thanx!

Michel

[CELAMBARASAN]

You could use

Load *;

SQL Select *,

case when a.PPCO_IND_MUTTOT=X'01' then 'J' else

case when a.PPCO_IND_MUTTOT=X'00' then 'N' else

1. a.PPCO_IND_MUTTOT end end AS FieldName

FROM TableName

[tresesco]

Replace True/False with 1/0, like:

If(IsNull(PPCO_IND_MUTTOT) = -1,'NULL',If(PPCO_IND_MUTTOT= 1, 'JA', If(PPCO_IND_MUTTOT= 0, 'NEE', 'OTHER'))) AS PPCO_IND_MUTTOTM

[tresesco]

Replace True/False with 1/0, like:

If(IsNull(PPCO_IND_MUTTOT) = -1,'NULL',If(PPCO_IND_MUTTOT= 1, 'JA', If(PPCO_IND_MUTTOT= 0, 'NEE', 'OTHER'))) AS PPCO_IND_MUTTOTM

[MayilVahanan]

Hi

Its not like a value, then how can you able to check it.

Better you go with celambarasan post.

Load *;

SQL Select *,

case when a.PPCO_IND_MUTTOT=X'01' then 'J' else

case when a.PPCO_IND_MUTTOT=X'00' then 'N' else

1. a.PPCO_IND_MUTTOT end end

FROM TableName

[#6]

Once in Qlickview it's not a value anymore but in the database it is. I think the solution lies within the select statement.

[#7]

Hi,

When I add your statement into the select it looks like this:

SQL SELECT *,

// Option 1

case when a.PPCO_IND_MUTTOT=X'01' then 'J' else

case when a.PPCO_IND_MUTTOT=X'00' then 'N' else

a.PPCO_IND_MUTTOT end end

//Option 2

//CASE WHEN PPCO_IND_MUTTOT=X'01' THEN 'J'

//        WHEN PPCO_IND_MUTTOT=X'00' THEN 'N'

//       ELSE PPCO_IND_MUTTOT END

FROM PARTSPLAN.PPCO_CONDITIE

WHERE PPCO_EINDDATUM = '31-12-9999';

Both options give the next error.

ErrorSource: IBM OLE DB Provider for DB2, ErrorMsg: [DB2/NT64] SQL0104N  An unexpected token "*" was found following "SELECT ".  Expected tokens may include:  "?".  SQLSTATE=42601

[#8 · Accepted solution]

Hello,

Well the problem is solved. Unit4 helped me solve this boolean load problem.

The select is changed as follows:

All the needed fields have been summed up instead of using a *.

And for the boolean fields the Case When is added.

PPCO_IND_MUTTOT,

CASE WHEN PPCO_IND_MUTTOT=X'01' THEN 'J' ELSE    CASE WHEN PPCO_IND_MUTTOT=X'00' THEN 'N' ELSE  'NULL' END END AS PPCO_IND_MUTTOT,

In the load you also add the field PPCO_IND_MUTTOT even if this is a new field that does not exists in the source table and that looks like:

Load

PPCO_IND_MUTTOT, 

Now it works fine.

Michel