Android Question SQL JOIN and multiple ORDER BY

[dexMilano]

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)


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?

 

[Mahares]

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:

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)

 

[JakeBullet70]

Have you see SQLite2009 Pro Enterprise Manager?

It has a Query designer that helps you write the SQL.

 

[LucaMs]

Have you see SQLite2009 Pro Enterprise Manager?

It has a Query designer that helps you write the SQL.

... but it is not free

 

[DonManfred]

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.
  
  

 

[dexMilano]

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:

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)

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