SQL - IF STATEMENT throw error on SQL syntax

Question

I have a little bit bigger SQL query and having problem with it

Error I get:

#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 'GROUP_CONCAT(DISTINCT IF(po.repairmethod != 'E' OR (po.repairmethod = 'E' AND po' at line 6

QUERY i use:

SELECT c.vin, c.case_id, c.claimnumber, c.platenumber, c.axrmrs_id, c.insurer_memberid, c.country, c.date_created, c.totalloss, c.lastcalc_manufacturer_code, c.lastcalc_model_code, c.lastcalc_submodel_code, c.audavin_triggered, c.accident_date, c.registration_date, c.manufacturing_year,
                      cl.spareparts, cl.totalcosts, cl.laborhours, cl.laborcosts, cl.calculationdate, cl.paintlabor, cl.paintmaterial, cl.currency, car.manufacturer, car.model, car.submodel,
                      IFNULL(org.name, 0) as orgName,
                      GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'L%',po.text,NULL) ORDER BY 1) AS textL,
                      GROUP_CONCAT(DISTINCT IF(po.repairmethod = 'E',po.text,NULL) ORDER BY 1) AS textE
                      GROUP_CONCAT(DISTINCT IF(po.repairmethod != 'E' OR (po.repairmethod = 'E' AND po.guidenumber = 'N/A') AND po.repairmethod NOT LIKE 'L%',NULL) ORDER BY 1) AS textO
                    FROM axnmrs_cases AS c
                      LEFT JOIN axnmrs_calculations as cl on c.case_id = cl.case_id AND c.country = cl.country
                      LEFT JOIN axnmrs_positions as po on c.case_id = po.case_id
                      LEFT JOIN car_type as car on car.manufacturer_code = c.lastcalc_manufacturer_code AND car.main_type = c.lastcalc_model_code AND car.subtype_code = c.lastcalc_submodel_code
                      LEFT JOIN organization_list as org on org.memberId = c.insurer_memberid
                    WHERE c.vin= "U5YFF24128L064909"
                      GROUP BY c.vin, c.case_id, c.axrmrs_id

I was thinking problem was in brackets but I try to replace it unfortunately without success.

Whole query work correctly without last GROUP_CONCAT LINE ( as error already say basicly )

Any hints what's I am doing wrong or how to optimalizate this query? Thanks :)

EDIT: error after added comma at the end of second GROUP_CONCAT:

#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 ') ORDER BY 1) AS textO FROM axnmrs_cases AS c ' at line 6

Please add the related tag. GROUP_CONCAT is not in sql server, so remove the sql server tag — Krishnraj Rana
– Krishnraj Rana, Commented Dec 16, 2015 at 16:19
you may be missing a comma after "AS textE", before the next GROUP_CONCAT. — BWS
– BWS, Commented Dec 16, 2015 at 16:27

ScaisEdge · Accepted Answer · 2015-12-16 17:02:39Z

2

found one

mssing a comma at the end of this like in sample

GROUP_CONCAT(DISTINCT IF(po.repairmethod = 'E',po.text,NULL) ORDER BY 1) AS textE

in this way

GROUP_CONCAT(DISTINCT IF(po.repairmethod = 'E',po.text,NULL) ORDER BY 1) AS textE,

and also missing a parameter third concat see ****missing****

GROUP_CONCAT(DISTINCT IF(po.repairmethod != 'E' OR (po.repairmethod = 'E' AND po.guidenumber = 'N/A') 
 AND po.repairmethod NOT LIKE 'L%',****missing ***,NULL) ORDER BY 1) AS textO

edited Dec 16, 2015 at 17:02

answered Dec 16, 2015 at 16:28

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andurit Over a year ago

hey @scaisEdge , thank you for this. You are right but dispite this I get same error just at another part of code, please check my edit:)

ScaisEdge Over a year ago

I have found another error and have update the answer

Andurit Over a year ago

Thank you, now it works! I really appriciate your help

Oscar Pérez · Accepted Answer · 2015-12-16 16:28:46Z

You lack a commaafter your seccond GROUP_CONCAT.

SELECT c.vin, c.case_id, c.claimnumber, c.platenumber, c.axrmrs_id, c.insurer_memberid, c.country, c.date_created, c.totalloss, c.lastcalc_manufacturer_code, c.lastcalc_model_code, c.lastcalc_submodel_code, c.audavin_triggered, c.accident_date, c.registration_date, c.manufacturing_year,
                  cl.spareparts, cl.totalcosts, cl.laborhours, cl.laborcosts, cl.calculationdate, cl.paintlabor, cl.paintmaterial, cl.currency, car.manufacturer, car.model, car.submodel,
                  IFNULL(org.name, 0) as orgName,
                  GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'L%',po.text,NULL) ORDER BY 1) AS textL,
                  GROUP_CONCAT(DISTINCT IF(po.repairmethod = 'E',po.text,NULL) ORDER BY 1) AS textE ,
                  GROUP_CONCAT(DISTINCT IF(po.repairmethod != 'E' OR (po.repairmethod = 'E' AND po.guidenumber = 'N/A') AND po.repairmethod NOT LIKE 'L%',NULL) ORDER BY 1) AS textO
                FROM axnmrs_cases AS c
                  LEFT JOIN axnmrs_calculations as cl on c.case_id = cl.case_id AND c.country = cl.country
                  LEFT JOIN axnmrs_positions as po on c.case_id = po.case_id
                  LEFT JOIN car_type as car on car.manufacturer_code = c.lastcalc_manufacturer_code AND car.main_type = c.lastcalc_model_code AND car.subtype_code = c.lastcalc_submodel_code
                  LEFT JOIN organization_list as org on org.memberId = c.insurer_memberid
                WHERE c.vin= "U5YFF24128L064909"
                  GROUP BY c.vin, c.case_id, c.axrmrs_id

hey @Oscar Pérez, thank you. You are right but dispite this fact It's not working :( Please check my edit

Collectives™ on Stack Overflow

SQL - IF STATEMENT throw error on SQL syntax

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related