Home : Variable WHERE Clause
Q14590 - INFO: Variable WHERE Clause

Certain Spread functions (VT, VINFODDVTMVSTAT ) allow you to specify the variable via an SQL Where Clause instead of a VarNum or reference to a cell with a VarNum. 

The WHERE Clause must be a valid SQL and refer to the VARDESC/LOCATION tables. 

Referring to a Cell value in the Where clause.  Use the syntax CELL(A1) to use the value of a cell in the query.  NOTE:  Cell references are NOT updated when the cell is moved, columns/rows inserted, etc.  The "?" can be used in place of the Column or row specification to indicate the current row/col.  Example,  CELL(B?) refers to column B and the current row the formula is in. 

EXAMPLES:

VT("LOCATION.PWSID='CELL(B2)' and NAME LIKE '%Total Coliform PA'",1) - NOTE: CELL(B2) will return the value in the cell from B2, NM0680123 in this case.

SQL Statement would become:

SELECT VARID
FROM VARDESC LEFT OUTER JOIN LOCATION ON VARDESC.LOCID=LOCATION.LOCID
WHERE LOCATION.PWSID='NM0680123' AND NAME LIKE '%Total Coliform PA'
ORDER BY VARNUM

VINFO("VARDESC.LOCID=CELL(B?) AND NAME LIKE '%pH'","DECPLACES") - If Formula is in E6, would find the LOCID in cell B6.

 

MVSTAT("AVG",1,"R","LOCATION.LVL1_ID =10 AND NAME LIKE '%Free Chlorine'")

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 5/13/2020 4:27 PM.
Last Modified on 8/26/2020 10:03 AM.
Last Modified by Scott Dorner.
Article has been viewed 287 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article