Issue Overview: Referencing Aliased Columns in Nested Queries Causing "No Such Field" Errors

The core issue revolves around attempting to reference column aliases defined within the same SELECT clause before they are materialized. In SQLite, aliases declared in the SELECT list are not accessible to other expressions within the same SELECT clause due to the order of query execution. This leads to errors like "pmult: no such field" when trying to use these aliases in subsequent calculations. The problem is exacerbated by the use of correlated subqueries to fetch foreign table data (unit multipliers) and a misunderstanding of how JOIN operations interact with column visibility.

The stock table contains inventory items with foreign keys (recunit, pkgunit) referencing a units table that defines unit names (name) and conversion multipliers (multiplier). The goal is to display stock quantities in received units (e.g., kilograms) while storing them in base units (e.g., grams) and calculate conversion ratios between packaging and receiving units. The original query uses multiple subqueries to fetch unit names and multipliers, then attempts to divide two aliased multipliers (pmult/pkgmult), but pkgmult is undefined, and pmult is inaccessible due to scoping rules.

Possible Causes: Subquery Scoping, Alias Visibility, and Join Misconfiguration

1. Alias Visibility in SELECT Clause:
   Aliases like pmult and rmult are defined in the SELECT list using correlated subqueries. SQLite evaluates these subqueries independently and does not make their results available to other parts of the SELECT clause until the entire row is resolved. Thus, referencing pmult in (pmult/pkgmult) fails because pmult is not yet materialized.

2. Undefined Column pkgmult:
   The expression (pmult/pkgmult) references pkgmult, which is not defined anywhere in the query. This is likely a typo or misunderstanding of column names. The units table has a multiplier column, but no pkgmult field exists. The intended reference might be to a second instance of units.multiplier from a different join.

3. Inefficient Use of Correlated Subqueries:
   The query uses multiple correlated subqueries (e.g., (SELECT units.name FROM units WHERE units.id=stock.recunit) AS RecUnit) instead of explicit JOIN operations. This approach forces SQLite to execute separate subqueries for each row, increasing overhead and complicating column referencing.

4. Missing Table Aliases in Joins:
   The LEFT JOIN units ON stock.pkgunit=units.id clause joins the units table once for pkgunit, but the query also needs to join units again for recunit. Without aliasing these joins, the second reference to units.multiplier becomes ambiguous, leading to unexpected results or errors.

5. Misaligned Unit Conversion Logic:
   The physical interpretation of unit conversions (e.g., kg/L to g/mL) introduces domain-specific constraints. If the units table does not account for dimensional analysis (e.g., distinguishing mass from volume), calculated ratios like pmult/rmult may produce nonsensical values, even if syntactically correct.

Troubleshooting Steps, Solutions & Fixes: Correcting Alias References and Optimizing Joins

Step 1: Replace Correlated Subqueries with Explicit Joins

Instead of using subqueries to fetch units.name and units.multiplier, join the units table twice with distinct aliases for recunit and pkgunit:

SELECT 
  stock.id, 
  stock.name, 
  rec_unit.name AS RecUnit, 
  pkg_unit.name AS PkgUnit, 
  stocktype.name AS TypeName,
  rec_unit.multiplier AS rmult,
  pkg_unit.multiplier AS pmult
FROM stock
LEFT JOIN units AS rec_unit ON stock.recunit = rec_unit.id
LEFT JOIN units AS pkg_unit ON stock.pkgunit = pkg_unit.id
LEFT JOIN stocktype ON stock.type = stocktype.id;

This eliminates correlated subqueries, improves performance, and makes rmult and pmult available as direct columns.

Step 2: Fix Column References in Calculated Fields

The original query attempted to calculate (pmult/pkgmult), but pkgmult is undefined. Assuming the goal is to compute the ratio between packaging and receiving units, use the correctly aliased columns:

SELECT 
  ...,
  (pkg_unit.multiplier / rec_unit.multiplier) AS conversion_ratio
FROM ...

If pkgmult was intended to reference another column, ensure the units table contains that column or adjust the join logic accordingly.

Step 3: Validate Unit Conversion Logic

Ensure that the units.multiplier values are stored correctly. For example:

• If 1 kg = 1000 g, units.multiplier for kg should be 1000.
• If the receiving unit is kg (rec_unit.multiplier = 1000) and the packaging unit is g (pkg_unit.multiplier = 1), the ratio pkg_unit.multiplier / rec_unit.multiplier would be 0.001, which might not align with the intended calculation. Adjust the formula based on whether you need to convert from received to base units or vice versa.

Step 4: Handle Division and Data Types

Explicitly cast multipliers to REAL if integer division truncates results:

SELECT 
  ...,
  (CAST(pkg_unit.multiplier AS REAL) / rec_unit.multiplier) AS conversion_ratio
FROM ...

Step 5: Compose the Final Query

Combining all fixes:

SELECT 
  stock.id, 
  stock.name, 
  rec_unit.name AS RecUnit, 
  pkg_unit.name AS PkgUnit, 
  stocktype.name AS TypeName,
  rec_unit.multiplier AS rmult,
  pkg_unit.multiplier AS pmult,
  (CAST(pkg_unit.multiplier AS REAL) / rec_unit.multiplier) AS conversion_ratio
FROM stock
LEFT JOIN units AS rec_unit ON stock.recunit = rec_unit.id
LEFT JOIN units AS pkg_unit ON stock.pkgunit = pkg_unit.id
LEFT JOIN stocktype ON stock.type = stocktype.id;

This query avoids subqueries, correctly references aliased columns, and ensures valid unit conversions.