# Infor VISUAL SQL Query Generator

> Source: https://netray.co/tools/visual-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build a SQL Server query for open or late VISUAL work orders by filling a few fields, then copy it into your query tool.

## Category

developer

## Erp

Infor VISUAL

## Industry

Discrete Manufacturing

## Audience

- VISUAL administrator
- Report writer
- IT analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Work order status

**Key:** status

**Type:** select

**Options:**

- U
- F
- R
- C
- X

**Default:** R

**Help:** U unreleased, F firmed, R released, C closed, X cancelled.

#### Want dates up to (days from today)

**Key:** days_back

**Type:** number

**Default:** 0

**Help:** 0 means want date up to today, which finds late orders.

#### Part ID starts with

**Key:** part_filter

**Type:** text

**Placeholder:** Leave blank for all parts

#### Maximum rows

**Key:** top_n

**Type:** number

**Default:** 200

**Blocks:**

#### Late and open work orders

**Body:** -- Work orders by status with a want date on or before a cutoff
-- Verify table and column names against your VISUAL version before use
SELECT TOP {{top_n}}
    wo.BASE_ID,
    wo.LOT_ID,
    wo.SPLIT_ID,
    wo.SUB_ID,
    wo.PART_ID,
    wo.STATUS,
    wo.DESIRED_QTY,
    wo.RECEIVED_QTY,
    wo.DESIRED_QTY - wo.RECEIVED_QTY AS QTY_REMAINING,
    wo.DESIRED_WANT_DATE
FROM WORK_ORDER wo
WHERE wo.TYPE = 'W'
  AND wo.STATUS = '{{status}}'
  AND wo.PART_ID LIKE '{{part_filter}}%'
  AND wo.DESIRED_WANT_DATE <= DATEADD(day, {{days_back}}, GETDATE())
ORDER BY wo.DESIRED_WANT_DATE, wo.BASE_ID, wo.LOT_ID, wo.SPLIT_ID, wo.SUB_ID;

**Notes:** Table and column names shown (WORK_ORDER, BASE_ID, LOT_ID, SPLIT_ID, SUB_ID, PART_ID, STATUS, DESIRED_QTY, RECEIVED_QTY, DESIRED_WANT_DATE, TYPE) are common in VISUAL databases but can differ by version and customization. Run on a test or read only copy first, and never run ad hoc queries during peak transaction times.

## Content

**Intro:** Most VISUAL report requests start the same way: show me the open work orders that are late. This generator writes that SQL Server query for you from a few fields, so you can paste it into SSMS or a Crystal Reports command.

**Sections:**

#### How to use the generator

**Body:** Pick the status you want, set how many days from today the want date should be included, and optionally limit by the start of a part ID. The query appears on the right and updates as you type. Copy it into a query tool connected to a read only copy or reporting database. With a days value of 0 you get orders whose want date is today or earlier, which are your late orders. A positive value looks ahead, which is useful for a weekly shortage meeting.

#### How the query is built

**Body:** VISUAL identifies a work order by Base ID, Lot ID, Split ID and Sub ID, so the query returns and sorts on all four. The type filter keeps only work orders, since the same table can hold other records. Remaining quantity is desired minus received. The want date comparison uses DATEADD against the current date, which keeps the query reusable without editing dates by hand. The TOP clause protects you from accidentally returning an entire table.

**Bullets:**

- Join and sort on all four ID parts.
- Filter on type and status explicitly.
- Use DATEADD so dates are relative.
- Cap rows while testing.

#### Extending the query

**Body:** To see customer demand behind an order, join to the relevant customer order tables, or add operation and requirement tables to find the blocking material. Add one join at a time and re check row counts, because joins on one to many tables multiply rows and inflate totals. If you need cost, be careful to pick the right cost fields for your costing method, as covered in the VISUAL costing method reference.

#### Common mistakes

**Body:** The classic error is joining only on Base ID and merging lots and sub orders. Another is running heavy queries against the live database during production hours. A third is trusting column names from memory. Always check the schema of your own database, because customizations and versions change it. Treat the generated SQL as a starting draft that you review.

**Cta:** If you would rather ask questions like which work orders are late and why in plain English, with the SQL shown for review, we can set up a read-only assistant over your VISUAL database.

**Faq:**

#### Will this work on my VISUAL version?

**Answer:** The table and column names are common across many installations, but versions and customizations differ. Check your schema in SQL Server Management Studio before using the query, and test on a non production copy first.

#### Is any of my data sent to Netray?

**Answer:** No. The generator runs in your browser and only substitutes your entries into text. Nothing is executed against a database and nothing is stored on a server by this tool.

#### Why does the query sort by four ID columns?

**Answer:** A VISUAL work order is identified by Base ID, Lot ID, Split ID and Sub ID together. Sorting on all four keeps lots, splits and sub assemblies in a stable order and avoids confusing mixed results.

#### Can I use this query in Crystal Reports?

**Answer:** Yes, as a command or a view definition, though you may prefer to convert the literals into parameters. Keep the same ID columns, and test the report with a small row cap before publishing it.

## Search terms

visual sql query, infor visual work order sql, visual manufacturing sql server queries, visual open work orders query, visual late work orders sql, infor visual database tables, visual crystal reports sql, visual work order table

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