conduit_width, which only equals the vertical dimension for circular pipes. For rectangular, egg-shaped, or arch sections, divide by conduit_height (the field that holds the full vertical dimension for every shape) instead — swap one field name below. Treat d/D as relative depth for non-circular shapes, not a literal diameter ratio, and don't read d/D alone as proof of surcharge without checking the model's actual hydraulic state.DURATION(tsr.flooddepth > 0) aggregate returning minutes outright — see the Time Varying SQL reference in the official 03 Context folder — but COUNT works on all versions and shows the mechanism.SUM(tsr.us_flow) and labeled it volume — dimensionally wrong unless every timestep has the same duration. INTEGRAL() multiplies each value by its timestep length before summing, which is what volume actually requires.AVG(IIF(cond, tsr.x, NULL)) pattern used elsewhere on this page: the WHEN clause restricts any aggregate to the timesteps where a condition holds, without nesting IIF. Confirmed against the official ICM Time Varying SQL reference. WHEN fields: tsr.timestep_no (1-based), tsr.timestep_start, tsr.timesteps, tsr.timestep_duration, tsr.sim_start, tsr.sim_end. Date literals use #dd/mm/yyyy hh:mm#.02 SWMM/0004 - Peak Sustained Depth (Rolling Average) script, which is itself the clearest real-world demonstration of the WHEN clause in the whole collection.COUNT(tsr.x > 0) * $dt_minutes pattern used earlier on this page: DURATION stays correct even with variable timestep lengths, where COUNT×dt silently breaks.SUM() which just adds ordinates and is only meaningful when every timestep happens to be the same length. The SQL engine doesn't know units, so the ×60 to convert m³/s·min into m³ is the user's responsibility. Caveat: AVG, INTEGRAL, and SUM all ignore the simulation's final timestep, since its duration isn't well-defined.RINDEX assigns each value a bucket index against a sorted list; TITLE(n, list) turns that index into a human-readable range label. TITLE() is easy to miss — it's absent from the widely-circulated Autodesk SQL function inventory, yet it appears in twelve scripts across the InfoWorks and SWMM folders and is documented in the repo's own 03 Context Function Reference. Without it you end up hand-writing a sixteen-deep nested IIF to label buckets; with it, one GROUP BY does the whole job.TITLE(), there's a specific way to get it silently wrong — and it produces a table that looks perfectly plausible while every row is mislabelled by one bucket. The rule, per the official Function Reference: RINDEX returns 0 when the value is below the first list element, 1 when it's ≥ first but < second, and n when it's ≥ the last of n elements. So whether index 0 means "below the smallest bucket" or "the smallest bucket" depends entirely on whether your list starts at zero.COUNT(prefix.*) rather than counting a specific field. Per the official Function Reference, COUNT behaves in three contexts: array rows (COUNT(details.*) = total rows), one-to-many links (COUNT(us_links.*) = number of upstream links), and GROUP BY (COUNT(*) = group size). Counting a named field instead works but silently skips rows where that field is NULL — .* avoids that trap.LOOKUPFN(expr, clamp, step, list) does piecewise-linear (or stepwise) x→y interpolation against a flat list of x,y pairs — a real alternative to manually querying an HD table or rating curve for one interpolated value. clamp=true holds the output at the endpoint value for out-of-range input instead of returning NULL; step=true switches from linear interpolation to stepwise (holds the previous y until the next x).LIKE uses DOS-style wildcards (*/?), and has a sharp limitation: once * appears, everything after it matches unconditionally — so LIKE '*01' (suffix) and LIKE '*01*' (contains) don't behave like their obvious reading suggests. For suffix or contains matching on asset IDs, district codes, or naming conventions, use MATCHES (a small regex dialect) instead.HAVING narrows to groups that themselves fail a check — here, systems whose largest pipe is suspiciously small.SELECT ... INTO FILE writes query results straight to a CSV on disk — skip the grid-view-then-manual-export step entirely. Works with the same column aliasing rules as any output query (quote an alias to include spaces or punctuation).3 > NULL is NULL, not false). Equality is the one documented exception — x = NULL behaves exactly like x IS NULL, and x <> NULL like x IS NOT NULL. That's why both forms work for equality specifically, but the exception doesn't extend to >/< — don't assume it does. An empty string is simultaneously NULL and '', and boolean/checkbox fields have no concept of NULL at all.us_node. (singular), per the official 03 Context Syntax Reference's implicit-join table: Link → Node = us_node / ds_node. That's distinct from us_links./ds_links., which are the plural one-to-many prefixes going the other way, from a node out to its links. This selects every conduit whose upstream node is flooding — the pipes feeding a flood hotspot.03 Context/InfoWorks_ICM_SQL_Syntax_Reference.md implicit-join table and the Glossary's navigation-keyword table, which both list us_node/ds_node (singular, one-to-one, from link) for the ICM modelling network. Whether a time-varying tsr. aggregate can be chained through the join the same way (e.g. MAX(us_node.tsr.depth)) is not stated either way in those files — sim., a summary field rather than a time series, is the safer demonstrated case.AREF(1,…) is the first element), COUNT($list) used for list length instead of LEN($list), END; closing WHILE loops instead of WEND;, and an invented WHERE field IN (SELECTED [Type]) subquery pattern that ICM SQL doesn't support (no subqueries — ever). The corrected version below uses the documented flag-propagation pattern instead.This page is a companion to the Innovyze Open-Source-Support repository's SQL collection — re-verified against a fresh clone on July 10, 2026: 50 InfoWorks script folders (0001–0049 with 0022 missing, plus two Bit Bucket folders that are README-only — see the correction below), 45 SWMM script folders, and the official 12-file 03 Context LLM system. Several of this cookbook's original proposals have since been adopted officially (Manning's Q-full, tsr envelopes, WHEN-clause rolling stats, SWMM LID coverage; the Bit Bucket unification only partly — see the correction log). The focus stays on the most powerful but under-documented ICM SQL features: SPATIAL keyword queries, sim. and tsr. results access, SWMM network objects, 2D zone/1D-2D coupling, connected object joins (us_links. / ds_links. / us_node. / ds_node.), and array data queries. Every script includes full SQL code with inline comments explaining what each line does and when to use it.
An external technical review scored an earlier draft 7.4/10, flagging six SQL correctness issues and proposing a set of feature improvements. Every claim below was re-checked against primary sources (Autodesk's own ICM and WS Pro help pages, fetched directly, plus a fresh clone of the Innovyze repo) rather than accepted at face value — two of the six didn't survive that check, and one fix turned out to be bigger than the review itself caught. The reasoning for each verdict is below so the "why," not just the "what," is on record.
Confirmed against Autodesk's SQL-and-spatial-searches help page, which states explicitly: "the SPATIAL and SELECT clauses are defined as separate query blocks and have to be separated by a semicolon." All six explicit SPATIAL cards on this page were missing it — every one is now fixed.
joined. → us_node. (and the wrong turn in between)This one took two passes, and the first pass made it worse. Recorded in full because the intermediate state shipped.
Round 1 — the original bug (real). A card used joined.sim.max_floodvolume from a Conduit to reach its upstream node. That was wrong.
Round 2 — my fix (also wrong). I replaced it with us_nodes./ds_nodes. (plural) and declared joined. "not a documented ICM keyword." Both claims were incorrect. There is no plural us_nodes. prefix, and joined. is documented — I over-corrected from a page that didn't cover the case.
Round 3 — verified against the repo's own context files. The official 03 Context/InfoWorks_ICM_SQL_Syntax_Reference.md implicit-join tables and InfoWorks_ICM_SQL_Glossary.md navigation-keyword table settle it:
us_node / ds_node, singular, one-to-one. Subcatchment → Node is node. These are what this cookbook needs, and what the page now uses throughout.us_links / ds_links (plural, one-to-many), plus all_us_links / all_ds_links for a full trace. These were right all along.joined. is real — it's the one-to-one from a survey or incident object to its parent asset in Collection/Distribution asset networks (CCTV Survey → Pipe, Manhole Survey → Manhole, Pipe Repair → Pipe, and so on), with an alternative type-specific name available (pipe, manhole). It is simply not the modelling-network link→node prefix, which is the specific misuse the original card made.All 51 occurrences across cards, comments, pills, and reference tables now use the singular form, and the lint rule was rewritten: it no longer calls joined. categorically wrong, instead flagging it only outside a survey/asset context, and it now also catches the plural us_nodes. form this page briefly shipped.
All four held up against ICM's own documented rules (which this cookbook's companion context guide had already independently established): lists are 1-indexed, so AREF($i,…) starting at $i=0 was wrong; COUNT($list) was used where list length needs LEN($list); WHILE loops were closed with END; instead of WEND;; and WHERE field IN (SELECTED [Type]) is an invented subquery pattern ICM SQL doesn't support. The card now uses the documented flag-propagation pattern instead (us_links.$flag / us_node.$flag chained each hop).
SUM(tsr.flow) mislabeled as volumeConfirmed against the ICM Time Varying SQL reference: SUM() just adds the ordinates at each timestep, which is only meaningful if every timestep is the same length. INTEGRAL() multiplies each value by its timestep duration before summing — the actually-correct way to get volume from a flow series. The scenario-comparison card now uses INTEGRAL(tsr.us_flow) * 60, and a full section on INTEGRAL, DURATION, EARLIEST/LATEST, and the WHEN clause has been added below.
A fair catch: dividing by conduit_width only gives a true diameter ratio for circular pipes. conduit_height holds the full vertical dimension for every shape and is the correct denominator for rectangular, egg, or arch sections. The card now carries this caveat explicitly rather than presenting one formula as universal.
The original check compared one invert to one connected node's ground level without specifying which end. Split into two explicit clauses — US invert against us_node.ground_level, DS invert against ds_node.ground_level (singular, per the correction above), so both ends are checked and neither is ambiguous.
spatial. to gislayer."The review's citation for this was Autodesk's WS Pro help page, which does use gislayer. — but ICM is a different product in the same family. Fetching ICM's own spatial-searches help page directly confirms: "The layer is then referred to in the query as spatial." Applying a WS-Pro-specific term to ICM would have introduced an error while fixing one. spatial.field stays as originally written.
Checked by fetching the pages directly rather than assuming: help2.innovyze.com is still live, current, ICM-specific documentation (the fetched pages carry a 2023.2 release stamp and are the pages that actually document spatial. and the SPATIAL semicolon rule used throughout this correction pass). The review's suggested replacement URLs are for WS Pro and InfoAsset Manager — different products, not a newer home for the same ICM content. No swap made; if Autodesk migrates ICM's docs later, this section should be revisited.
All five now live on every card: a per-card confidence badge (Official / Schema-checked / Concept — see note below), a client-side lint line flagging the exact anti-patterns this log describes, a search bar filtering titles/SQL/fields together, and Copy SQL / Download .sql buttons. The network/version selector that rewrites field names live and the full query-builder wizard were scoped down to a single "Query Starter" widget (network + query-kind → correct skeleton) rather than built as originally proposed — a dynamic version-aware rewriter risks silently producing wrong SQL for a page whose entire purpose is correctness, and honesty about what's actually implemented matters more here than matching the full feature list.
The review proposed four levels: Official / Verified / Schema-checked / Concept, with "Verified" meaning manually tested in a named ICM version. Nothing on this page has been run inside actual ICM software, so no card claims "Verified" — that would overstate what's actually been checked. Cards adapted directly from the Innovyze repo show Official; the 2D-zone category is marked Concept since its specifics (flood_type enum values, 2d_pt_id) weren't freshly re-confirmed against a primary source in this pass; everything else is Schema-checked — real field names, correct syntax per the sources above, not executed in live ICM.
TITLE(), the RINDEX trap, and a repo-inventory correctionAn older ICM SQL chat was supplied for mining. Most of it duplicated material already here, but three things came out of chasing its claims back to source:
1. TITLE(n, list) was missing from every reference on this page — including the comprehensive Autodesk function inventory added in the previous round, whose list-function section runs LEN / RINDEX / LOOKUP / LOOKUPFN / MEMBER / INDEX / AREF and stops. Yet TITLE() is used in twelve repo scripts across both the InfoWorks and SWMM folders, and is documented in the repo's own 03 Context Function Reference and SQL Combined.md. It's the function that turns a RINDEX bucket index into a range label, and it's what makes the official Bit Bucket scripts two lines long.
2. The old chat contained a worked bucketing query with a genuine off-by-one. Its list began with a leading 0, then labelled RINDEX = 0 as "< 100" and 1 as "100 - 200". Per the official semantics, a leading zero makes index 0 mean "below zero" and index 1 the first real bucket — so every label sat one bucket off. Nothing errors; the histogram just quietly misreports. Kept as a worked failure card rather than discarded, since the trap is easy to hit and the official TITLE() idiom sidesteps it entirely. Also worth noting: the newer folder READMEs describe RINDEX as returning "the largest value in the list less than or equal to the input" — that describes the net effect of RINDEX + TITLE together; RINDEX alone returns an index, not a value.
3. A claim on this page was wrong and is now corrected. Verifying the Bit Bucket scripts meant listing those folders, which showed the two newer "unified" folders contain only a README — no .sql. This page had previously stated they bundle both unit variants. Corrected in the repository map and the gap analysis above.
A separately-supplied comprehensive SQL inventory (citing help.autodesk.com/view/IWICMS/2027/ENU/) filled real gaps this page didn't cover: the full function inventory, LIKE/MATCHES pattern operators, GROUP BY/HAVING, INTO FILE CSV export, and a precise NULL-equality rule. Direct re-fetch of the cited 2027 URL 503'd on every attempt — including a search-confirmed-real 2026 URL from the same path pattern, so this reads as a bot-block on Autodesk's newer help CDN rather than a sign the content is fake. Cross-checked instead: the document's GSUBST('01880132','01','ND') → 'ND88ND32' example matches, verbatim, the known-good help2.innovyze.com tech notes page already cited throughout this cookbook. On that basis the material was treated as verified and added as five new cards, plus a companion section in the linked context guide (now v3.1).
These scripts live at innovyze/Open-Source-Support → 01 InfoWorks ICM → 02 SQL → 01 InfoWorks. Each folder contains a .sql file and often a README.md. Status: ● Existing = works as-is, ● Enhance = this cookbook proposes improvements, ● New = proposed addition. July 2026: the folder now also contains unified 0039 Conduit Diameter and 0040 Conduit Length Bit Bucket scripts (both unit systems, RINDEX bucketing, READMEs) — adopting this cookbook's unification proposal, so those four Enhance flags below are resolved.
| ID | Script Name | What It Does | Category | Status |
|---|---|---|---|---|
| 0001 | Split pipe every x chainage | Divides conduits at regular intervals along their length — used for 1D-2D coupling resolution and detailed profiling | traversal | ● Enhance |
| 0002 | Copy object data to a scenario | Transfers field values between network scenarios — core scenario management operation | update | ● Enhance |
| 0003 | Select all intersecting conduits | SPATIAL Cross search finding conduits that geometrically cross each other — pipe conflict detection | spatial | ● Enhance |
| 0004 | Average a value of nodes within polygon | SPATIAL Inside search with AVG aggregation — spatial averaging of node field values inside a polygon | spatial | ● Existing |
| 0005 | Select links with lowest invert level | Finds the deepest conduits — useful for identifying critical deep infrastructure | selection | ● Existing |
| 0006 | Globally Update SuDS Controls | Batch update SuDS/LID parameters across the entire network — green infrastructure management | update | ● Existing |
| 0007 | Select 3 links upstream/downstream of node | Network traversal with fixed hop count — localized inspection around a selected node | traversal | ● Enhance |
| 0008 | Select GIS points in subcatchment | SPATIAL Contains — finds GIS point objects inside subcatchment polygons | spatial | ● Existing |
| 0009 | Select subcatchments x distance from node | SPATIAL Distance — proximity-based subcatchment selection from a node | spatial | ● Existing |
| 0010 | Get min river bed levels for cross sections | Queries the cross-section structured array data to extract minimum bed elevation — river modeling | array / river | ● Enhance |
| 0011 | Select within distance of selected object | Buffer-based selection radiating from current selection — proximity analysis | spatial | ● Existing |
| 0012 | Select subcatchments from selected nodes | Reverse lookup: finds subcatchments that drain to the currently selected nodes | selection | ● Existing |
| 0013 | Update Channel XS Manning n | Batch update roughness values inside cross-section array data — river calibration | array / river | ● Enhance |
| 0014 | Select XS lines that cross a river reach | SPATIAL Cross between XS lines and river reach geometry — river model setup | spatial / river | ● Existing |
| 0015 | Transfer inverts between crossing pipes | Copies invert levels between intersecting conduits in different systems — conflict resolution | spatial / update | ● Existing |
| 0016 | Spatial select polygons inside subcatchments | SPATIAL Contains — polygon-in-polygon containment test | spatial | ● Existing |
| 0017 | Populate user text with connecting links | Writes comma-separated list of connected link IDs into node user_text fields using us_links. | connected obj | ● Existing |
| 0018 | Populate user text with max discharge from HD curve | Extracts peak discharge from head-discharge table array data into user fields | array / results | ● Existing |
| 0019 | Populate user_text_4 with downstream outfalls | Network trace to outfall — tags every node with its downstream outfall ID using iterative traversal | traversal | ● Enhance |
| 0020 | SQL CASE emulation using IF statements | Demonstrates ICM's IF/ELSE pattern since standard SQL CASE isn't supported — teaching example | utility | ● Enhance |
| 0021 | Create dummy node + DS conduit with orifice | Programmatic network object creation — demonstrates SQL INSERT for nodes, conduits, and orifices | creation | ● Existing |
| 0023 | Get river chainage from node (limited) | Extracts chainage position for nodes on river reaches — limited functionality noted by authors | river | ● Enhance |
| 0024 | Call out info box with OK/Cancel | User interaction pattern — shows dialog prompts within SQL scripts | utility / UI | ● Existing |
| 0025 | Spatial query: sum GIS point values in subcatchment | SPATIAL Contains + SUM aggregation — adds up GIS point values for all points inside each subcatchment | spatial | ● Existing |
| 0026 | Result moving average | Smooths simulation time-series results using tsr. prefix with a moving average window — one of only 2 tsr scripts in the repo | tsr results | ● Enhance |
| 0027 | Travel Time | Calculates travel time along conduit paths using length/velocity — time-of-concentration estimation | hydraulics | ● Enhance |
| 0028 | Select all links downstream of a node | Full downstream trace — selects every link between starting node and outfall | traversal | ● Existing |
| 0029 | Connect Gully 2D to nearest node via orifice | Creates orifice connections from 2D gully objects to nearest manholes — the only 2D-focused SQL in the repo | 2D / creation | ● Enhance |
| 0030 | Select nodes where subcatchments discharge to | Forward lookup: finds nodes receiving flow from subcatchments | selection | ● Existing |
| 0031 | Populate node user text with US/DS conduit inverts | Writes upstream and downstream conduit invert levels into node user fields using us_links. / ds_links. | connected obj | ● Existing |
| 0032 | Select links with same US and DS nodes | Finds parallel/duplicate conduits — common data quality issue after imports | QA/QC | ● Enhance |
| 0033 | Set ds_invert interpolating pipe soffits | Interpolates downstream inverts to maintain consistent pipe soffit alignment | update | ● Existing |
| 0034 | Delete duplicate default tables | Cleans up redundant default table entries — database housekeeping | QA/QC | ● Existing |
| 0035 | Kutter's Formula Calculation | Computes pipe flow capacity using Kutter's equation — alternative to Manning's for older systems | hydraulics | ● Enhance |
| 0036 | Select all links without Outfall downstream | Identifies dead-end network branches that never reach an outfall — connectivity/topology check | traversal / QA/QC | ● Existing |
| 0037a | SQL Sim Parameters | Reads and displays simulation parameter settings in a grid — configuration audit | utility | ● Existing |
| 0037b | Update Channel XS Manning n | Duplicate of 0013 with slight variations — batch XS roughness update | array / river | ● Existing |
| 0038 | WhenMax depth and flow for Links | Extracts timing of peak depth and flow using sim.max_ and WHENMAX — one of only 2 sim-results scripts in the repo | sim results | ● Enhance |
| 0039 | SQL Bit Bucket for Diameter (inches) | Bins conduits into diameter ranges for histogram analysis — imperial units | QA/QC | ● Enhance |
| 0040 | SQL Bit Bucket for Diameter (mm) | Same as 0039 but metric — bins conduits by diameter in millimetres | QA/QC | ● Enhance |
| 0041 | SQL Bit Bucket for ICM Length (feet) | Bins conduits into length ranges — imperial | QA/QC | ● Enhance |
| 0042 | SQL Bit Bucket for ICM Length (meters) | Bins conduits into length ranges — metric | QA/QC | ● Enhance |
| 0043 | Set subcatchment trade flow profiles | Assigns trade/commercial flow profiles to subcatchments in bulk — wastewater modeling | update | ● Existing |
| 0044 | Select links connected to selected nodes | Basic connectivity selection — all links touching the currently selected nodes | selection | ● Existing |
| 0045 | Select all links upstream of selected nodes | Full upstream trace — selects the entire contributing network above selected points | traversal | ● Existing |
| 0046 | Set Runoff Surface fixed runoff coefficient | Applies fixed runoff coefficients to subcatchment runoff surface array entries | array / update | ● Existing |
| 0047 | Create custom surcharge results table | Builds a formatted surcharge summary grid from sim.max_ results — reporting | sim results | ● Enhance |
| 0048 | Insert head-discharge tables and curve data | Programmatically creates HD tables with curve point data — avoids manual entry of rating curves | creation / array | ● Existing |
| 0049 | Calculate Runoff Area Contributions | Computes contributing runoff areas from subcatchment data — hydrology setup verification | hydraulics | ● Existing |
Update, July 10, 2026: The 02 SQL/03 Context folder now officially exists in the repo — a 12-file modular LLM context system maintained by Alex Grist (Squark89), last updated July 7, 2026. It includes Instructions.md (loading rules, token-budget tiers, network-type decision protocol — the designated starting point), InfoWorks_ICM_SQL_Lessons_Learned.md (anti-patterns, load-first), Syntax / Function / Pattern / Error / Glossary / Tutorial references with PAT_SQL_XXX_NNN cross-link IDs, a three-file schema set (Schema_InfoWorks at 2,277 lines, Schema_SWMM at 1,162 lines, Schema_Common), a Database_Fields_Guide that routes unknown fields to the Autodesk Product Help MCP, and a human-only 00 Reference/ folder containing the full SQL language reference and the ICM Time Varying SQL document (source of the DURATION / INTEGRAL / WHEN-clause material below). Find it at innovyze/Open-Source-Support → 02 SQL → 03 Context.
The table below maps the 12 context files this cookbook originally proposed against what the official system now delivers — most are covered; the remaining gaps are candidates to contribute upstream:
| Proposed Context File | Content Description | Official Coverage |
|---|---|---|
| CTX-001-SPATIAL-keyword.md | Complete reference for the SPATIAL keyword: all 5 geometry types (Cross, Inside, Contains, Distance, Nearest), all 4 layer types (Network, Background, GIS, Spatial rain), distance parameter syntax, how SPATIAL NONE resets spatial mode within IF/WHILE blocks, and which geometry combinations are valid (the table from the help file). Include worked examples of each combination. | ● Covered — Syntax_Reference + Pattern_Reference handle SPATIAL; the geometry×layer validity matrix remains thinner than proposed |
| CTX-002-sim-results.md | Reference for the sim. prefix: all available fields for nodes (max_level, max_flooddepth, max_floodvolume, max_flvol, etc.), links (max_us_depth, max_us_flow, max_us_vel, max_us_froude, max_ds_*), and subcatchments. How to load results into the GeoPlan. The difference between max results and current-timestep results. How the time control "show maximum" toggle affects sim. values. | ● Covered — object-specific sim.* field tables live in Schema_InfoWorks / Schema_SWMM |
| CTX-003-tsr-time-series.md | Reference for the tsr. prefix (time series results): all aggregate functions (MIN, MAX, AVG, SUM, COUNT, WHENMAX, WHENMIN, ANY, ALL, FIRST, LAST), how AVG is time-weighted, how COUNT works with boolean expressions, tsr.timestep_no for windowed queries, IIF() for conditional filtering within tsr expressions. The tsr2. prefix for comparing two simulations. | ● Partial — Function_Reference + Schema_Common cover aggregates, but DURATION / INTEGRAL / EARLIEST / LATEST / WHEN clauses sit only in the human-only 00 Reference/ICM Time Varying SQL.md — worth promoting into the LLM set |
| CTX-004-sim2-tsr2-comparison.md | How to load a second simulation as alternate results (sim2./tsr2.): the right-click → "Open As → As alternate results for comparison" workflow. Fields available, how to compute deltas, percentage changes, and timing shifts between scenarios. Limitations (both sims must have same network/timesteps). | ● Gap — sim2./tsr2. alternate-results comparison not yet documented in the official set; prime upstream contribution |
| CTX-005-connected-objects.md | Complete reference for us_links., ds_links., us_node., and ds_node. prefixes (node→link and link→node directions respectively — corrected from an earlier draft that used an invented joined. keyword). How aggregate functions work across multiple connected objects. Combining connected-object fields with sim. results (e.g., us_node.sim.max_floodvolume). | ● Covered — Syntax_Reference implicit joins + Schema_Common relationship paths |
| CTX-006-array-data.md | How to query Value arrays (bends, vertices) vs Structure arrays (cross-sections, runoff surfaces, HD curves). Available aggregate functions for arrays (MIN, MAX, AVG, SUM, COUNT, ANY, ALL). Which array fields are accessible by name vs. which require positional access. Specific field names for cross-section arrays (section_array.elevation, section_array.chainage, section_array.roughness_N), runoff surface arrays, and HD curve arrays. | ● Covered — blob/array rules in Syntax_Reference and Function_Reference |
| CTX-007-SWMM-vs-InfoWorks.md | Differences between querying InfoWorks (hw_) and SWMM (sw_) networks in SQL. Object type names in the SQL dropdown vs. Ruby table prefixes. Which fields exist in SWMM but not InfoWorks (shape, barrels, flap_gate) and vice versa (system_type, scenario fields). SWMM-specific curve tables and how to query them. The FROM keyword override for SWMM object types. | ● Covered — Schema_Common carries the IW-vs-SWMM difference table; Instructions.md enforces the never-mix rule |
| CTX-008-2D-zone-SQL.md | How SQL interacts with 2D zones: setting flood_type values ('Stored', '2D', 'Gully 2D', 'Outfall 2D', 'Lost'), the flooding_discharge_coeff field, head-discharge table assignment, the [2d_pt_id] field for subcatchment 2D inflow points, and using SPATIAL Inside Network [2D Zone] to select objects within a 2D zone polygon. Workflow sequence for 1D-2D coupling via SQL. | ● Gap — flood_type / 2d_pt_id / 1D-2D coupling workflow not yet in the official context files; candidate to contribute |
| CTX-009-PROMPT-variables.md | User input patterns: PROMPT LINE $var 'Label', PROMPT LINE $var 'Label' RANGE min max, PROMPT LINE $var 'Label' LIST $listvar, PROMPT DISPLAY. Variable types (LET, LIST), AREF() for list access, SELECT ... INTO $var for capturing query results, and the macro system (User Macros tab) as a find-and-replace engine. | ● Covered — Pattern_Reference PROMPT templates; macro-system detail still lives with sancarn |
| CTX-010-SQL-field-reference.md | Complete field name listings for each object type: All Nodes (node_id, x, y, ground_level, chamber_floor, chamber_roof, shaft_area, flood_type, system_type, user_text_1–10, user_number_1–10, etc.), Conduit (us_node_id, ds_node_id, link_suffix, conduit_length, conduit_width, conduit_height, us_invert, ds_invert, gradient, roughness_N, shape, system_type, etc.), Subcatchment (subcatchment_id, node_id, total_area, population, etc.). With data types and units. | ● Covered ++ — delivered beyond proposal: 2,277-line InfoWorks schema, 1,162-line SWMM schema, plus MCP Help routing via Database_Fields_Guide |
| CTX-011-FROM-keyword.md | How to use SELECT FROM [Object Type] to override the dropdown and query multiple object types in one script. Object type names for InfoWorks (Conduit, All Nodes, All Links, Subcatchment, Orifice, Weir, Pump, etc.) and SWMM (SWMM Conduit, SWMM Node, SWMM Subcatchment, SWMM Weir, SWMM Orifice, SWMM Pump, etc.). Using INSERT to create new objects in a different table. | ● Covered — object manifests in both schema files |
| CTX-012-multi-clause-patterns.md | Advanced multi-clause SQL patterns: chaining SELECT/DESELECT/UPDATE with semicolons, CLEAR SELECTION, SELECT SELECTED to filter within existing selection, IF/ELSEIF/ELSE/END blocks, WHILE/END loops, nesting blocks, and how SPATIAL scope works within IF/WHILE blocks. The "master checklist" pattern of chaining validations. | ● Covered — Pattern_Reference PAT_SQL IDs + Tutorial_Context workflows |
0039 - SQL Bit Bucket for Conduit Diameter, 0040 - SQL Bit Bucket for Conduit Length) contain only a README.md — no .sql files. Their READMEs describe and link scripts by name (SQL Bit Bucket for Diameter (in).sql etc.) that are not present in those folders; the runnable SQL still lives only in the four original single-unit folders (0039 (inches), 0040 (mm), 0041 (feet), 0042 (meters)). So the unification is documentation-only so far, not a code merge — worth knowing before pointing a colleague at the newer folder and watching them find it empty.0003 Calculate Pipe Gradient and Full Flow Capacity (Manning's Q-full — previously this cookbook's #1 gap, now official), 0004 Peak Sustained Depth (Rolling Average) (the WHEN-clause showcase), 0033–0039 full tsr. envelope dumps for Link / Node / Subcatchment / 1D-Points / Land-Uses / Runoff-Surfaces / SWMM-Link results, 0040 SET Gradient_Full_Flow, 0049 Subcatchment LID or SuDS Coverage, 0050 Kutter SQL for ICM SWMM, plus the InfoSewer-heritage set (Wet Well, Population Flow, Area, Manhole Area) and SWMM housekeeping scripts.tsr. usage in the community), subcatchment drainage assignment, auto-creation of overland channels, 2D inflow point redirection, scenario data pulling, PROMPT/RANGE/LIST user input examples, and more. Available as both a .sql file and an importable .icmt transportable database.User Macros tab), showing how macros are essentially find-and-replace engines and can be used to build reusable sub-routines.Re-audited against a fresh clone of the repo (50 InfoWorks + 45 SWMM scripts). Several gaps this cookbook identified have since been closed officially — marked ✓ — while others remain open contribution opportunities:
0003 Calculate Pipe Gradient and Full Flow Capacity (US units, with README) and 0040 SET Gradient_Full_Flow. A metric variant remains a contribution opportunity.0033–0039: complete MIN/MAX/AVG/SUM/COUNT/WHENMAX/WHENMIN dumps for Link, Node, Subcatchment, 1D-Points, Land-Uses, Runoff-Surfaces, and SWMM-Link results.0039 Conduit Diameter and 0040 Conduit Length folders documenting exactly that merge — but they contain README.md only; the .sql files their tables reference aren't in them, and the runnable scripts remain in the four original folders. Documented, not yet merged.0049), Kutter for SWMM (0050), barrels, inverts, FM roughness, and pump finding. The dedicated weir/orifice geometry validation proposed here is still absent.0004 Peak Sustained Depth demonstrates the AVG(tsr.x) WHEN tsr.timestep_no ... clause — cleaner than the IIF-NULL pattern this cookbook used.0029 (Gully 2D connector) touches 2D. The flood_type conversion sequence, discharge coefficients, and 2d_pt_id redirection in this cookbook have no repo equivalent.00 Reference/ICM Time Varying SQL.md; no repo script uses them. DURATION(tsr.flooddepth > 0) replaces COUNT×dt arithmetic and INTEGRAL(tsr.us_flow) gives flow-to-volume directly — high-value additions.us_node.sim. queries remain cookbook-only.sim./tsr./sim2./tsr2. prefixes, variables, arrays, connected objects, and advanced features03 Context/InfoWorks_ICM_SQL_Syntax_Reference.md is better: it tabulates every implicit join per network type.sim.max_floodvolume flooding query and risk-of-flooding freeboard analysissim.field_name — Access max results (or current timestep) for the primary loaded simulationsim2.field_name — Same for the alternate/comparison simulationtsr.field_name — Access ALL timesteps of primary simulation. Full aggregate set: MIN, MAX, AVG (time-weighted), SUM, COUNT, WHENMAX, WHENMIN, ANY, ALL, FIRST, LAST, plus the four most guides miss: DURATION, INTEGRAL, EARLIEST/LATEST, WHENEARLIEST/WHENLATESTtsr2.field_name — Same for the alternate simulation's time seriesAGG(tsr.x) WHEN condition — WHEN clause: window any tsr aggregate to specific timesteps, e.g. AVG(tsr.depth) WHEN tsr.timestep_no >= $i AND tsr.timestep_no <= $i+$w. WHEN fields: tsr.timestep_no (1-based), tsr.timestep_start, tsr.timesteps, tsr.timestep_duration, tsr.sim_start, tsr.sim_end. Date literals: #01/01/2013 12:30#DURATION(tsr.cond) — Minutes for which the condition is true (not necessarily contiguous) — direct flood/surcharge duration with no COUNT×dt arithmeticINTEGRAL(tsr.x) — Σ value × timestep-minutes; INTEGRAL(tsr.us_flow) * 60 converts a flow series (m³/s) straight to volume (m³). Caveat: AVG, INTEGRAL, and SUM ignore the simulation's final timestepEARLIEST(IIF(tsr.cond, tsr.x, NULL)) / WHENEARLIEST(tsr.cond) — First qualifying value / first time a condition becomes true (LATEST/WHENLATEST mirror them)us_links.field — From a node, access fields of conduits arriving at that node (their DS end is here)ds_links.field — From a node, access fields of conduits departing from that node (their US end is here)us_node.field / ds_node.field — Singular. From a link, access its upstream/downstream node (one-to-one). node.field does the same from a subcatchment. Plural us_nodes. does not exist; joined. is a survey→parent-asset prefix, not this. A subcatchment's node_id is also just its own field — readable with no prefix at all.spatial.field — Access fields of the GIS/background/network layer object found by the SPATIAL searchSPATIAL type layer "name" [distance] — Set up spatial search (Cross/Inside/Contains/Distance/Nearest)PROMPT LINE $var 'Label' + PROMPT DISPLAY — Ask user for input before running the querySELECT ... INTO $var — Store query results into a variable or list for use in subsequent clausesAREF($index, $list) — Access element at position $index in a list variableIIF(condition, true_value, false_value) — Inline if/else (ICM's equivalent of SQL CASE)DP n — Display to n decimal places in grid outputSELECT FROM [Object Type] — Override the Object Type dropdown, query a different table within the same SQLsim. / tsr. scripts: drag simulation results onto the GeoPlan firstsim2. / tsr2. scripts: right-click a second simulation → "Open As → As alternate results for comparison"