| CONCAT ((SELECT ...),...) Error [message #1089] |
Sat, 08 September 2007 10:56  |
luca
Messages: 3
Registered: September 2007
|
Junior Member |
|
|
Hi Tony,
I have 3 tables:
instruments:
- instrument_id -----------------> unsigned int(10) (PK)
- instrument_model_id --------> unsigned int(10) (FK)
.....
instrument_models:
- instrument_model_id ----------------> unsigned int(10) (PK)
- instrument_type_id ------------------> unsigned int(10) (FK)
- instrument_model_name -----------> varchar(32)
- instrument_model_manufacturer --> varchar(32)
.....
instrument_types:
- instrument_type_id --------------> unsigned int(10) (PK)
- instrument_type_name ---------> varchar(32)
.....
I use a LIST1 Transaction Pattern to show "instruments" data.
I'd like to show a new field "instrument_model" as the concatenation of:
"instrument_type_name - instrument_model_manufacturer instrument_model_name"
First of all, I write in instruments.list1.screen.inc:
$structure['main']['fields'][] = array('instrument_model' => 'Instrument Model');
Then, I create a relationship between instrument_models(snr) and instruments(jnr):
Relationship Type: CASCADE
Order By: instrument_model_id
Parent Field: CALCULATED
Calculated Field: CONCAT((SELECT instrument_type_name FROM instrument_types WHERE instrument_models.instrument_type_id = instrument_types.instrument_type_id),' - ',instrument_model_manufacturer,' ',instrument_model_name) AS instrument_model
Column Id(snr): instrument_model_id
Column Id(jnr): instrument_model_id
I "Export To PHP" the tables and this produces the lines in instruments.dict.inc:
// parent relationship details
$this->parent_relations[] = array('parent' => 'instrument_models',
'parent_field' => 'CONCAT((SELECT instrument_type_name FROM instrument_types WHERE instrument_models.instrument_type_id = instrument_types.instrument_type_id),\' - \',instrument_model_manufacturer,\' \',instrument_model_name) AS instrument_model',
'fields' => array('instrument_model_id' => 'instrument_model_id'));
BUT, when I go to enter instruments menu, I read the error windows:
Fatal Error: MySQL error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), AS instrument_model FROM instruments LEFT JOIN instrument_models ON (instrume' at line 1 (# 1064).
SQL query: SELECT SQL_CALC_FOUND_ROWS instruments.*, CONCAT((SELECT instrument_type_name FROM instrument_types WHERE instrument_models.instrument_type_id = instrument_types.instrument_type_id), ' - ', instrument_models.instrument_model_manufacturer, ' ', instrument_models.instrument_model_name, ), AS instrument_model FROM instruments LEFT JOIN instrument_models ON (instrument_models.instrument_model_id=instruments.instrumen t_model_id) LIMIT 10 OFFSET 0
Error in line 407 of file 'C:\Programmi\wamp\www\wis\includes\dml.mysqli.class.inc'.
What is wrong of my procedure?
Is there another way to do it?
Thanks for your support,
Luca
|
|
|
|
| Re: CONCAT ((SELECT ...),...) Error [message #1090 is a reply to message #1089] |
Sat, 08 September 2007 13:34  |
AJM
Messages: 2368
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
Trying to use a SELECT statement inside a CONCAT(..) is causing the problem. The framework has routines which unpacks the SELECT statement to see if any of its component parts need to be qualified, then it repacks then back together again. Unfortunately it appears that your mixture of SELECT and CONCAT is causing the unpacking and repacking to become confused.
You should try using a JOIN to get the columns from the related tables, then use those column names inside the CONCAT(..).
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
| Re: CONCAT ((SELECT ...),...) Error [message #1092 is a reply to message #1091] |
Sun, 09 September 2007 11:18 |
AJM
Messages: 2368
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
The default SELECT statement created by Radicore should be as follows:
SELECT instruments.*
FROM instruments
LEFT JOIN instrument_models ON (instrument_models.instrument_model_id = instruments.instrument_model_id)
You need to extend this in your _cm_pre_getdata() method (as shown in http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq84) so that it becomes as follows:
SELECT instruments.*, CONCAT (instrument_type_name, ' - ',
instrument_model_manufacturer, ' ',
instrument_model_name) AS instrument_model
FROM instruments
LEFT JOIN instrument_models ON (instrument_models.instrument_model_id = instruments.instrument_model_id)
LEFT JOIN instrument_types ON (instrument_type.instrument_type_id = instrument_models.instrument_type_id)
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
