# How to replace NULL in Infor SyteLine (SQL, IDO and reports)

> Source: https://netray.co/erp-help/syteline-replace-null-isnull-coalesce-sql-ido-reports
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

In SQL use ISNULL(column, default) or the standard COALESCE(a, b, default). In an IDO filter use IS NULL or IS NOT NULL, and in SSRS report expressions test with IsNothing or IIf. NULL is not equal to an empty string or zero, so each layer needs its own check.

## Kind

how-to

## System

syteline

## Erp

Infor SyteLine (CloudSuite Industrial)

## Module

SQL / IDO / Reports

## Query

infor syteline how to replace null

## Alternate Queries

- syteline isnull in sql view
- syteline ido filter is null
- syteline report expression null value

## Applies To

Infor SyteLine 8.x to CloudSuite Industrial on SQL Server, with Mongoose IDOs and SSRS reports.

## Steps

### Replace NULL where it appears

**Items:**

- In a SQL query or view: SELECT ISNULL(c.cust_num, 'N/A') or COALESCE(c.cust_num, c.alt_num, 'N/A').
- In arithmetic: ISNULL(qty, 0) before adding or multiplying, since any NULL in an expression makes the result NULL.
- In a comparison: use IS NULL, never = NULL. Use IS NOT NULL for the opposite.
- In an IDO filter on a form or call: write the filter as Column IS NULL or Column IS NOT NULL.
- In an SSRS expression: =IIf(IsNothing(Fields!qty.Value), 0, Fields!qty.Value) or =Fields!qty.Value + 0 for numbers after a Nothing check.
- In an outer join, move the filter on the right table into the ON clause or add OR col IS NULL so unmatched rows are not removed.
- Test with a row that is known to contain a NULL.
- Store the fix in the view or report, not in every consumer.

## Sections

### ISNULL versus COALESCE

**Body:**

ISNULL is SQL Server specific and takes two arguments. The result takes the data type of the first argument, which can truncate a longer replacement string. COALESCE is standard SQL, takes any number of arguments and returns the first non-null one.

For readability and portability prefer COALESCE, and use ISNULL when you want the first argument's type, for instance in older code.

**Code:**

SELECT j.job,
       ISNULL(j.qty_complete, 0) AS qty_done,
       COALESCE(j.description, i.description, 'No description') AS descr
FROM job j
LEFT JOIN item i ON i.item = j.item AND i.site_ref = j.site_ref;

### NULL in IDO filters and forms

**Body:**

- IDO filters are passed to the underlying SQL, so IS NULL works. Writing Col = '' only finds empty strings, which are different from NULL, and returns different rows.
- On forms, a blank field may be an empty string or a NULL depending on how it was saved. Check the data when a filter 'misses' rows that look blank.

### NULL in report expressions

**Body:**

- SSRS expressions fail or show #Error when arithmetic hits a Nothing value. Check with IsNothing first, and remember IIf evaluates both branches, so guard the risky branch.
- A better fix is to apply the COALESCE in the report dataset query or the SQL view, so every report sees clean numbers.

### Aggregates and joins

**Body:**

- SUM, AVG and COUNT(column) ignore NULLs. COUNT(*) counts rows. If a missing value should count as zero, convert it before averaging, or the average is overstated.
- Outer joins create NULLs by design, so check how they flow into the calculation.

## Pitfalls

- Writing = NULL or <> NULL, which is never true.
- Expecting an empty string to match IS NULL.
- Letting ISNULL truncate a longer replacement string to the first argument's length.
- Filtering on the right table in a WHERE after a LEFT JOIN and silently turning it into an inner join.
- Averaging a column with NULLs when missing values should count as zero.

## Ai Angle

SyteRay reviews a SyteLine query, view or report expression and points out where NULLs enter and where they break a total, then proposes the COALESCE fix and shows the corrected SQL for a developer to test. It can run on a local model against your own schema.

## Faq

### What is the SQL Server function to replace NULL?

**Answer:** ISNULL(expression, replacement) or COALESCE(expr1, expr2, ..., default). Both return the replacement when the expression is NULL.

### How do I filter for NULL in a SyteLine IDO filter?

**Answer:** Write the filter as ColumnName IS NULL, or IS NOT NULL for the opposite. Do not use equals NULL, which never matches.

### Why does my total show NULL?

**Answer:** In SQL any arithmetic with a NULL operand returns NULL. Wrap the nullable columns in ISNULL or COALESCE before adding or multiplying.

### Is a blank field in SyteLine NULL or empty?

**Answer:** It can be either, depending on how the row was saved. Test with both IS NULL and an empty-string comparison to see which rows you have.

## Search terms

syteline isnull, syteline coalesce, replace null sql server, syteline ido filter null, ssrs isnothing null, syteline sql view null

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json