[QODBC ALL] QODBC: Preserving Decimal (Currency) Format in Calculated or Alias Fields

Problem Description:

When building queries with calculated or alias fields based on numeric/currency columns (such as Amount, Debit, Credit, etc.), QODBC may return the result as a whole number instead of preserving the decimal (currency) format.

This commonly occurs with CASE expressions, arithmetic operations on numeric fields, or computed columns in report queries such as sp_report CustomTxnDetail.

Affected products: QODBC Desktop, QODBC Online, QuickBooks Desktop (all editions)
Expected outcome: Calculated numeric fields return decimal/currency values matching the precision of the source columns.

Symptoms:

• A SELECT query returning computed columns shows whole numbers instead of decimals for currency/numeric fields (e.g., $1,250.00 appears as 1250).
• The issue occurs specifically with alias/calculated fields - direct column reads from tables return correct decimal values.
• CASE expressions referencing the Amount field return whole numbers in report queries like sp_report CustomTxnDetail.

Cause:

QODBC's query engine may infer the data type of a computed/alias column from the source column's base type rather than its value precision. When a CASE expression or arithmetic operation is applied to an Amount field, the driver can resolve the result as an integer type instead of a decimal/currency type, stripping the decimal portion in the output.

This is a behavior in QODBC's data type inference - not a QuickBooks or SQL Server issue.

Resolution:

1. Multiply the numeric field by 1.000 to force decimal coercion.
   
   For example, instead of:

   CASE
     WHEN Account = '50800 · Direct Labor' THEN Amount
     ELSE 0END as LaborCost

   
   Use * 1.000 on the computed field:

   CASE
     WHEN Account = '50800 · Direct Labor' THEN Amount * 1.000
     ELSE 0END as LaborCost

2. Apply to all computed fields in the query. Every alias field derived from a numeric/currency column should use * 1.000 to preserve decimal format.
3. Test and verify. Re-run the query - calculated fields should now return values with full decimal precision.

SQL Examples:

Before (decimal lost):

SELECT TxnType, TxnDate, Amount,
  CASE
    WHEN Account = '50800 · Direct Labor' THEN Amount
    ELSE 0
  END as LaborCost
  FROM CustomTxnDetail

After (decimal preserved):

SELECT TxnType, TxnDate, Amount,
  CASE
    WHEN Account = '50800 · Direct Labor' THEN Amount * 1.000
    ELSE 0END as LaborCost
  FROM CustomTxnDetail

Example Scenarios:

Scenario 1 - CustomTxnDetail with labor/material split:

A user splits Transaction Detail by account type into LaborCost and MaterialCost using CASE expressions. Without * 1.000, both computed fields return whole numbers, losing cents values. Applying * 1.000 restores proper decimal precision on all rows.

Additional Notes:

• This is a QODBC-specific behavior. Standard SQL databases (SQL Server, PostgreSQL, MySQL) typically preserve decimal types in computed columns automatically. The * 1.000 workaround is specific to QODBC's data type inference.
• 1.000 does not change the value. Multiplying by 1.000 is mathematically equivalent - it only affects the output format, not the actual amount.
• Direct column reads are unaffected. This issue only occurs with computed/alias fields - reading Amount, Debit, or Credit directly from a table returns correct decimal values without any workaround needed.

Related Articles:

QODBC: Working with sp_report CustomTxnDetail SQL Query Syntax Reference for QODBC Best Practices for Building SQL Queries with QODBC

Keywords: