Help with Sqlite query

Databases
1

This query works fine on Valentina query analysis but in XOJO have an error any idea way.

[code]StrSql="?SELECT dataLocate, count(1) AS quota, Sum(CASE WHEN YesNo=‘Yes’ THEN 1 ELSE 0 END) as Yego, " _

  • " Sum(CASE WHEN YesNo=‘No’ THEN 1 ELSE 0 END) NO, " _
  • " Sum(CASE WHEN YesNo NOT IN (‘Yes’,‘No’) THEN 1 ELSE 0 END) as Nose, " _
  • " FROM Socios GROUP BY dataLocate ORDER BY dataLocate "[/code]

error
near “?SELECT”: syntax error

Thanks

2

I never used a , before FROM but my experience is limited. I don’t know if that is the problem or not.

Try changing ‘Nose,’ to ‘Nose’

Edit: on line:

+ " Sum(CASE WHEN YesNo='No' THEN 1 ELSE 0 END) NO, " _

change to

+ " Sum(CASE WHEN YesNo='No' THEN 1 ELSE 0 END) as NO, " _
3

Nose
this is a name for the column

4

he meant remove the “,” … .plus add “AS” into the previous line

5

ok
thanks

6

Thank you Dave, I was editing my post to add the missing “AS” but I guess I made a mistake (I don’t see the change). Now is clearer.

7

StrSql="??SELECT dataLocate, count(1) AS quota, Sum(CASE WHEN YesNo=‘Yes’ THEN 1 ELSE 0 END) AS Yego, " _

  • " Sum(CASE WHEN YesNo=‘No’ THEN 1 ELSE 0 END) AS NO, " _
  • " Sum(CASE WHEN YesNo NOT IN (‘Yes’,‘No’) THEN 1 ELSE 0 END) AS Noo " _
  • " FROM Socios " _
  • " GROUP BY dataLocate " _
  • " ORDER BY dataLocate "

Same Error MSG near “?SELECT”: syntax error

8

If you are copying/pasting the query from Valentina to Xojo, try pasting it into a plain text editor, then copying it from there. I’ve had problems with invalid characters in these situations.

9

What does “count(1)” mean in a SELECT ?

10

It means to count all the records (based on GROUP BY) … the “1” could be anything , usually developers use “*” or “8”… but its value is only important unless you are counting SPECIFIC values (a discussion for another time)

so in this case the field “quota” will contain a count for each unique value of dataLocate

11

Is the error on the assignment to the StrSQL variable or on execution of the SQL? If on execution, perhaps the “AS NO,” in the second line is clashing with a reserved word for the database, which then returns an error which gets interpreted as a SQL Syntax issue.

12

Is there a Smart Quote in there somewhere?

13

I did a test, I copied the complete code posted by Alexis from the forum (1st post) and I get this when I run the test:

Then I typed strsql = " and just copy from SELECT until the end and I get this:

Could it be some invalid character(s) between =" and SELECT ?

14

Yes, this is the problem I’ve encountered more than once. There are invisible characters in there that causes issues with the Xojo plugin. I don’t know if they’re always present in the Valentina editor, or only added when copying from it.

15

yes the problems is copy paste from valentina now works.

thanks

16

Just seeing this thread now, I already reported this to the valentina developers a week ago btw. They will display non printable characters in their sql editor in an upcoming version.

17

BBEdit has a handy “Zap Gremlins” command that can fix most invisible character issues.

18

So does Xojo :stuck_out_tongue:

19

Thank you Norman, I learn new things every day.

20

select text
right click
Clean invisible ascii characters