Select Woes - Conditional Conversion of Text to Numeric

[James Cox]

Currently we are using the following SQL to bring back the most recent
records:

SQLStmt = "SELECT sample,"
SQLStmt = SQLStmt & " SAMPLING_POINT,"
SQLStmt = SQLStmt & " TO_CHAR(SAMPLED_DATE,'YYYY/MM/DD') SAMPLED_DATE,"
SQLStmt = SQLStmt & " VW_NAME,"
SQLStmt = SQLStmt & " VW_TEXT,"
SQLStmt = SQLStmt & " VW_UNITS"
SQLStmt = SQLStmt & " FROM lims.vdata"
SQLStmt = SQLStmt & " WHERE SAMPLING_POINT = '" & strProdName & "'"
SQLStmt = SQLStmt & " AND Upper(VW_NAME) IN " &
"('Comp1','Comp2,Comp3','Comp4')"
SQLStmt = SQLStmt & " ORDER BY SAMPLED_DATE desc,"
SQLStmt = SQLStmt & " VW_NAME"

This returns a recordset that contains rows for different chemical
components (the Comp1 -> Comp4) for different products (the SAMPLING_POINT)
at all the different SAMPLED_DATEs in the database. The actual
concentration of the chemical component is in VW_TEXT and is a number
formatted as text.

What we need to move to is a way to select, for a given VW_NAME, to select
the highest value found in VW_TEXT for each Comp1 through Comp4 - regardless
of the date. This means that Comp1's highest value might be 14.6 on 20 Jun
2003 and the hightest for Comp2 might be 990.4 on 11 Nov 2000 - and that is
what we need. (Note that the highs do not have to be at the same
SAMPLED_DATE.

What might be a straight-forward conversion of a text field to a numeric one
is messed up by the fact that when the concentration of one of the Comps is
lower than the detection limit of the instrument used to analyze it, an
entry for VW_TEXT like "< 230" is added to the database. In such cases -
where there is a "<" in VW_TEXT - the converted-to-numeric value should be
1/2 of 230. Of course, we've changed the instruments used to analyze over
the years and these detection limits have changed (and will continue to) so
we can't exhaustively list and test for the numeric value of the detection
limits. Whatever we do has to be 'dynamic' in the sense that it actually
calculates half of the numeric values.

We're stumped - any help would be most appreciated!

James Cox

 

[John Spencer (MVP)]

Well, you can use a calculated field for VW_TEXT. The following should convert
the text to a number. I even assume that the "<" may occur in a position other
than the first position. If it is always in the first position (no leading
spaces) then you could simplify this.

IIF(Instr(1,[vw_Text],"<")=0,Val(vw_text),Val(Mid(vw_text,Instr(1,vw_text,"<")+1))/2)

Simplified a bit
IIF(Left(vw_text="<",Val(Mid(vw_text,2))/2,Val(vw_text))