Replacing null from select result to default value

[elhananjair]

Hello there, I am trying to run multiple queries using sql.begin function, and some of the queries are undefined results, I want to replace those undefined with 0.

I tried this but had no success

   select COALESCE(status, 0) as count from mytable;

as a result when I try to loop into the results and access result[0].count I am getting Cannot read properties of undefined (reading 'count')

Am I missing something?

[porsager]

Is your table perhaps simply empty?

[porsager]

If you're not returning anything from sql.begin, that also explains it. Please post more complete code.

[elhananjair]

This is what my code looks like

let countDataOverview = 0;
let dash_data_name = ['c_amount', 's_sms', 'new_sub', 'new_unsub', 'm_total_sub', 'm_total_unsub', 'm_total_charge', 'smsc_stat', 'total_sub', 'total_unsub'];
let dash_data = {
    c_amount: 0,
    s_sms: 0,
    new_sub: 0,
    new_unsub: 0,
    m_total_sub: 0,
    m_total_unsub: 0,
    m_total_charge: 0,
    smsc_stat: 0,
    total_sub: 0,
    total_unsub: 0,
}

async function queryOverview(){
    return await sql.begin(sql => [
        /*
        todo -  Fetch different data for overview
       */
        sql`select count(distinct phone)::int from sms.delivery where date = cast(now() as date)`,
        sql`select count(distinct phone)::int from sms.sentsms where date = cast(now() as date)`,
        sql`select count(distinct phone)::int from subscription.sub where subdate = cast(now() as date)`,
        sql`select count(distinct phone)::int from subscription.unsub where unsubdate = cast(now() as date)`,
        sql`select count(phone)::int from subscription.sub where subdate between (SELECT date_trunc('month', now())::date) and (SELECT (date_trunc('month', now()) + interval '1 month - 1 day')::date)`,
        sql`select count(phone)::int from subscription.unsub where unsubdate between (SELECT date_trunc('month', now())::date) and (SELECT (date_trunc('month', now()) + interval '1 month - 1 day')::date)`,
        sql`select sum(charge)::int as count from sms.dailycharge where date between (SELECT date_trunc('month', now())::date) and (SELECT (date_trunc('month', now()) + interval '1 month - 1 day')::date)`,
        sql`select COALESCE(smsc_stat, 0) as count from smsc`,
        sql`select count(phone)::int from subscription.sub`,
        sql`select count(phone)::int from subscription.unsub`,
    ])
}

async function overAll(){
    try {
        overviewQ = await queryOverview();
        weeklyQ = await queryWeeklyCharge();
        overviewQ.map(result => {
            dash_data[dash_data_name[countDataOverview]] = result[0].count;
            countDataOverview++
        })
        console.log(weeklyQ)
        weeklyQ.map(result => {
            weekly_charge[weekly_name[countDataOWeekly]] = result[0];
            countDataOWeekly++
            console.log(result[0].charge)
        })
        sql.close();
    }catch (e) {
        console.log("There is an error reading from database, " + e.message)
    }
}
export const load = async ({locals}) => {
    if(!locals.user) {
        throw redirect(302, '/')
    }
    await overAll()
    return {
        dash_data,
        weekly_charge
    }
}

The table called smsc doesn't have a data just for testing purpose

[porsager]

Oh, but if it doesn't have data, isn't that your answer? Then the query returns no rows and result[0] will be undefined?

[porsager]

Besides from that, I think you could benefit from restructuring your code here to just be a single query using CTEs or subqueries, that way you could also ensure you'd always get 0 in case of no rows.

All that data mangling you're doing in js would be way more efficient + prettier in SQL

[elhananjair]

Oh, but if it doesn't have data, isn't that your answer? Then the query returns no rows and result[0] will be undefined?

In a real case, the application will set the column with 0, but I want to know more if it's possible to replace null or undefined with 0

[elhananjair]

Besides from that, I think you could benefit from restructuring your code here to just be a single query using CTEs or subqueries, that way you could also ensure you'd always get 0 in case of no rows.

All that data mangling you're doing in js would be way more efficient + prettier in SQL

CTE's, I have no clue about that, I will check it now. I am fetching data from multiple tables to show on my application's dashboard, just in case the query doesn't return anything I wanted to send 0 to the component using that value. That's why I used sql.begin, Can you give a hint please, using CTEs to check if the column has no rows and then make it 0 on the return, or if there is a way to replace results from sql.begin with 0 if undefined?

[elhananjair]

UPDATE: I found this query from https://copyprogramming.com/howto/how-can-i-return-0-from-query-in-pgsql-if-the-row-doesn-t-exist

I replaced

 sql`select COALESCE(smsc_stat, 0) as count from smsc`

with

sql`select coalesce((select smsc_stat from smsc), 0) as count`

and it returned 0 as I expected