| 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
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
