Query examples for search results

Displaying formulas in search results

The f.formula_id=s.formula_id phrase is required because a formula is an object with a 2-part key. Without it, all data from the FsFormula table is shown.

SELECT f.formula_code, f.version, f.description 
FROM fsformula f, [%%SEARCHTABLE] s 
WHERE f.formula_id = s.formula_id 
ORDER BY f.formula_code, f.version

For an object like an item, if I.item_code=s.Item_Code is not used, all items in the FsItem table are displayed.

Placing pivoted data

Pivoted data must be last (right-most) in the SELECT clause of the results query for formula and item searches.

SELECT f.formula_code, f.version, f.description, tp.param_code, 
tp.pvalue

SELECT i.item_code, i.description, tp.param_code, tp.pvalue

Displaying ingredient, context, custom, or reference data in a pivoted form is optional.

Displaying search results for technical parameters

This SQL query shows the SPGRAV, SPF, and DENSITY technical parameters along with the formulas returned from a search.

SELECT f.formula_code, f.version, f.description, tp.param_code, 
tp.pvalue 
FROM fsformula f, [%%SEARCHTABLE] s, FsFormulaTechParam tp 
WHERE f.formula_id = s.formula_id 
AND f.formula_id = tp.formula_id 
AND tp.param_code in ("SPGRAV", "SPF", "DENSITY") 
ORDER BY f.formula_code, f.version

Returning parameter values in the Formula Search form.

Use %%PIVOT_PARAM in the WHERE clause. If the user does not specify any parameter criteria in the Parameters tab, no results are displayed.

SELECT f.formula_code, f.version, f.description, tp.param_code, 
tp.pvalue 
FROM fsformula f, [%%SEARCHTABLE] s, FsFormulaTechParam tp 
WHERE f.formula_id = s.formula_id 
AND f.formula_id = tp.formula_id 
AND tp.param_code in ([%%PIVOT_PARAM]) 
ORDER BY f.formula_code, f.version

Displaying all parameters in search results

You can display all technical parameters in search results by omitting the parameters from the WHERE clause.

WHERE f.formula_id = s.formula_id AND f.formula_id = tp.formula_id 

For an item search, use this syntax:

WHERE i.item_code=s.item_code AND i.item_code=tp.item_code