# Infor LN and Baan 4GL Embedded SQL Select Generator

> Source: https://netray.co/tools/infor-ln-4gl-embedded-sql-select-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in a table, columns and a where clause and get a Baan / Infor LN embedded SQL select loop with host variables, index ordering and selectempty and selecterror handling.

## Category

developer

## Erp

Baan IV / Infor LN

## Audience

- LN developer
- Baan developer
- Technical consultant

## Tool

**Kind:** template

**Output:** code

**Language:** text

**Fields:**

#### Main table (logical code)

**Key:** table

**Type:** text

**Default:** tdsls401

**Help:** Logical table code without the leading t, e.g. tdsls401 (sales order lines).

#### Columns to select

**Key:** select_fields

**Type:** text

**Default:** tdsls401.orno, tdsls401.pono, tdsls401.item, tdsls401.qoor

#### Where clause

**Key:** where_clause

**Type:** text

**Default:** tdsls401.orno = :i.orno

**Help:** Use :variable for host variables declared in the script.

#### Function argument (host variable)

**Key:** host_var_decl

**Type:** text

**Default:** domain tcorno i.orno

#### Order by

**Key:** order_by

**Type:** text

**Default:** tdsls401._index1

#### Lock rows

**Key:** lock

**Type:** select

**Options:**

- | read only, no lock
- for update

**Default:** | read only, no lock

#### Row limit

**Key:** row_limit

**Type:** select

**Options:**

- | all matching rows
- as set with 1 rows

**Default:** | all matching rows

#### Join table (optional variant)

**Key:** join_table

**Type:** text

**Default:** tcibd001

#### Join condition

**Key:** join_condition

**Type:** text

**Default:** tcibd001.item = tdsls401.item

#### Columns from join table

**Key:** join_fields

**Type:** text

**Default:** tcibd001.dsca

**Blocks:**

#### Declaration

**Body:** |******************************************************************************
|* Embedded SQL on {{table}}
|******************************************************************************
declaration:

	table	t{{table}}		| main table
	table	t{{join_table}}		| only needed for the join variant

#### Select loop in a function

**Body:** functions:

function long read.{{table}}.rows({{host_var_decl}})
{
	long	row.count

	row.count = 0

	select	{{select_fields}}
	from	{{table}}
	{{lock}}
	where	{{where_clause}}
	order by {{order_by}}
	{{row_limit}}
	selectdo
		row.count = row.count + 1
		| the selected columns of {{table}} hold the current row here
		| with for update: change fields, then db.update(t{{table}}, db.retry)
	selectempty
		| no row matched the where clause
		row.count = 0
	selecterror
		| the query failed, log or report it
		row.count = -1
	endselect

	return(row.count)
}

#### Variant with a join

**Body:** function long read.{{table}}.with.{{join_table}}({{host_var_decl}})
{
	long	row.count

	row.count = 0

	select	{{select_fields}}, {{join_fields}}
	from	{{table}}, {{join_table}}
	where	{{where_clause}}
	and	{{join_condition}}
	order by {{order_by}}
	selectdo
		row.count = row.count + 1
		| {{join_fields}} is filled for the current {{table}} row
	selectempty
		row.count = 0
	endselect

	return(row.count)
}

#### Calling it

**Body:** | for example in a choice or field section of the session script
	long	lines.found

	lines.found = read.{{table}}.rows({{table}}.orno)
	if lines.found <= 0 then
		| nothing to process
	endif

**Notes:** Check the generated statement against the SQL programming chapter of the Infor ES Programmer's Guide for your Tools version. The lock and row limit options insert either the clause or a comment line, so the statement stays valid either way. With for update, commit with commit.transaction() in a UI or 3GL script, but never commit inside a DAL hook. The example function argument and the call assume an order number domain; change both if you change the where clause.

## Content

**Intro:** This generator writes Baan IV and Infor LN embedded SQL for 3GL and 4GL scripts: table declarations, a select with host variables, index ordering and a full selectdo, selectempty, selecterror, endselect loop. It also gives a join variant and a call example, so you can paste it into a UI script, DLL or DAL in Program Scripts / Libraries (ttadv2530m000).

**Sections:**

#### How to use it

**Body:** Enter the logical table code (tdsls401, not ttdsls401100), the columns you need, and a where clause that uses host variables with a leading colon, such as :i.orno. Pick whether rows are locked for update and whether only the first row is read. The output declares the table with the t prefix, wraps the select in a function that returns the row count, and adds a join variant against a second table such as item general data (tcibd001). Delete the variant you do not need. Select only the columns you use: a narrow column list is cheaper, and columns you did not select are not filled in the record buffer inside selectdo.

**Bullets:**

- Declare every table in the select with table t<code> in the declaration section
- Host variables are script variables or function arguments prefixed with a colon
- order by <table>._index1 orders by the table's first index

#### Embedded SQL versus database SQL

**Body:** Embedded SQL uses logical LN names. The database driver translates them into physical names: on SQL Server or Oracle, tdsls401 in company 100 is the table ttdsls401100 and the column orno is t_orno. That is why an ad hoc query in SQL Server Management Studio looks different from the 4GL code, and why you should not copy t_ names into a script. Embedded SQL runs in the current company of the session; a query across companies has to say so explicitly, and you should test that against your own version before relying on it. References between tables (for example item on the order line pointing to tcibd001) are always expressed with logical table and field names.

#### Worked example

**Body:** With the defaults the generator reads all lines of one sales order: select tdsls401.orno, tdsls401.pono, tdsls401.item, tdsls401.qoor from tdsls401 where tdsls401.orno = :i.orno order by tdsls401._index1. Inside selectdo, tdsls401.qoor holds the ordered quantity of the current line, so you can total it or test it. If the order has no lines, selectempty runs once and the function returns 0; if the query fails, selecterror runs and the function returns -1. Switch the row limit to as set with 1 rows when you only need to know whether a line exists, which avoids reading the whole set.

#### Common mistakes

**Body:** Forgetting the table declaration gives a compile error on the select. Using for update without a following db.update and commit leaves rows locked until the transaction ends. Calling other database actions on the same table inside a long selectdo loop can disturb the set you are reading; collect keys first if you must update many rows. Writing a where clause on non-indexed fields over a large table such as tdsls401 forces a full scan, so filter on the leading fields of an index when you can. Finally, never commit inside a DAL hook: the DAL runs inside the caller's transaction.

**Cta:** If your team wants to ask LN about sales orders and items in plain English, with the same logical tables and roles, we can run that on your own servers.

**Faq:**

#### What is the difference between selectdo and selectempty in Baan 4GL?

**Answer:** selectdo is the body that runs once for every row the select returns. selectempty runs once when no row matches. selecterror runs when the query itself fails. The loop closes with endselect. Put row processing in selectdo and the not-found handling in selectempty instead of testing a counter afterwards.

#### What does as set with 1 rows do?

**Answer:** It limits the result set to one row, so the database stops after the first match. Combined with order by on an index it gives you the first or, with descending order, the last record by that index. Use it for existence checks and lookups instead of reading a full set and breaking out of the loop.

#### Why do column names start with t_ in SQL Server but not in my script?

**Answer:** LN stores each logical table as a physical table named t, the table code and the company number, for example ttdsls401100, and prefixes every column with t_. Embedded SQL in scripts uses the logical names and the driver translates them. Only direct database queries outside LN use the physical names.

#### Is this useful for Infor LN technical training?

**Answer:** Yes, as a starting point. New LN technical consultants usually learn the select loop first because it appears in nearly every UI script, DLL and DAL. Generate a few variants, compile them in a test VRC and step through them with the debugger to see when each section runs.

## Search terms

baan 4gl, baan 4gl select statement, infor ln embedded sql, baan selectdo endselect, baan as set with 1 rows, infor ln technical consultant, infor ln technical training, baan sql for update

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