我有一个在sql Server 2008中运行良好的查询但是当我尝试将它与
MySQL一起使用时,它似乎是JOIN的问题.
第一个错误是
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 ‘ON faxcont.IDDireccion = dir_cont.RecID AND faxcont.Tipo = 2 AND faxcont.Defecto’ at line 52″
如何纠正？
SELECT req.RecID,req.FechaEntrega,req.IDUsuario AS RecIDResp,req.NroSucursal,req_dp.NroFila,req.ID,req.ID AS NroReq,emp.Empresa AS NombreEmpresa,req.IDFiscalSistema,req_dp.ImporteUnitario1,req_dp.ImporteUnitario2,req_dp.ImporteUnitario3,req_dp.ImporteUnitario4,req_dp.ImporteUnitario5,req_dp.ImporteUnitario6,req.NroMoneda,req_dp.SobrePrecio,req_dp.Impuesto AS TasaIVA,dir_emp_sist.CodPais AS CodPaisEmpresa,dir_emp_sist.CodCiudad AS CodCiudadEmpresa,tel.numero AS TelefonoEmpresa,fax.numero AS FaxEmpresa,mail_emp.Direccion AS MailEmpresa,emp_sist.SitioWeb AS WebEmpresa,dir_cont.CodPais AS CodPaisCont,dir_cont.CodCiudad AS CodCiudadCont,telcont.numero AS TelefonoCont,faxcont.numero AS FaxCont,req.FechaModificacion AS FechaGrabacion,cont.Apellido AS ApellidoCont,cont.Nombre AS NombreCont,mail_cont.Direccion AS MailCont,emp_sist.Empresa AS NombreEmpSist,fis_sist.RazonSocial AS RazonSocialSist,fis_sist.NroImpuesto1 AS CUIT,dir_fis_sist.Calle AS CalleFisSist,dir_fis_sist.Numero AS NroFisSist,dir_fis_sist.Piso AS PisoFisSist,dir_fis_sist.Dto AS DtoFisSist,dir_fis_sist.CP AS CPFisSist,dir_fis_sist.Localidad AS LocFisSist,dir_fis_sist.Ciudad AS CiudadFisSist,dir_fis_sist.Provincia AS ProvinciaFisSist,dir_fis_sist.Pais AS PaisFisSist,responsable.Nombre AS NombreResp,responsable.Apellido AS ApellidoResp,mail_resp.Direccion AS MailResp,dir_resp.CodPais AS CodPaisResp,dir_resp.CodCiudad AS CodCiudadResp,telresp.numero AS TelefonoResp,com.DireccionEntrega AS DirEntrega,CASE PATINDEX('%.%',req_dp.NroItem) WHEN 0 THEN '0' WHEN 2 THEN '1' END AS Item,req_dp.NroItem) WHEN 0 THEN req_dp.NroItem WHEN 2 THEN LEFT(req_dp.NroItem,1) END AS Item2,req_dp.NroItem,req_dp.Codigo,req_dp.Descripcion,CASE WHEN req_dp.recid IS NULL
THEN 0 WHEN req_dp.Estado = 9 THEN 0 ELSE (CASE mon.Numero WHEN 1 THEN req_dp.ImporteUnitario1 WHEN 2 THEN req_dp.ImporteUnitario2 WHEN 3 THEN req_dp.ImporteUnitario3
WHEN 4 THEN req_dp.ImporteUnitario4 WHEN 5 THEN req_dp.ImporteUnitario5 WHEN 6 THEN req_dp.ImporteUnitario6 END) * ((100 - req_dp.Descuento) / 100)
* ((100 + req_dp.SobrePrecio) / 100) END AS PrecIoU,req_dp.Cantidad,(CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4 THEN req_dp.importeprecio4
WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) AS Importe,req_dp.Descuento AS DescProd,CASE WHEN req_dp.Descuento = 0 THEN 0 ELSE ((CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3
WHEN 4 THEN req_dp.importeprecio4 WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END)
- ((req_dp.Descuento * (CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4
THEN req_dp.importeprecio4 WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) / 100)))
- (CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4 THEN req_dp.importeprecio4
WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) END AS ImpDescXProd,req.SubTotal,req.Descuento AS PorcDescGral,req.SubTotal - req.SubTotal * ((100 - req.Descuento) / 100) AS DescuentoGral,req.SubTotal2 AS SubTotalConDto,req.Impuesto AS IVA,req.Total,mon.Simbolo,mon.Moneda,req.ID AS NroRequerimiento,req.MostrarImpuestos,CASE req.MostrarImpuestos WHEN 1 THEN 'Los valores están expresados en ' + mon.Moneda + '. Incluyen IVA.' WHEN 0 THEN 'Los valores están expresados en ' + mon.Moneda
+ '. No incluyen IVA.' END AS LeyendaIVA,com.CondicionesPago AS CondPago,com.FormaPago,com.PlazoEntrega,com.HorarioEntrega,com_t.Tipo AS TipoTransp,com_t.valor AS TranspPropio,emp_t.Empresa AS EmpTransp,fis_t.RazonSocial AS RazSocTransp,com.PrecioEnvio,adi.Campo1 AS Adi1,adi.Campo2 AS Adi2,adi.Campo3 AS Adi3,adi.Campo4 AS Adi4,adi.Campo5 AS Adi5,adi.Campo6 AS Adi6,adi.Campo7 AS Adi7,adi.Campo8 AS Adi8,adi.Campo9 AS Adi9,adi.Campo10 AS Adi10,adi.Campo11 AS Adi11,adi.Campo12 AS Adi12,adi.Campo13 AS Adi13,adi.Campo14 AS Adi14,adi.Campo15 AS Adi15,adi.Campo16 AS Adi16,adi.Campo17 AS Adi17,adi.Campo18 AS Adi18,adi.Campo19 AS Adi19,adi.Campo20 AS Adi20,ped_1.IDRequerimiento AS IDPed,ped2_1.RecID AS IDPed2,ped3.RecID AS IDPed3,req_dp.Notas,req.Descuento AS DesReq,cont.IDContacto AS RecidContReq,usu.RecID AS RecidContResp,direcciones.Calle,direcciones.Numero,direcciones.Piso,direcciones.Dto,direcciones.Torre,direcciones.CP,direcciones.Ciudad,direcciones.Provincia,direcciones.Pais,direcciones.Defecto AS Dir_Cont_Defecto,direcciones.RecID AS Dir_Cont_Recid
FROM telefonos AS faxcont RIGHT OUTER JOIN
telefonos AS telcont RIGHT OUTER JOIN
direcciones AS dir_cont RIGHT OUTER JOIN
fiscal AS fis_sist INNER JOIN
requerimientos AS req INNER JOIN
requerimientositems AS req_dp ON req.RecID = req_dp.IDRequerimiento INNER JOIN
moneda AS mon ON req.NroMoneda = mon.Numero INNER JOIN
contactos AS cont ON req.IDRef = cont.IDContacto INNER JOIN
empresas AS emp ON cont.IDEmpresa = emp.IDEmpresa ON fis_sist.RecID = req.IDFiscalSistema INNER JOIN
empresas AS emp_sist ON fis_sist.IDref = emp_sist.IDEmpresa LEFT OUTER JOIN
direcciones ON emp.IDEmpresa = direcciones.idref LEFT OUTER JOIN
contactos AS responsable INNER JOIN
usuarios AS usu ON responsable.IDContacto = usu.IDContacto ON req.IDUsuario = usu.RecID LEFT OUTER JOIN
direccionescorreo AS mail_resp ON responsable.IDContacto = mail_resp.IDref AND mail_resp.Defecto = 1 LEFT OUTER JOIN
direcciones AS dir_emp_sist ON emp_sist.IDEmpresa = dir_emp_sist.idref AND dir_emp_sist.Defecto = 1 LEFT OUTER JOIN
direcciones AS dir_fis_sist ON fis_sist.IDDireccion = dir_fis_sist.RecID ON dir_cont.idref = cont.IDContacto ON telcont.IDDireccion = dir_cont.RecID AND
telcont.Tipo = 3 AND telcont.Defecto = 1 ON faxcont.IDDireccion = dir_cont.RecID AND faxcont.Tipo = 2 AND faxcont.Defecto = 1 LEFT OUTER JOIN
direccionescorreo AS mail_cont ON cont.IDContacto = mail_cont.IDref AND mail_cont.Defecto = 1 LEFT OUTER JOIN
direcciones AS dir_resp ON responsable.IDContacto = dir_resp.idref AND dir_resp.Defecto = 1 LEFT OUTER JOIN
telefonos AS telresp ON responsable.IDContacto = telresp.IDref2 AND telresp.Tipo = 3 AND telresp.Defecto = 1 LEFT OUTER JOIN
comercial AS com ON req.RecID = com.idref LEFT OUTER JOIN
comercialtransportes AS com_t ON com.RecID = com_t.idref LEFT OUTER JOIN
empresas AS emp_t ON com_t.IDEmpresa = emp_t.IDEmpresa LEFT OUTER JOIN
fiscal AS fis_t ON emp_t.IDEmpresa = fis_t.IDref AND fis_t.Defecto = 1 LEFT OUTER JOIN
adicional AS adi ON req.RecID = adi.IDRef LEFT OUTER JOIN
telefonos AS tel ON dir_emp_sist.RecID = tel.IDDireccion AND tel.Tipo = 5 AND tel.Defecto = 1 LEFT OUTER JOIN
telefonos AS fax ON dir_emp_sist.RecID = fax.IDDireccion AND fax.Tipo = 7 AND fax.Defecto = 1 LEFT OUTER JOIN
direccionescorreo AS mail_emp ON emp_sist.IDEmpresa = mail_emp.IDref AND mail_emp.Defecto = 1 LEFT OUTER JOIN
(SELECT IDRequerimiento
FROM pedidos AS ped
GROUP BY IDRequerimiento) AS ped_1 ON req.RecID = ped_1.IDRequerimiento LEFT OUTER JOIN
(SELECT RecID
FROM pedidos AS ped2
GROUP BY RecID) AS ped2_1 ON req.IDPedido = ped2_1.RecID LEFT OUTER JOIN
requerimientositemsclientes AS req_dp_cli ON req_dp.RecID = req_dp_cli.IDReqProd LEFT OUTER JOIN
pedidositems AS ped_dp ON req_dp_cli.IDPedProd = ped_dp.RecID LEFT OUTER JOIN
pedidos AS ped3 ON ped_dp.IDPedido = ped3.RecID
WHERE (req.RecID = '{?ID}')
ORDER BY req_dp.NroFila
编辑
附加下面的格式化代码,使用http://sqlformat.appspot.com：
SELECT req.RecID,req_dp.NroItem)
WHEN 0 THEN '0'
WHEN 2 THEN '1'
END AS Item,req_dp.NroItem)
WHEN 0 THEN req_dp.NroItem
WHEN 2 THEN LEFT(req_dp.NroItem,1)
END AS Item2,CASE
WHEN req_dp.recid IS NULL THEN 0
WHEN req_dp.Estado = 9 THEN 0
ELSE (CASE mon.Numero
WHEN 1 THEN req_dp.ImporteUnitario1
WHEN 2 THEN req_dp.ImporteUnitario2
WHEN 3 THEN req_dp.ImporteUnitario3
WHEN 4 THEN req_dp.ImporteUnitario4
WHEN 5 THEN req_dp.ImporteUnitario5
WHEN 6 THEN req_dp.ImporteUnitario6
END) * ((100 - req_dp.Descuento) / 100) * ((100 + req_dp.SobrePrecio) / 100)
END AS PrecIoU,(CASE mon.Numero
WHEN 1 THEN req_dp.importeprecio1
WHEN 2 THEN req_dp.importeprecio2
WHEN 3 THEN req_dp.importeprecio3
WHEN 4 THEN req_dp.importeprecio4
WHEN 5 THEN req_dp.importeprecio5
WHEN 6 THEN req_dp.importeprecio6
END) AS Importe,CASE
WHEN req_dp.Descuento = 0 THEN 0
ELSE ((CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4 THEN req_dp.importeprecio4 WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) - ((req_dp.Descuento * (CASE mon.Numero WHEN 1 THEN req_dp.importeprecio1 WHEN 2 THEN req_dp.importeprecio2 WHEN 3 THEN req_dp.importeprecio3 WHEN 4 THEN req_dp.importeprecio4 WHEN 5 THEN req_dp.importeprecio5 WHEN 6 THEN req_dp.importeprecio6 END) / 100))) - (CASE mon.Numero
WHEN 1 THEN req_dp.importeprecio1
WHEN 2 THEN req_dp.importeprecio2
WHEN 3 THEN req_dp.importeprecio3
WHEN 4 THEN req_dp.importeprecio4
WHEN 5 THEN req_dp.importeprecio5
WHEN 6 THEN req_dp.importeprecio6
END)
END AS ImpDescXProd,CASE req.MostrarImpuestos
WHEN 1 THEN 'Los valores están expresados en ' + mon.Moneda + '. Incluyen IVA.'
WHEN 0 THEN 'Los valores están expresados en ' + mon.Moneda + '. No incluyen IVA.'
END AS LeyendaIVA,direcciones.RecID AS Dir_Cont_Recid
FROM telefonos AS faxcont
RIGHT OUTER JOIN telefonos AS telcont
RIGHT OUTER JOIN direcciones AS dir_cont
RIGHT OUTER JOIN fiscal AS fis_sist
INNER JOIN requerimientos AS req
INNER JOIN requerimientositems AS req_dp ON req.RecID = req_dp.IDRequerimiento
INNER JOIN moneda AS mon ON req.NroMoneda = mon.Numero
INNER JOIN contactos AS cont ON req.IDRef = cont.IDContacto
INNER JOIN empresas AS emp ON cont.IDEmpresa = emp.IDEmpresa ON fis_sist.RecID = req.IDFiscalSistema
INNER JOIN empresas AS emp_sist ON fis_sist.IDref = emp_sist.IDEmpresa
LEFT OUTER JOIN direcciones ON emp.IDEmpresa = direcciones.idref
LEFT OUTER JOIN contactos AS responsable
INNER JOIN usuarios AS usu ON responsable.IDContacto = usu.IDContacto ON req.IDUsuario = usu.RecID
LEFT OUTER JOIN direccionescorreo AS mail_resp ON responsable.IDContacto = mail_resp.IDref
AND mail_resp.Defecto = 1
LEFT OUTER JOIN direcciones AS dir_emp_sist ON emp_sist.IDEmpresa = dir_emp_sist.idref
AND dir_emp_sist.Defecto = 1
LEFT OUTER JOIN direcciones AS dir_fis_sist ON fis_sist.IDDireccion = dir_fis_sist.RecID ON dir_cont.idref = cont.IDContacto ON telcont.IDDireccion = dir_cont.RecID
AND telcont.Tipo = 3
AND telcont.Defecto = 1 ON faxcont.IDDireccion = dir_cont.RecID
AND faxcont.Tipo = 2
AND faxcont.Defecto = 1
LEFT OUTER JOIN direccionescorreo AS mail_cont ON cont.IDContacto = mail_cont.IDref
AND mail_cont.Defecto = 1
LEFT OUTER JOIN direcciones AS dir_resp ON responsable.IDContacto = dir_resp.idref
AND dir_resp.Defecto = 1
LEFT OUTER JOIN telefonos AS telresp ON responsable.IDContacto = telresp.IDref2
AND telresp.Tipo = 3
AND telresp.Defecto = 1
LEFT OUTER JOIN comercial AS com ON req.RecID = com.idref
LEFT OUTER JOIN comercialtransportes AS com_t ON com.RecID = com_t.idref
LEFT OUTER JOIN empresas AS emp_t ON com_t.IDEmpresa = emp_t.IDEmpresa
LEFT OUTER JOIN fiscal AS fis_t ON emp_t.IDEmpresa = fis_t.IDref
AND fis_t.Defecto = 1
LEFT OUTER JOIN adicional AS adi ON req.RecID = adi.IDRef
LEFT OUTER JOIN telefonos AS tel ON dir_emp_sist.RecID = tel.IDDireccion
AND tel.Tipo = 5
AND tel.Defecto = 1
LEFT OUTER JOIN telefonos AS fax ON dir_emp_sist.RecID = fax.IDDireccion
AND fax.Tipo = 7
AND fax.Defecto = 1
LEFT OUTER JOIN direccionescorreo AS mail_emp ON emp_sist.IDEmpresa = mail_emp.IDref
AND mail_emp.Defecto = 1
LEFT OUTER JOIN
(SELECT IDRequerimiento
FROM pedidos AS ped
GROUP BY IDRequerimiento) AS ped_1 ON req.RecID = ped_1.IDRequerimiento
LEFT OUTER JOIN
(SELECT RecID
FROM pedidos AS ped2
GROUP BY RecID) AS ped2_1 ON req.IDPedido = ped2_1.RecID
LEFT OUTER JOIN requerimientositemsclientes AS req_dp_cli ON req_dp.RecID = req_dp_cli.IDReqProd
LEFT OUTER JOIN pedidositems AS ped_dp ON req_dp_cli.IDPedProd = ped_dp.RecID
LEFT OUTER JOIN pedidos AS ped3 ON ped_dp.IDPedido = ped3.RecID
WHERE (req.RecID = '{?ID}')
ORDER BY req_dp.NroFila
解决方法
从前两个表开始并将它们连接在一起 –
requerimientos AS req INNER JOIN requerimientositems AS req_dp ON req.RecID = req_dp.IDRequerimiento'
如果可行的话(写一个涉及两个表的查询[即测试]并确保它没问题)添加下一个 –
requerimientos AS req INNER JOIN requerimientositems AS req_dp ON req.RecID = req_dp.IDRequerimiento INNER JOIN moneda AS mon ON req.NroMoneda = mon.Numero INNER JOIN contactos AS cont ON req.IDRef = cont.IDContacto
并重复.
这可能需要一些努力,但它是一种敏捷的方法,将极大地帮助您前进.
‘特别是那种意大利面;)
如果这样做和/或你想尝试更大的跳跃,试着删除那些子选择,看看是否是问题：
去掉：
LEFT OUTER JOIN (SELECT IDRequerimiento FROM pedidos AS ped GROUP BY IDRequerimiento) AS ped_1 ON req.RecID = ped_1.IDRequerimiento LEFT OUTER JOIN (SELECT RecID FROM pedidos AS ped2 GROUP BY RecID) AS ped2_1 ON req.IDPedido = ped2_1.RecID
AND telcont.Tipo = 3 AND telcont.Defecto = 1 ON faxcont.IDDireccion = dir_cont.RecID AND faxcont.Tipo = 2
那个ON没有表格？
