SubQuery syntax error!

[Jc_sc]

Hi, I'm programming in Java with SQLite. I have a Select with SubQuery, but i receive syntax error. Please Help me!

Code Select Expand

"SELECT _id,n_cto,datldo,sal FROM Conti ORDER BY n_cto ASC" + "(SUM(eta)-SUM(uta) FROM Giornate " + "WHERE "+GTable.DATE+" BETWEEN "+FirstdDayOfYear+" AND "+dayOfWeek+")";

[Rolf Brandt]

Could you give us the comple Error message? Usually you would ge a message like "Syntax error near ...". That might help.

Rolf

[Jc_sc]

the syntax error is reported here

Code Select Expand

ASC" + "(SUM

[Gary Stout]

I am only guessing here, but you may need to add a space after ASC " or before " (SUM.

Gary

[cj]

Creating a program to see error:


"SELECT _id,n_cto,datldo,sal FROM Conti ORDER BY n_cto ASC" + "(SUM(eta)-SUM(uta) FROM Giornate " + "WHERE "+GTable.DATE+" BETWEEN "+FirstdDayOfYear+" AND "+dayOfWeek+")";

[Rolf Brandt]

I think Gary is probably right.

Rolf

[cj]

I thought there was an extra (
removed that and was working on the SUM statement.
Also thought DATE keyword would be needed later on before  FirstdDaydOfYear and dayOfWeek.

After looking more the first ( is needed .

Looking at the original source it does look like a space is needed after the ASC.

Hope he got it working?



#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"

TYPE MyType
  Date AS STRING * 10
END TYPE

FUNCTION PBMAIN () AS LONG
  LOCAL gTable AS MyType
  LOCAL FirstdDayOfYear AS STRING
  LOCAL DayOfWeek       AS STRING
  LOCAL sql             AS STRING

  gTable.Date     = "field1"
  FirstdDayOfYear = "field2"
  DayOfWeek       = "field3"

  slConnect "192.168.1.2",0
  slOpen "test.db3","C"
  slExe "drop table if exists Conti"
  slExe "drop table if exists Giornate"
  slExe "create table if not exists Conti(_id,n_cto,datldo,sal)"
  slExe "create table if not exists Giornate(f1,f2,f3)"


sql = "SELECT * FROM Conti ORDER BY n_cto ASC " + _
   "SUM(eta) AS Total -SUM(uta) FROM Giornate "
'    + _
'   "WHERE DATE("+GTable.DATE + ") BETWEEN "  + _
'   "DATE(" + FirstdDayOfYear + ") AND DATE(" + dayOfWeek + " )"

   slsel sql

'sql = "SELECT _id,n_cto,datldo,sal FROM Conti ORDER BY n_cto ASC" + "(SUM(eta)-SUM(uta) FROM Giornate " + "WHERE "+GTable.DATE+" BETWEEN "+FirstdDayOfYear+" AND "+dayOfWeek+")"


END FUNCTION