Operation in a sql in a macro

asked 2019-11-07 15:22:57 +0100

hermanv
9 ●6 ●12 ●16

updated 2019-11-07 17:19:19 +0100

Ratslinger
26546 ●9 ●35 ●135

In the SQL tool this works OK insert into "Ledenarchief" ("archlidid","Naam") select "lidid" + 1000 ,"Naam" from "Leden" where "Datum ontslag" is not null or "datum overlijden" is not null When I want to do this in a macro (first time I do this), like

SQL = "INSERT INTO ""Ledenarchief"" " & _
"(""archlidid"" " & _
",""OKRAlidnr"" " & _
",""Naam"" " & _
") " & _
"SELECT" & _
" ""lidid"" "+1000" " & _
",""OKRAlidnr"" " & _
",""Naam"" " & _
"FROM ""Leden"" " & _
"WHERE ""Leden"".""Datum ontslag"" "is not null" "or" ""Leden"".""datum overlijden"" "is not null""

I get error BASIC syntax error. Unexpected symbol: ???. and the "+1000" is hightlighted. So I must have made a mistake somewhere, but I ddon't get it. Someone can help??

edit retag flag offensive close merge delete

Comments

By "" "lidid" "" +1000 "" do you mean the name of a lidid1000 field or a calculated field?

In the first case I believe that "" lidid "" + cstr (1000) should be used, making sure that cstr (1000) does not start with a space that should therefore be removed with the right function (str, n).

But you would still write "" lidid1000 "" first

In the second case it seems to me that a calculated field must be written in the form (expression) as CalcFieldName. If you mean anything else you should explain it.

The error you get indicates that +1000 is interpreted either as a field name or as an unknown operator

torreone ( 2019-11-07 16:16:19 +0100 )edit

The meaning is to add the value 1000 to the value of the lidid field (which is numeric, archlidid as well). Can you explain how to do this. I don't find a proper explanation by googling, maybe by not using the proper terminology.

hermanv ( 2019-11-07 16:54:07 +0100 )edit

try to replace " ""lidid"" "+1000" " with

" (""lidid"" + 1000) as ""fldNameYouWant"" "

torreone ( 2019-11-07 17:27:53 +0100 )edit

That does away with this error, tx, but up to the next one: same error for "WHERE ""Leden"".""Datum ontslag"" "is not null", and the "null" gets highlighted.

hermanv ( 2019-11-07 17:42:58 +0100 )edit

try " WHERE ""Leden"".""Datum ontslag"" is not null or ""Leden"".""datum overlijden"" is not null " at the end of your sql string command query

torreone ( 2019-11-07 18:11:10 +0100 )edit

That did it, tx a lot.

hermanv ( 2019-11-08 14:10:17 +0100 )edit

add a comment

2 Answers

Sort by » oldest newest most voted

answered 2019-11-07 21:16:07 +0100

torreone

194 ●12 ●21 ●32

updated 2019-11-07 21:16:46 +0100

I pick up Ratslinger's invitation and report the answer from my comments.

I didn't have the time to try the solution in a macro so I preferred to wait for a confirmation from whoever opened the discussion

The problem is that, since the substrate sql is already enclosed in quotes, the sections +1000, not null, etc were not to be enclosed further in single quotes. In doing so they were misinterpreted.

As confirmed by Ratslinger the solution was

SQL = "insert into" "Ledenarchief" "(" "archlidid" "," "Naam" ") select" "lidid" "+ 1000," "Naam" "from" "Leden" "where" "Datum ontslag" " is not null or "" datum overlijden "" is not null "

edit flag offensive delete link

add a comment

answered 2019-11-07 20:19:10 +0100

Ratslinger
26546 ●9 ●35 ●135

Hello,

First @torreone has presented the correct solution to your problem and the comments should be converted to an actual answer.

But what is not covered is 'why' and how you could not find an answer with a search. The problem is with converting your statement into an actual string in basic. Normally this is simply surrounding the characters with quotes.

Your original statement working in Tools->SQL:

insert into "Ledenarchief" ("archlidid","Naam") select "lidid" + 1000 ,"Naam" from "Leden" where "Datum ontslag" is not null or "datum overlijden" is not null

Now create a string by surrounding with quotes:

SQL = "insert into "Ledenarchief" ("archlidid","Naam") select "lidid" + 1000 ,"Naam" from "Leden" where "Datum ontslag" is not null or "datum overlijden" is not null"

This creates a problem as once the second quote is found the string ends and the interpreter is looking for more basic syntax. Since Table and Fields names here need to be surrounded with quotes themselves, to be used within a string they need to be double quoted. For your string no other quoting is necessary and the result is:

SQL = "insert into ""Ledenarchief"" (""archlidid"",""Naam"") select ""lidid"" + 1000 ,""Naam"" from ""Leden"" where ""Datum ontslag"" is not null or ""datum overlijden"" is not null"

The need for quoting Table and Field names can be overcome depending upon the database used (you do not specify).

HSQLDB can use uppercase without quotes (no spaces). This can make SQL statements a lot easier to enter.

edit flag offensive delete link

Comments

That explains a lot to me, and BTW, it's Firebird as I got somewhere the note that HSQLDB is (or will become) a dead end. Tx for your help.

hermanv ( 2019-11-08 14:13:57 +0100 )edit

@hermanv,

Since you question was answered by torreone, you should mark it as the correct answer. This also helps others know the question was answered.

Ratslinger ( 2019-11-08 17:26:21 +0100 )edit

add a comment

Operation in a sql in a macro

Comments

2 Answers

Comments

Question Tools

Stats

Related questions

Operation in a sql in a macro edit

Comments

2 Answers

Comments

Question Tools

Stats

Related questions

Operation in a sql in a macro