MySQL Question

Syntaxis error in Create View MySQL

I'm trying to create a View that draws the last entry from Logs (Registro tracto and registro Operador) corresponding to the trip (viaje) of a LoadOrder (Orden de carga) and shows every 'Load Order with info' E-R diagram

I've sketched the next view but I can't make it work because of Sytaxis error.

`CREATE VIEW `OrdenEnCurso` AS
SELECT OC.'NO_ORDEN_DE_CARGA' AS 'ORDEN DE CARGA'
, OC.'ID_VIAJE' AS 'ID DE VIAJE'
, RT.'TRACTO_ECO' AS 'TRACTO'
, ROO.'NOMBRE' AS 'N.OPERADOR',
GF.S AS 'GASTOS FINANCIADOS'
FROM
'ORDEN_DE _CARGA' AS OC
INNER JOIN (SELECT 'TRACTO_ECO','ID_VIAJE' FROM 'REGISTRO_TRACTO' GROUP BY
'ID_VIAJE' HAVING MAX('FECHAHORA') ) AS RT
ON OC.ID_VIAJE=RT.ID_VIAJE
INNER JOIN (SELECT CONCAT_WS(',',O.AP_PAT,O.AP_MAT,O.NOMBRE) AS 'NOMBRE'
FROM 'OPERADOR' AS O
INNER JOIN (SELECT 'ID_VIAJE','NO_OPERADOR' FROM 'REGISTRO_OPERADOR'
GROUP BY 'ID_VIAJE' HAVING MAX('FECHAHORA') ) AS RO
ON RO.NO_OPERADOR=O.NO_OPERADOR) AS ROO
ON OC.ID_VIAJE=ROO.ID_VIAJE
INNER JOIN (SELECT 'OC',SUM(IMPORTE) AS S FROM 'GASTOS_F_CLIENTE'
GROUP BY
'NO_ORDEN_DE_CARGA') AS GF ON OC.NO_ORDEN_DE_CARGA=GF.OC;


Also, as you may see, I would like to concat the Operator name separetaed with a coma. And Finally sum how much the client will pay extra per LoadOrder.

Answer Source

You are confusing the use of ticks and quotation marks

Try this SQL statement

CREATE VIEW OrdenEnCurso AS
    SELECT OC.NO_ORDEN_DE_CARGA AS `ORDEN DE CARGA`,
        OC.ID_VIAJE AS `ID DE VIAJE`,
        RT.TRACTO_ECO AS TRACTO,
        ROO.NOMBRE AS `N.OPERADOR`, 
        GF.S AS `GASTOS FINANCIADOS`
    FROM ORDEN_DE_CARGA AS OC 
    INNER JOIN (
        SELECT TRACTO_ECO, ID_VIAJE
        FROM REGISTRO_TRACTO 
        GROUP BY ID_VIAJE
        HAVING MAX(FECHAHORA) 
        ) AS RT 
        ON OC.ID_VIAJE = RT.ID_VIAJE
    INNER JOIN (
            SELECT CONCAT_WS(`,`,O.AP_PAT,O.AP_MAT,O.NOMBRE) AS NOMBRE
            FROM OPERADOR AS O 
        INNER JOIN (
            SELECT ID_VIAJE, NO_OPERADOR 
            FROM REGISTRO_OPERADOR
            GROUP BY ID_VIAJE 
            HAVING MAX(FECHAHORA) 
            ) AS RO 
            ON RO.NO_OPERADOR=O.NO_OPERADOR
        ) AS ROO
        ON OC.ID_VIAJE=ROO.ID_VIAJE
    INNER JOIN (
        SELECT OC,SUM(IMPORTE) AS S 
        FROM GASTOS_F_CLIENTE
        GROUP BY NO_ORDEN_DE_CARGA
        ) AS GF 
        ON OC.NO_ORDEN_DE_CARGA=GF.OC;