B4J Question [SOLVED]SQL question...

[rbghongade]

Dear friends,
i have a situation where I am supposed to find the immediate lower and the immediate higher values for a user input value. How to write the query for the same?
Example: The user input is 140, in the table I have values 135,137,138,142,143. The expected results are (138 and 142).

 

[keirS]

select * from(select TOP 1 * from dbo.DateTest where value < 140 order by value desc ) as A UNION ALL  select * from  (select TOP 1 * from dbo.DateTest where value > 140 order by value asc  ) AS b

 

[rbghongade]

Thanks, but am unable to use it. How to use this query in B4J? Pardon my abysmal DB knowledge . I have used code like:

rs1=SQL1.ExecQuery("Select * from E192 where value < 140 ") 'E192 is table name
    Log(rs1.GetString("VALUE")) '"VALUE" is field name

 

[OliverA]

What database are you using?

 

[LucaMs]

rs1 = Sql1.ExecQuery2("SELECT * FROM E192 WHERE Value BETWEEN ? AND ?", Array(MinValue, MaxValue))

MinValue and MaxValue are the user input (variables), of course.

 

[rbghongade]

@OliverA: I am using SQLITE
@LucaMs: The requirement is otherway round. The user supplies one value and our code is supposed to return two values (immediate lower and immediate upper )from the user input. But thanks for immediate reply!

 

[aeric]

You can use prepared statement. Replace the number 140 with ? then use ExecuteQuery2 and pass the value using parameter array.

From keirS query,

query = “SELECT value FROM (SELECT TOP 1 value FROM E192 WHERE valuue < ? ORDER BY value DESC) as A UNION SELECT value FROM  (SELECT TOP 1 value FROM E192 WHERE value > ? ORDER BY value ASC) AS b”
rs1 = SQL1.ExecuteQuery2(query, array as object(intInput, intInput))

 

[keirS]

What DBMS are you using?

 

[OliverA]

You can use prepared statement. Replace the number 140 with ? then use ExecuteQuery2 and pass the value using parameter array.

Does TOP work with SQLite or should you use LIMIT?

query = “SELECT value FROM (SELECT value FROM E192 WHERE value < ? ORDER BY value DESC LIMIT 1) as A UNION SELECT value FROM  (SELECT value FROM E192 WHERE value > ? ORDER BY value ASC LIMIT 1) AS b”
rs1 = SQL1.ExecuteQuery2(query, array as object(intInput, intInput)

 

[aeric]

Or simply:
query = “SELECT TOP 1 value FROM E192 WHERE value < ? ORDER BY value DESC UNION SELECT TOP 1 value FROM E192 WHERE value > ? ORDER BY value ASC”

 

[aeric]

Does TOP work with SQLite or should you use LIMIT?

query = “SELECT value FROM (SELECT value FROM E192 WHERE value < ? ORDER BY value DESC LIMIT 1) as A UNION SELECT value FROM  (SELECT value FROM E192 WHERE value > ? ORDER BY value ASC LIMIT 1) AS b”
rs1 = SQL1.ExecuteQuery2(query, array as object(intInput, intInput)

You are right. We need to use LIMIT 1 at the back instead of SELECT TOP 1 in SQLite.

 

[rbghongade]

@OliverA , the code works perfect. Thank you.
Friends, thank you all for your support.

 

[LucaMs]

Reusable

Public Sub Adjacencies(TableName As String, FieldName As String, MidValue As Int) As List
    Dim lstResult As List
    lstResult.Initialize
   
Dim Query As String
Query = _
$"
SELECT ${FieldName} FROM
(SELECT ${FieldName} FROM ${TableName} WHERE ${FieldName} < ? ORDER BY ${FieldName} DESC LIMIT 1) AS A
UNION
SELECT ${FieldName} FROM
(SELECT ${FieldName} FROM ${TableName} WHERE ${FieldName} > ? ORDER BY ${FieldName} ASC LIMIT 1) AS B
"$
    Dim Cursor As ResultSet
    Cursor = DB.ExecQuery2(Query, Array(MidValue, MidValue))
    Do While Cursor.NextRow
        lstResult.Add(Cursor.GetInt(FieldName))
    Loop
   
    Return lstResult
End Sub

 

[rbghongade]

@LucaMs, that is wonderful!

 

[OliverA]

the code works perfect. Thank you.

Have you tried the suggestion posted by @aeric in post #11? That would condense the statement (adjusted to using LIMITs) to

query = “SELECT value FROM E192 WHERE value < ? ORDER BY value DESC LIMIT 1 UNION SELECT value FROM E192 WHERE value > ? ORDER BY value ASC LIMIT 1”

 

[OliverA]

@LucaMs, that is wonderful!

Just make sure you know where the source of your table and column names come from. Otherwise you may subject yourself to SQL injection attacks.

 

[rbghongade]

@OliverA , thanks for your concern . I am using the code for finding the standard values of resistors for circuit design! the standard values are stored in the tables! So no worries there!