Android Question SQL JOIN and multiple ORDER BY

dexMilano

dexMilano

Active Member
Licensed User
Longtime User
Hello all,
I compose this SQL statement

"SELECT * FROM ACT_CUS_DATA JOIN ACTIVITIES USING ACTIVITY_ID WHERE CUS_VALUE >= 1339 ORDER BY CUS_VALUE DESC, CREATION_DATE ASC"

This statement has 2 features I'm using the first time
1) a [inner] Join
2) Sorting in different ways with different

I receive an error by the compiler saying
"... SqlException: near ACTIVITY_ID syntax error (code 1) ...."
but I'm not able to identify the issue.
The name of fields and tables seems to be ok.
I've also checked the statement with a couple of SQLite tutorial (here my reference)
:mad::mad::mad::mad::mad:

Any suggestion will be welcome.
Thanks in advance

Corrado

BTW
Not easy to extract this information, I had to manually type them.
Is there a smarter way to do it?
 
Sort by date Sort by votes
M

Mahares

Expert
Licensed User
Longtime User
You did not tell us which of the 2 tables has what fields and what is the common field for both tables. But since I am guessing which field belongs to what table, this should give you an idea: ACD is the alias for the ACT_CUS_DATA table, A is the alias for the ACTIVITIES table:
B4X: 
txt= "SELECT * FROM ACT_CUS_DATA ACD INNER JOIN ACTIVITIES A ON ACD.ACTIVITY_ID=A.ACTIVITY_ID " _
& "WHERE ACD.CUS_VALUE >= 1339 ORDER BY ACD.CUS_VALUE DESC, ACD.CREATION_DATE"
Cursor1=SQL1.ExecQuery(txt)
 
Last edited:
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Did you have a look at the OsenXPSuite? This Program is not free... But the SQLite Manager is Freeware.
Freeware
SQLite2009 Pro Enterprise Manager
SQLite2009 Pro Enterprise Manager is a freeware tool that enables you to manage your SQLite3 databases.

Free Version (7 Mb )
Last Updated 2011.11.02
Engine version SQLite v3.7.9
Download Now!
Features:
  • Encryption Method is now compatible with wxSQLite3 (AES-128 bits) and SQLite3 ADO.NET Provider (RSA-MS Crypt)
  • Syntax Highlight
  • Hex Viewer
  • Dump database to SQL file format
  • Unicode Support
  • Blob/Image viewer
  • Built-in FTS3 Extention
  • Built-in LUA Programming Language
  • Encrypted database support
  • Export recordset into excel, csv, xml and html format
  • Import data from Ms Access / MS SQL server
  • Includes SQLite2009 Pro ODBC Driver
  • Includes Additional sqlite3 function (compress, decompress, crc32, md5, lua_exec, etc)
  • Transactions supported
  • Visual Query Builder
  • Includes the user-contributed extension-functions from http://www.sqlite.org/contrib.
    The new functions are: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi, replicate, charindex, leftstr, rightstr, reverse, proper, padl, padr, padc, strfilter, and aggregates stdev, variance, mode, median, lower_quartile, upper_quartile.

 
Upvote 0

dexMilano

Active Member
Licensed User
Longtime User
Thanks Mahares,
your clue is correct and your intuition has been the right one.

Even if in this site I've found that there is a compatible clearer syntax, only the "INNER JOIN.." is accepted by the compiler.

This was the issue.

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar Threads

P
  • Question
Android Question Problem with SQL
Replies
8
Views
379
parijs
P
D
  • Question
Android Question SQL Query Question
Replies
2
Views
625
dw_b4x
D
  • Question
Android Question SQL Query??
Replies
7
Views
1K
Mahares
M
  • Question
Android Question Sqlite Join multiple tables
Replies
6
Views
4K
jpvniekerk
I
  • Question
Android Question SQL Multiple Join Errors
Replies
6
Views
2K
IlCasti
I