MemberJunction / MemberJunction/MJ

Support arithmetic expressions in query template parameters and composition pass-throughs

Open
#2,298 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

## Summary

Two related gaps in the MJ SQL Query templating engine's parameter handling:

### 1. Arithmetic in template expressions

Expressions like `CAST({{ TargetYear }} - 1 AS NVARCHAR)` fail because the Nunjucks template outputs the raw parameter value and the surrounding SQL arithmetic doesn't get processed correctly when the parameter is used with a filter like `sqlString`.

**Current workaround**: Callers must pass a separate `PriorYear` parameter alongside `TargetYear`, which is redundant and error-prone.

**Desired behavior**:
```sql
-- This should work: render TargetYear as int, let SQL do the arithmetic
AND e.year = CAST({{ TargetYear }} - 1 AS NVARCHAR)
```

### 2. Arithmetic in composition pass-through parameters

The `{{query:"..."}}` composition syntax supports pass-through parameters like `(TargetYear=SomeParam)`, but only simple variable references. Expressions like `(TargetYear=TargetYear - 1)` are not supported.

**Current workaround**: Callers must define a separate parameter and pass it explicitly.

**Desired behavior**:
```sql
-- This should work: pass TargetYear - 1 to the dependency's TargetYear param
FROM {{query:"Golden-Queries/Membership/MSTA NAMS-DESE Member Bridge(TargetYear=TargetYear - 1)"}} bridge
```

## Use Case

A "Non-Returning Member District Switches" query needs to call a bridge query for `TargetYear - 1` (the prior year) while the outer query uses `TargetYear` (the current year). Without expression support, this requires a redundant `PriorYear` parameter that the caller must manually keep in sync with `TargetYear`.

## Impact

This affects any composition query where a dependency needs a parameter derived from the outer query's parameters rather than passed through verbatim.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.