左外连接匹配
左外连接匹配
提问于 2017-02-01 01:45:14
我有下面的查询，我修改了它，以便能够在一个名为Futuredial_Validation的新表中查找，我写了所有的条件和连接，但它仍然不在表中查找，只是在一个名为cellscripter的表中，其余的查询都可以工作，我真的很感谢大家的帮助，谢谢：
SELECT row_number() OVER (ORDER BY wms_us.wms_us.rrno.rrdate, wms_us.wms_us.rrno.pono, wms_us.wms_us.transferboxdet.meidhex, att.Date_cleared) AS ROW_ID,
wms_us.wms_us.rrno.pono AS ATT_PO,
wms_us.wms_us.rrno.rrdate AS Received_Date,
CASE
WHEN (wms_us.wms_us.model.modeldesc = 'MIXED')
THEN wms_us.wms_us.transferboxdet.basemodel
ELSE wms_us.wms_us.model.modelbase
END AS Model,
CASE
WHEN LEN(wms_us.wms_us.transferboxdet.meidhex) >= 15
THEN LEFT(wms_us.wms_us.transferboxdet.meidhex, 14)
ELSE wms_us.wms_us.transferboxdet.meidhex
END AS IMEI,
/*Check in Futerdial else obtain the values from cellscripter*/
CASE
WHEN (Future.[Error Code] = '1')
THEN Future.LocalTime
ELSE att.Date_cleared
END AS Date_cleared,
CASE
WHEN (Future.[Error Code] = '1')
THEN 'PASS'
ELSE att.Result
END AS Result
FROM wms_us.wms_us.transferboxdoc
INNER JOIN wms_us.wms_us.transferboxdet
ON wms_us.wms_us.transferboxdoc.transferboxdocid = wms_us.wms_us.transferboxdet.transferboxdocid
INNER JOIN wms_us.wms_us.rrno
ON wms_us.wms_us.transferboxdet.rrnoid = wms_us.wms_us.rrno.rrnoid
INNER JOIN wms_us.wms_us.model
ON wms_us.wms_us.transferboxdoc.modelid = wms_us.wms_us.model.modelid
LEFT OUTER JOIN Futerdial.dbo.Futuredial_Validation AS Future
ON Future.IMEI = LEFT(wms_us.wms_us.transferboxdet.meidhex, 14)
LEFT OUTER JOIN DRSCSQLQADB01.att_view2.dbo.attview2 AS att
ON att.IMEI = LEFT(wms_us.wms_us.transferboxdet.meidhex, 14)
WHERE (wms_us.wms_us.rrno.rrdate > '2016-12-01')回答 1
Stack Overflow用户
发布于 2017-02-01 03:36:32
最有可能的情况是:您的连接规范不起作用，或者[Error Code]的case表达式不正确。
使用以下命令检查是否获得[Future_imei]和[Error Code]的任何值：
select
row_id = row_number() over (
order by rrno.rrdate
, rrno.pono
, tbdet.meidhex
, att.Date_cleared)
, att_po = rrno.pono
, Received_Date = rrno.rrdate
, Model=case
when (m.modeldesc = 'mixed')
then tbdet.basemodel
else m.modelbase
end
, imei=case
when len(tbdet.meidhex) >= 15
then left(tbdet.meidhex, 14)
else tbdet.meidhex
end
, [Future_imei] = Future.imei
, Future.[Error Code]
, Date_cleared=case
when (Future.[Error Code] = '1')
then Future.LocalTime
else att.Date_cleared
end
, [Result]=case
when (Future.[Error Code] = '1')
then 'pass'
else att.Result
end
from wms_us.wms_us.transferboxdoc as tbdoc
inner join wms_us.wms_us.transferboxdet as tbdet
on tbdoc.transferboxdocid = tbdet.transferboxdocid
inner join wms_us.wms_us.rrno as rrno
on tbdet.rrnoid = rrno.rrnoid
inner join wms_us.wms_us.model as m
on tbdoc.modelid = m.modelid
left outer join Futerdial.dbo.Futuredial_Validation as Future
on Future.imei = left(tbdet.meidhex, 14)
left outer join drscsqlqadb01.att_view2.dbo.attview2 as att
on att.imei = left(tbdet.meidhex, 14)
where (rrno.rrdate > '2016-12-01')页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41964126
复制相关文章
相似问题
腾讯云开发者
