using cast in sql in libre office base

Question

I am using libreOffice Base to query an external (access.mdb) database which is predefined and cannot be altered. This is because I get a new database every day and have to create reports based on the included data.

In this database on row is declared as varchar(50) but the values are digital(10,2) and i need those values to calculate some results.

Now I am trying to cast the row into an new digital row, but it fails:

select cast(´myVal´,digital[10,2]) as numVal from ´myTable´

myVal is as mentioned above defined to be varchar[50]

Can anyone tell me how the right syntax for the cast works?

Hi Mark, Base tells me the syntax is wrong. But its an very simple errormessage with no more details. — Frankk, Jun 23 '17 at 09:30
Take a look at https://support.office.com/en-us/article/Type-Conversion-Functions-8EBB0E94-2D43-4975-BB13-87AC8D1A2202 ; as far as I'm aware `digital(10, 2)` is not an access data type. — Mark Rotteveel, Jun 23 '17 at 09:32
which syntax do i have to use the access syntax or the base syntax — Frankk, Jun 23 '17 at 09:34
As far as I know, you need to use the syntax of the database system you access: so the Microsoft Access syntax. — Mark Rotteveel, Jun 23 '17 at 09:36
Hi Mark, I tried an cast to varchar(50) or varchar with the same result — Frankk, Jun 23 '17 at 09:43
That is because Access doesn't have `CAST`, you need to use the conversion functions listed in the link I posted in my second comment (at least, I think so, it has been a while since I have done anything with Access). — Mark Rotteveel, Jun 23 '17 at 09:56

score 0 · Answer 1 · answered Jun 23 '17 at 10:16

0

With the Hint of Mark I found the right syntax. Its the Syntax of the database system I accessed - means in this case the syntax from the access.mdb

So the right syntax is:

select clng(´myVal´) as numVal from ´myTable´

answered Jun 23 '17 at 10:16

Frankk

3
1

using cast in sql in libre office base

1 Answers1