Android Code Snippet Taking SquareRoot in SQL

RB Smissaert · Oct 22, 2019

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 · Oct 22, 2019

RB Smissaert said:

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

LucaMs · Oct 22, 2019

RB Smissaert said:
Didn't quote the code properly:

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

RB Smissaert · Oct 22, 2019

LucaMs said:
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 · Oct 22, 2019

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 · Oct 22, 2019

Erel said:

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 · Oct 22, 2019

RB Smissaert said:
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 · Oct 22, 2019

DonManfred said:
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 · Oct 22, 2019

RB Smissaert said:
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.

Erel said:
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 · Oct 22, 2019

DonManfred said:
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 · Oct 22, 2019

RB Smissaert said:
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:

Erel said:
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

RB Smissaert · Oct 22, 2019

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

Yes, should start using this.

RBS

LucaMs · Oct 22, 2019

RB Smissaert said:
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 · Oct 22, 2019

LucaMs said:
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 · Oct 22, 2019

LucaMs said:
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 · Oct 22, 2019

RB Smissaert said:
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 · Oct 23, 2019

RB Smissaert said:
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.

Android Code Snippet Taking SquareRoot in SQL

RB Smissaert

Well-Known Member

LucaMs

Expert

RB Smissaert

Well-Known Member

Erel

B4X founder

RB Smissaert

Well-Known Member

DonManfred

Expert

RB Smissaert

Well-Known Member

DonManfred

Expert

RB Smissaert

Well-Known Member

LucaMs

Expert

RB Smissaert

Well-Known Member

LucaMs

Expert

LucaMs

Expert

RB Smissaert

Well-Known Member

LucaMs

Expert

Mahares

Expert

Similar Threads

Android Code Snippet Taking SquareRoot in SQL

Well-Known Member

Expert

Well-Known Member

B4X founder

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Well-Known Member

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency