Android Code Snippet Taking SquareRoot in SQL

SQLite doesn't have SQTR as a standard function, so you would normally do this in code.
There is a way though to do this in SQL with a CTE:

B4X:
 strSQL = "update Table1 set xValue = (" & _
    "with Guesses(FindRootOf, " & _
    "guessRoot) as (" & _
    "select xValue, " & _
    "case when xValue < 0 then null else xValue / 2.0 end " & _
    "union " & _
    "select FindRootOf, " & _
    "(GuessRoot + FindRootOf / GuessRoot) / 2.0 As NewGuessRoot " & _
    "from Guesses " & _
    "where GuessRoot > 0.0) " & _
    "select GuessRoot " & _
    "from Guesses " & _
    "order by abs(GuessRoot * GuessRoot - FindRootOf) limit 1)"
 SQL1.ExecNonQuery(strSQL)
[CODE]

This is about 4x faster than taking the values with a select statement, taking the SQRT in B4A code and
updating the new value back.

RBS
 

RB Smissaert

Well-Known Member
Licensed User
Longtime User
SQLite doesn't have SQTR as a standard function, so you would normally do this in code.
There is a way though to do this in SQL with a CTE:

B4X:
 strSQL = "update Table1 set xValue = (" & _
    "with Guesses(FindRootOf, " & _
    "guessRoot) as (" & _
    "select xValue, " & _
    "case when xValue < 0 then null else xValue / 2.0 end " & _
    "union " & _
    "select FindRootOf, " & _
    "(GuessRoot + FindRootOf / GuessRoot) / 2.0 As NewGuessRoot " & _
    "from Guesses " & _
    "where GuessRoot > 0.0) " & _
    "select GuessRoot " & _
    "from Guesses " & _
    "order by abs(GuessRoot * GuessRoot - FindRootOf) limit 1)"
 SQL1.ExecNonQuery(strSQL)
[CODE]

This is about 4x faster than taking the values with a select statement, taking the SQRT in B4A code and
updating the new value back.

RBS

Didn't quote the code properly:

B4X:
 strSQL = "update Table1 set xValue = (" & _
    "with Guesses(FindRootOf, " & _
    "guessRoot) as (" & _
    "select xValue, " & _
    "case when xValue < 0 then null else xValue / 2.0 end " & _
    "union " & _
    "select FindRootOf, " & _
    "(GuessRoot + FindRootOf / GuessRoot) / 2.0 As NewGuessRoot " & _
    "from Guesses " & _
    "where GuessRoot > 0.0) " & _
    "select GuessRoot " & _
    "from Guesses " & _
    "order by abs(GuessRoot * GuessRoot - FindRootOf) limit 1)"
 General.cConn.SQL1.ExecNonQuery(strSQL)

RBS
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
B4X:
strSQL = $"update Table1 set xValue = (
    with Guesses(FindRootOf, 
    guessRoot) as (
    select xValue, 
    case when xValue < 0 then null else xValue / 2.0 end 
    union 
    select FindRootOf, 
    (GuessRoot + FindRootOf / GuessRoot) / 2.0 As NewGuessRoot 
    from Guesses 
    where GuessRoot > 0.0) 
    select GuessRoot 
    from Guesses
    order by abs(GuessRoot * GuessRoot - FindRootOf) limit 1)"$
 SQL1.ExecNonQuery(strSQL)
 

RB Smissaert

Well-Known Member
Licensed User
Longtime User
B4X:
strSQL = $"update Table1 set xValue = (
    with Guesses(FindRootOf,
    guessRoot) as (
    select xValue,
    case when xValue < 0 then null else xValue / 2.0 end
    union
    select FindRootOf,
    (GuessRoot + FindRootOf / GuessRoot) / 2.0 As NewGuessRoot
    from Guesses
    where GuessRoot > 0.0)
    select GuessRoot
    from Guesses
    order by abs(GuessRoot * GuessRoot - FindRootOf) limit 1)"$
 SQL1.ExecNonQuery(strSQL)

Can't test now, but not sure this will work.
You would for example get no endunion.
Or am I somehow mistaken with this?
Can't see it figuring out where spaces should be.

RBS
 

DonManfred

Expert
Licensed User
Longtime User

RB Smissaert

Well-Known Member
Licensed User
Longtime User

DonManfred

Expert
Licensed User
Longtime User
But I specified the end of line spaces and they are needed as far as I know.
They are needed if you want to concat the next line using the codestructure you were using.

B4X:
dim a as string = "codeline..... _
nextline"

Using Smart String Literal it is NOT needed at all.

strSQL = $"update Table1 set xValue = (
with Guesses(FindRootOf,
guessRoot) as (
select xValue,
case when xValue < 0 then null else xValue / 2.0 end
union
select FindRootOf,
(GuessRoot + FindRootOf / GuessRoot) / 2.0 As NewGuessRoot
from Guesses
where GuessRoot > 0.0)
select GuessRoot
from Guesses
order by abs(GuessRoot * GuessRoot - FindRootOf) limit 1)"$

SQL1.ExecNonQuery(strSQL)

Try it yourself! You´ll see it will be easier to construct such QUERIES.
 

RB Smissaert

Well-Known Member
Licensed User
Longtime User
They are needed if you want to concat the next line using the codestructure you were using.

B4X:
dim a as string = "codeline..... _
nextline"

Using Smart String Literal it is NOT needed at all.



Try it yourself! You´ll see it will be easier to construct such QUERIES.

Of course, the smart literal will recognize the line breaks, just as SQL will do, so yes, of course it will work.
Didn't think of this and indeed this is quite nice and will use.

RBS
 

LucaMs

Expert
Licensed User
Longtime User
Didn't think of this and indeed this is quite nice and will use.
Note that the smart string literals have also other excellent features, like:

String Interpolation

Smart strings can hold zero or more placeholders with code. The placeholders can be easily formatted.
A placeholder starts with $[optional formatter]{ and ends with }:
B4X:
Log($"5 * 3 = ${5 * 3}"$) '5 * 3 = 15
You can put any code you like inside the placeholders.
B4X:
Dim x = 1, y = 2, z = 4 As Int
Log($"x = ${x}, y = ${y}, z = ${Sin(z)}"$) 'x = 1, y = 2, z = -0.7568024953079282
 

Mahares

Expert
Licensed User
Longtime User
Hopefully, after all this fun, somebody will fine this SQL SQRT method useful to know
For any of us to understand what you did:
1. You should follow the guidelines of a 'Code Snippet' when posting a snippet.
2. You should explain and identify what all columns and variables are.
3. You should have a concrete example that one can follow or better yet include a small project that illustrates the concept.
4. What kind of test did you conduct to find out it is 4x faster than...
5. And yes, string literals for such a complex SQL statement is the way to go.
 
Top