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:

 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]

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:

 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:

 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

 

[LucaMs]

Didn't quote the code properly:

https://www.b4x.com/android/forum/threads/b4x-smart-string-literal.50135/

 

[RB Smissaert]

https://www.b4x.com/android/forum/threads/b4x-smart-string-literal.50135/

I was talking about not putting [/CODE] at the end of the posted code.

RBS

 

[Erel]

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]

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]

Can't test now, but not sure this will work.

if your code above using the multiple lines (complicate to read and maintain SQL) works then i expect it to work too.
Smart String literal makes is more easier to create multiline SQL Queries.

See the tutorial: https://www.b4x.com/android/forum/threads/b4x-smart-string-literal.50135/

 

[RB Smissaert]

if your code above using the multiple lines (complicate to read and maintain SQL) works then i expect it to work too.
Smart String literal makes is more easier to create multiline SQL Queries.

See the tutorial: https://www.b4x.com/android/forum/threads/b4x-smart-string-literal.50135/

But I specified the end of line spaces and they are needed as far as I know.
So how would this work with the code Erel posted?

RBS

 

[DonManfred]

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.

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]

They are needed if you want to concat the next line using the codestructure you were using.

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]

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 }:

Log($"5 * 3 = ${5 * 3}"$) '5 * 3 = 15

You can put any code you like inside the placeholders.

Dim x = 1, y = 2, z = 4 As Int
Log($"x = ${x}, y = ${y}, z = ${Sin(z)}"$) 'x = 1, y = 2, z = -0.7568024953079282

 

[RB Smissaert]

Note that the smart string literals have also other excellent features, like:

Yes, should start using this.

RBS

 

[LucaMs]

Yes, should start using this.

RBS

Completely off-topic (this is a snippet!) but...

it's even fun to type $" and immediately (or anyway inside the string) another $; try



I stop here, don't worry

 

[LucaMs]

it's even fun to type $" and immediately (or anyway inside the string) another $; try

https://www.b4x.com/android/forum/threads/b4x-smart-string-literal.50135/page-3#post-691002

 

[RB Smissaert]

https://www.b4x.com/android/forum/threads/b4x-smart-string-literal.50135/page-3#post-691002

Hopefully, after all this fun, somebody will fine this SQL SQRT method useful to know.

RBS

 

[LucaMs]

Hopefully, after all this fun, somebody will fine this SQL SQRT method useful to know.

RBS

first of all I should read and understand it (sorry)

 

[Mahares]

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.