Ambiguous reference in query

Greetings!

I have this error:

Illuminate/Database/QueryException with message 'SQLSTATE[HY000]: General error: 0 [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPEN EDGE]Column reference "persona_numero" is ambiguous. (13852) (SQLPrepare[0] at ext/pdo_odbc/odbc_driver.c:204) (SQL: select count(*) as aggregate from "PUB"."tl06_orden_trabajo_cabecera" inner join "PUB"."tg04_personas" on "persona_numero" = "persona_numero_control" left join "PUB"."tg04_personas" on "persona_numero" = "persona_numero_aseguradora" where "codigo_empresa" = 2 and "codigo_taller" = VTAHI and "fecha_recepcion" = 2018-02-20)'

And this is the query:

SuperDealer::raw('SELECT a.codigo_empresa, a.codigo_sucursal, a.codigo_taller, a.secuencia_orden, a.persona_numero_control, a.numero_orden ASOT_ID_ORDEN, a.anio_modelo AS AN_ID_ANTIGUEDAD, a.codigo_marca AS MA_ID_MARCA, a.codigo_modelo AS MO_ID_MODELO,c.persona_cedula AS AS_ID_ASEGURADORA,c.persona_nombre AS AS_NOMBRE_ASEGURADORA, a.numero_orden AS OT_NUMERO_OT, a.chasis AS OT_VIN, a.placa AS OT_PLACA, a.kilometraje AS OT_KM, a.numero_motor AS OT_NUMERO_MOTOR, a.anio_modelo AS OT_ANIO_FABRICACION,a.reparaciones_solicitadas AS OT_DESCRIPCION_TRABAJO, a.observacion AS OT_OBSERVACIONES, a.fecha_recepcion AS OT_FECHA_INGRESO, a.fecha_ofrece AS OT_FECHA_PROMETIDA, a.fecha_liquidacion AS FECHA_ENTREGA, a.estado AS ID_ESTADO, a.fecha_anulacion AS OT_FECHA_ANULACION, b.persona_cedula AS ID_ASESOR_SERVICIO, b.persona_nombre AS PRIMER_NOMBRE_ASESOR_SERVICIO, b.persona_nombre AS AUXILIAR FROM PUB."tl06_orden_trabajo_cabecera"')
->join('PUB.tg04_personas', 'persona_numero', '=', 'persona_numero_control')
->leftJoin('PUB.tg04_personas','persona_numero','=','persona_numero_aseguradora')
->where('codigo_empresa', 2)->where('codigo_taller', 'VTAHI')
->whereBetween('fecha_recepcion', [$date1, $date2])->get();

I know that persona_numero was tipped twice, but I don't know how to turn the following query to Eloquent query:

SELECT a.codigo_empresa, a.codigo_sucursal, a.codigo_taller, a.secuencia_orden, a.persona_numero_control, a.numero_orden AS OT_ID_ORDEN, a.anio_modelo AS AN_ID_ANTIGUEDAD, a.codigo_marca AS MA_ID_MARCA, a.codigo_modelo AS MO_ID_MODELO,c.persona_cedula AS AS_ID_ASEGURADORA,c.persona_nombre AS AS_NOMBRE_ASEGURADORA,a.numero_orden AS OT_NUMERO_OT, a.chasis AS OT_VIN, a.placa AS OT_PLACA, a.kilometraje AS OT_KM, a.numero_motor AS OT_NUMERO_MOTOR, a.anio_modelo AS OT_ANIO_FABRICACION,a.reparaciones_solicitadas AS OT_DESCRIPCION_TRABAJO, a.observacion AS OT_OBSERVACIONES, a.fecha_recepcion AS OT_FECHA_INGRESO, a.fecha_ofrece AS OT_FECHA_PROMETIDA, a.fecha_liquidacion AS FECHA_ENTREGA, a.estado AS ID_ESTADO, a.fecha_anulacion AS OT_FECHA_ANULACION,b.persona_cedula AS ID_ASESOR_SERVICIO, b.persona_nombre AS PRIMER_NOMBRE_ASESOR_SERVICIO, b.persona_nombre AS AUXILIAR FROM PUB."tl06_orden_trabajo_cabecera" a INNER JOIN PUB."tg04_personas" b ON b.persona_numero = a.persona_numero_control LEFT JOIN PUB."tg04_personas" c ON c.persona_numero = a.persona_numero_aseguradora WHERE a.codigo_empresa = '2' AND a.codigo_taller = 'VTAHI' AND a.numero_orden = '13514';

Please help! :(

vladv

6 years ago

Level 12

hi,

If you have 2 tables with the same field name you need to specify in the queries the table name or you will end up with an ambiguous error.

So for your case, I suppose you have a table "PUB.tg04_personas" and an alias table "a" both with the field "persona_numero_control" then you could try:

// ->join('PUB.tg04_personas', 'PUB.tg04_personas.persona_numero', '=', 'a.persona_numero_control')

SuperDealer::raw('SELECT a.codigo_empresa, a.codigo_sucursal, a.codigo_taller, a.secuencia_orden, a.persona_numero_control, a.numero_orden ASOT_ID_ORDEN, a.anio_modelo AS AN_ID_ANTIGUEDAD, a.codigo_marca AS MA_ID_MARCA, a.codigo_modelo AS MO_ID_MODELO,c.persona_cedula AS AS_ID_ASEGURADORA,c.persona_nombre AS AS_NOMBRE_ASEGURADORA, a.numero_orden AS OT_NUMERO_OT, a.chasis AS OT_VIN, a.placa AS OT_PLACA, a.kilometraje AS OT_KM, a.numero_motor AS OT_NUMERO_MOTOR, a.anio_modelo AS OT_ANIO_FABRICACION,a.reparaciones_solicitadas AS OT_DESCRIPCION_TRABAJO, a.observacion AS OT_OBSERVACIONES, a.fecha_recepcion AS OT_FECHA_INGRESO, a.fecha_ofrece AS OT_FECHA_PROMETIDA, a.fecha_liquidacion AS FECHA_ENTREGA, a.estado AS ID_ESTADO, a.fecha_anulacion AS OT_FECHA_ANULACION, b.persona_cedula AS ID_ASESOR_SERVICIO, b.persona_nombre AS PRIMER_NOMBRE_ASESOR_SERVICIO, b.persona_nombre AS AUXILIAR FROM PUB."tl06_orden_trabajo_cabecera"')
->join('PUB.tg04_personas', 'persona_numero', '=', 'a.persona_numero_control')
->leftJoin('PUB.tg04_personas','persona_numero','=','persona_numero_aseguradora')
->where('codigo_empresa', 2)->where('codigo_taller', 'VTAHI')
->whereBetween('fecha_recepcion', [$date1, $date2])->get();

MarcosKlender

6 years ago

Level 1

I tried that and now it gives me the following error:

Column "a.persona_numero_control" cannot be found or is not specified for query.

I don't know how to write this as Eloquent query:

INNER JOIN PUB."tg04_personas" b
ON b.persona_numero = a.persona_numero_control
LEFT JOIN PUB."tg04_personas" c
ON c.persona_numero = a.persona_numero_aseguradora

so that it works when I call in the SELECT:

c.persona_nombre AS AS_NOMBRE_ASEGURADORA, 
b.persona_nombre AS PRIMER_NOMBRE_ASESOR_SERVICIO

Please or to participate in this conversation.