Drizzle TeamDT
Drizzle Team•7mo ago
loucass003

select with sql<number> returns string

Hi everyone!

I am new to drizzle and i have been trying to convert one of my sql queries to it
SELECT 
  customer,
  name,
  SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)::NUMERIC AS amount0_30,
  SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)::NUMERIC AS amount30_60,
  SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)::NUMERIC AS amount60_90,
  SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)::NUMERIC AS amount90_120,
  SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)::NUMERIC AS amount120
FROM (
  SELECT 
    "customer", 
    "name", 
    "amount", 
    (current_timestamp::date - "dueDate"::date) AS age
  FROM "Invoices"
) sub
GROUP BY customer, name ORDER BY customer ASC;


this is what i have been doing on the ts side

export interface AgingCustomer {
  customer: string;
  name: string;
  amount0_30: number;
  amount30_60: number;
  amount60_90: number;
  amount90_120: number;
  amount120: number;
}

 async getAgingList(): Promise<AgingCustomer[]> {
    const res = await this.db
      .select({
        customer: sql<string>`customer`,
        name: sql<string>`name`,
        amount0_30:
          sql<number>`SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)`.as(
            'amount0_30',
          ),
        amount30_60:
          sql<number>`SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)`.as(
            'amount30_60',
          ),
        amount60_90:
          sql<number>`SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)`.as(
            'amount60_90',
          ),
        amount90_120:
          sql<number>`SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)`.as(
            'amount90_120',
          ),
        amount120:
          sql<number>`SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)`.as(
            'amount120',
          ),
      })
      .from(
        sql`
          (
            SELECT 
              "customer", 
              "name", 
              "amount", 
              (current_timestamp::date - "dueDate"::date) AS age
            FROM "Invoices"
          ) sub
        `,
      )
      .groupBy(sql`customer, name`)
      .orderBy(sql`customer ASC`);
    console.log(res);
    typia.assert<AgingCustomer[]>(res);
    return res;
  }


the type of the const res does match the type i want as output but the actual data i get from it gives me string values for all the amounts.
I am really confused on why this happens.
Was this page helpful?