[Solved] 'SELECT CONCAT' returns unknown function error

Creating tables and queries
Post Reply
User avatar
pierrick
Posts: 8
Joined: Sat May 10, 2008 4:08 pm

[Solved] 'SELECT CONCAT' returns unknown function error

Post by pierrick »

Hi all,

Desperately trying to create simple queries (or so I thought!) using basic SQL statements (or so I thought!) which consistently return function errors.
I'm linking to a MsAccess database, I can see the table, I can see the data. So far so good.
I then want to create a query to reorganise the table data (sorting and concatenating various columns.) 3 hours later and I still can't figure out why

Code: Select all

SELECT CONCAT(`Col1`, `Col2`) AS Col3 FROM `Table1`
returns 'unknown function CONCAT'. It works in MySQL. The Base help tells me there is such a string function as 'CONCAT'. Why can't I use it to build a simple query?

Help please?
Last edited by pierrick on Mon Sep 01, 2008 7:23 pm, edited 2 times in total.
Kind regards,
Pierrick
User avatar
TheGurkha
Volunteer
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: Another lost newbie

Post by TheGurkha »

Hi and welcome to the forums.

You'll probably get a better response if you go to your first post and then use the Edit button and change the thread title to something more descriptive of the problem you're having: 'Select concat returns unknown function error', for example.
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
User avatar
pierrick
Posts: 8
Joined: Sat May 10, 2008 4:08 pm

Re: 'SELECT CONCAT' returns unknown function error

Post by pierrick »

Thanks for the suggestion TheGurkha, I've done the change. I'll see what happens.

Er. You wouldn't know why this unknown function error occurs would you?
Kind regards,
Pierrick
User avatar
TheGurkha
Volunteer
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: 'SELECT CONCAT' returns unknown function error

Post by TheGurkha »

No, sadly. I'm familiar with MySQL / SQL Server and some SQL, but I haven't done much in Base yet.

Someone much better able to handle your query will be along soon, I'm sure.
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: 'SELECT CONCAT' returns unknown function error

Post by Sliderule »

pierrick:

You said:
peirrick wrote:I'm linking to a MsAccess database, I can see the table, I can see the data.
Since, OpenOffice BASE, in your case, is using the database syntax of MsAccess . . . correct syntax to use would be:

Code: Select all

SELECT `Col1` + `Col2` AS `Col3` FROM `Table1`
Let me attempt to explain what is happening in the background . . . between OpenOffice Base and MsAccess. IF, you write your query in the GUI ( Graphical User Interface ) . . . for example . . . from Create Query in Design View... . . . Base will take the Query and put it through the 'parser' ( including checking for any Parameter Input . . . see the Base Help File ) to confirm the SQL syntax is 'valid'.

BUT, if you need to send some SQL 'directly' to MsAccess . . . withOUT Base checking the syntax ( for instance . . . if you are using some MsAccess function(s) that Base does not recognise ) . . . this CAN be done. That way, if the SQL is understood by MsAccess . . . it WILL work. The above is accomplished from the menu . . . Edit -> Run SQL command directly... MUST HAVE A CHECK MARK

So, in your case . . . MsAccess does NOT recognise CONCAT . . . BUT . . . in order to concatenate fields ( columns ) . . . use a plus . . . + . . . so . . . if you wanted to put an additional space between the values . . . it would look like . . . `Col1` + ' ' + `Col2` AS `Col3` AND with Edit -> Run SQL command directly... MUST HAVE A CHECK MARK . . . this WILL work.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
User avatar
pierrick
Posts: 8
Joined: Sat May 10, 2008 4:08 pm

Re: 'SELECT CONCAT' returns unknown function error

Post by pierrick »

Hi Sliderule,

Thank you for your extensive reply. I kind of understand what is going on with the MsAccess link and how it would restrict SQL syntax.
And of course thank you very much for solving my problem.
Post Reply