时间戳

首页 > 解决方案 > Sql 查询令人费解

sql - Sql 查询令人费解

问题描述

我的 SQL 查询错误说明：

Msg 537, Level 16, State 2, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function.

BELOW 是不起作用的查询，但是一旦我在内部查询（OQry）上将“Select”替换为“Select top 100”，它就会起作用（这在代码的注释中标记）。该错误看起来不合适。有任何想法吗？：

Select distinct OQry.Create_tstamp, OQry.LDC_Account_Num, OQry.Territory_code, Replace(LEFT(OQry.Customer_Name,CHARINDEX(' ',OQry.Customer_Name)-1),'-','') as LastName, 
trim(Replace(Right(OQry.Customer_Name, Len(OQry.Customer_Name) - Len(Replace(LEFT(OQry.Customer_Name,CHARINDEX(' ',OQry.Customer_Name)-1),'-','') )),'-','')) as FirstName,
OQry.Commodity,OQry.confirmed_start_date,OQry.confirmed_drop_date,OQry.Market_status_desc,OQry.Reason_code,OQry.Flow_direction_Desc,OQry.AGENT_CODE,Da.AGENT_CODE as [Sub Agent]
from
(
select --This doesn't work but If I replace this with "Select top 100" then it works....  WHAT?!
d.Create_tstamp, d.customer_tkn,
right('00000000'+d.customer_tkn,8) + '-' + right('00'+d.customer_acct_tkn,2) + '-' +  right('0000'+d.account_pkg_tkn,4) as [Account Number], a.AGENT_CODE,
dl.LDC_Account_Num, d.Territory_code, Customer_Name, da.account_pkg_desc as Commodity, da.confirmed_start_date, da.confirmed_drop_date, d.Reason_code, d.Flow_direction_Desc,
d.Market_status_desc
from 
datamart_market_detail d
left outer join datamart_ldc dl on dl.Customer_Tkn = d.Customer_tkn 
and dl.Customer_Acct_Tkn = d.Customer_Acct_Tkn and dl.Account_Pkg_Tkn = d.Account_Pkg_Tkn
left join datamart_accounts da on d.Customer_tkn = da.customer_tkn and d.Customer_Acct_Tkn = da.customer_acct_tkn and d.Account_Pkg_Tkn = da.account_pkg_tkn
left outer join datamart_agent a on a.CUSTOMER_TKN = da.customer_tkn 
where Market_status_desc like '%Drop%'
) OQry
left join
datamart_agent Da
on OQry.customer_tkn = Da.customer_tkn and OQry.[Account Number] = (right('00000000'+da.customer_tkn,8) + '-' + right('00'+da.customer_acct_tkn,2) + '-' +  right('0000'+da.account_pkg_tkn,4))

标签： sqlsql-servertsqlselect

解决方案

您有一个数据问题，源于Falsehoods Programmers Believe About Names。

在你的数据集中的某个地方，有一个值OQry.Customer_Name不是你期望的那样结构化，以便它成为CHARINDEX函数调用的基础。

仔细查看该列中的数据。

SELECT 
  *
FROM (...)  AS OQry
WHERE
  CHARINDEX(' ', OQry.Customer_Name) - 1 <= 0

推荐阅读