no result by case when in count

[pepit084]

Hi there,

short example:

create table mylib.article (
name char(20), 
hasSpecAttr char(1)
);

insert into mylib.article (name, hasSpecAttr)
values('Article1', '1'), ('Article1', '0');                                                   

Following select works fine from any client I tested:

select name, count(case hasSpecAttr when '1' then 1 else null end) cnt
from mylib.article
group by name

But if I execute this statement with idb-connector I get an empty result.

Can someone check this issue or am I doing something wrong?

Thank you!

OS-Version: OS400 V7R3M0 Node-Version: v10.15.0 npm-Version: 6.4.1 loopback-Version: 3.25.0

[pepit084]

Do you need more information?

[jasonclake]

@pepit084 Has this ever worked for you? Or did it stop working after upgrade? I am using v7r2 node v8 -- We do not use loopback in our shop. So not exactly your setup, but I can get your example to work.

Although your example works for me -- I would recommend avoiding the use of null in counts.

-- avoid counting null with sum
sum(case hasSpecAttr when '1' then 1 else 0 end) cnt

[pepit084]

@jasonclake Thank you for trying! Nope! It never worked for me. Strange thing is even if I outsource this Sql-Statement to a view and do a select on that I get an empty result.

[jasonclake]

@pepit084 I checked again -- looks like I am also getting an empty set when trying to count with nulls.

The problem is in DbStmt::fetchData -- Could qualify as a potential bug since SQLFetch returns "Success" (0) or "Success with info" (1) and fetchData is only looking for 0. But then again, it may have been on purpose, how would javascript know you had "success with info"

Your original query returns with a 1 and I believe the "info" is a count with nulls eliminated message of some sort but I am not sure.

I'll file another issue specifically about the Success vs Success with info return from SQLFetch and reference this issue.

example program:

const {dbconn, dbstmt} = require('idb-connector');

function runsqlp(sql, conn) {
    return new Promise((resolve) => {
        let statement = new dbstmt(conn);
        console.log(`sql: ${sql}`);
        statement.exec(sql, (result, error) => {
            console.log(`result: ${JSON.stringify(result, null, '  ')}`);
            console.log(`error: ${error}`);
            statement.close();
            resolve(result);
        });
    })
};

async function test(){
    const connection = new dbconn();
    connection.debug(true);
    connection.conn('*LOCAL');

    console.log('This works---------------------------------------------');
    await runsqlp(`select name
    , sum(case hasSpecAttr when '1' then 1 else 0 end) cnt
    from jasontemp.article
    group by name`, connection);

    console.log('This does NOT work---------------------------------------------');
    await runsqlp(`select name
    , count(case hasSpecAttr when '1' then 1 else null end) cnt
    from jasontemp.article
    group by name`, connection);

    connection.disconn();
    connection.close();
}
test();

[dmabupt]

Fixed in v1.1.10. Close the issue.