He escrito la consulta a continuación para recopilar datos relacionados con los ingresos de los conductores de camiones. Tanto la consulta principal como la consulta combinada funcionan correctamente. Sin embargo, cuando los combino, aparece un error
Msg 4104, nivel 16, estado 1, línea 37
No se pudo vincular el identificador de varias partes "tn.ctripnumber"
relativo a tRev.ctripnumber = tn.ctripnumber. Intenté usar el nombre de la tabla de ingresos y el alias tRev.
select
tn.ctripnumber as "Load Number",
tr.cresourcedesc as "Carrier/Driver",
tr.resourcetype as "Resource Type",
tn.cfirmorigin as "Pickup Origin",
tn.corigaddress as "Origin Address",
tn.corigcity as "Origin City",
tn.corigstate as "Origin State",
tn.corigzip as "Origin Zip",
tn.dorigappt_start as "Scheduled Arrival Date",
tn.dorigappt_end as "Scheduled Late Arrival Date",
tn.dtripstartdate as "Actual Arrival Date",
datediff(minute,tn.dorigappt_start,tn.dtripstartdate) as "Arrival Diff",
(case when tn.dtripstartdate-tn.dorigappt_start < 0 then 'Early' when tn.dorigappt_start-tn.dtripstartdate = 0 then 'On-time' else 'Late' end) as Arrival_Rank,
tn.cfirmdestination as "Delivery Destination",
tn.cdestaddress as "Destination Address",
tn.cdestcity as "Destination City",
tn.cdeststate as "Destination State",
tn.cdestzip as "Destination Zip",
tn.ddestappt_start as "Scheduled Delivery Date",
tn.ddestappt_end as "Scheduled Late Delivery Date",
tn.ddeliverydate as "Actual Delivery Date",
datediff(minute,tn.ddestappt_start,tn.ddeliverydate) as "Delivery Diff",
(case when tn.ddeliverydate-tn.ddestappt_start < 0 then 'Early' when tn.ddeliverydate-tn.ddestappt_start = 0 then 'On-time' else 'Late' end) as "Delivery Rank",
tn.nideadheadmiles as "Deadhead Miles",
tn.niloadedmiles as "Loaded Miles",
tn.nideadheadmiles + tn.niloadedmiles as "Total Miles"
from
tripnumber tn,tripresources tr
inner join
(select
r.ctripnumber, sum(Revenue_Subtotal) as "Revenue"
from
(select
r.ctripnumber, r.cmethod, sum(r.curevenue) as Revenue_Subtotal
from
revenuedtl r
where
r.cmethod in (select distinct r.cmethod from revenuedtl r)
group by
r.ctripnumber, r.cmethod) r
--where r.cmethod like 'BROK%'
group by
r.ctripnumber) tRev on tRev.ctripnumber = tn.ctripnumber
where
tn.ctripnumber = tr.ctripnumber
--and tn.ctripnumber = '324412'
and tr.resourcetype in ('D','M')
and tn.dtripstartdate >= '2018-12-01 00:00:00.000'
and tn.dtripstartdate < '2019-01-01 00:00:00.000'
Esta es tu fromcláusula:
from tripnumber tn,
tripresources tr join
(select r.ctripnumber, sum(Revenue_Subtotal) as "Revenue"
from (select r.ctripnumber, r.cmethod, sum(r.curevenue) as Revenue_Subtotal
from revenuedtl r
where r.cmethod in (select distinct r.cmethod from revenuedtl r)
group by r.ctripnumber, r.cmethod
) r
--where r.cmethod like 'BROK%'
group by r.ctripnumber
) tRev
on tRev.ctripnumber = tn.ctripnumber
Regla simple: nunca use comas en la FROMcláusula. Utilice siempre una sintaxis estándar , explícita y adecuada JOIN.
Entonces, reescribe este uso de manera adecuada JOIN:
from tripnumber tn join
tripresources tr
on tn.ctripnumber = tr.ctripnumber join
(select r.ctripnumber, sum(Revenue_Subtotal) as "Revenue"
from (select r.ctripnumber, r.cmethod, sum(r.curevenue) as Revenue_Subtotal
from revenuedtl r
where r.cmethod in (select distinct r.cmethod from revenuedtl r)
group by r.ctripnumber, r.cmethod
) r
--where r.cmethod like 'BROK%'
group by r.ctripnumber
) tRev
on tRev.ctripnumber = tn.ctripnumber
Y tu error desaparecerá.
La razón técnica por la que su consulta falla es que las reglas de alcance ,son diferentes de CROSS JOIN. De hecho, podría reemplazar la coma por CROSS JOINpara solucionar el problema. Pero la solución correcta es expresar sus combinaciones correctamente.
Este artículo se recopila de Internet, indique la fuente cuando se vuelva a imprimir.
En caso de infracción, por favor [email protected] Eliminar
Déjame decir algunas palabras