D10.天地弦-生活的点点滴滴是珍贵的

服务器: 消息 8624，级别 16，状态 1，行 442
内部 SQL Server 错误。

郁闷了两天的问题终于得到了解决。记下来

前天突然客户反应[客户订单]，用的好好的居然有"内部错误",跟踪有发现是MSSQL的错误。代码如下:

SET NOCOUNT ON

DECLARE
@mm_Key uniqueidentifier

SELECT
@mm_Key='{50A5A02D-0CA7-4864-958A-63205D29E42A}'

DECLARE @DanJuKey uniqueidentifier

SELECT @DanJuKey = @mm_Key

DELETE f_pzjl_d
  FROM mak_GongChengPZJL f_pzjl
  INNER JOIN mak_GongChengPZJL_D f_pzjl_d ON f_pzjl_d.FFormKey = f_pzjl.FFormKey
  INNER JOIN mak_WuLiaoGCPZ f_pz_m ON f_pz_m.FFormKey = f_pzjl.FGongCPZKey
  LEFT JOIN sal_CustOrder_D od_d ON f_pzjl.FFormKey = od_d.FGongChengPZKey
  WHERE f_pzjl.FOwnsFormKey = @DanJuKey  --<1>
        AND (od_d.FKey IS NULL
            OR od_d.FWuLiaoKey <> f_pz_m.FWuLiaoKey) 

试了下发现用<1>的@DanJuKey替换常量或者用@mm_Key就可以，我用@mm_Key替换了@DanJuKey暂时解决了问题。郁闷。

今天客户有反应[采购订单]也有相同的问题，我想这次不能这么忽悠过去了，于是认真查了查。网上也找了很久。找不到，突然我看代码的时候发现

DELETE f_pzjl_d
  FROM mak_GongChengPZJL f_pzjl
  INNER JOIN mak_GongChengPZJL_D f_pzjl_d ON f_pzjl_d.FFormKey = f_pzjl.FFormKey
  INNER JOIN mak_GongChengPZJL_D_SL f_pzjl_d_sl ON f_pzjl_d_sl.FKey = f_pzjl_d.FKey  --这一段多
  INNER JOIN mak_WuLiaoGCPZ f_pz_m ON f_pz_m.FFormKey = f_pzjl.FGongCPZKey
  LEFT JOIN sal_CustOrder_D od_d ON f_pzjl.FFormKey = od_d.FGongChengPZKey
  WHERE f_pzjl.FOwnsFormKey = @mm_Key
        AND (od_d.FKey IS NULL
            OR od_d.FWuLiaoKey <> f_pz_m.FWuLiaoKey)

结论:

  发现只是那一段多而已。于是查了查，发现mak_GongChengPZJL_D和mak_GongChengPZJL_D_SL的主键有级联的关系。一去掉就可以了,总算是找到了问题...可以安心了

开始以为没有进行ParentKey的设置，后来设了ParentKey也一样有问题.

后来无意中将DataSet进行了Post发现OK了

Self.cdsMain.Append;
if s <> '' then
  Self.cdsMain.FieldByName('FParentIndex').AsInteger := StrToInt(s);
Self.cdsMain.FieldByName('FIndex').AsInteger := 0;
Self.cdsMain.FieldByName('FParentCaption').AsString := s1;
self.cdsMain.Post;  //如果没有这句会跳到第一条

declare @StartDate datetime
declare @EndDate datetime
declare @WeekDay INT, @WeekNum INT

set @StartDate = '2009-10-4' 
set @EndDate = '2009-10-25'

set @StartDate = '2009-10-1' 
set @EndDate = '2009-10-2'

set @StartDate = '2009-10-3' 
set @EndDate = '2009-10-4'

SET @WeekDay = 1   --星期天

SELECT @WeekNum=datediff(day
                 ,case
                     when DATEPART(weekday,@StartDate) = @WeekDay then @StartDate - 1
                     else @StartDate
                  end
                 ,case
                      when DATEPART(weekday,@EndDate) = @WeekDay then @EndDate + 1
                      else @EndDate
                  end
             ) / 7

IF DATEPART(weekday,@StartDate)=@WeekDay
  SELECT @WeekNum = @WeekNum + 1
ELSE IF (@EndDate > @StartDate) AND (DATEPART(weekday,@EndDate) = @WeekDay)
  SELECT @WeekNum = @WeekNum + 1

SELECT @WeekNum




-----Delphi

function WeekDayCount(pvDate1, pvDate2: TDateTime; pvWeekDay: Integer): Integer;
var
  lvDate1, lvDate2: TDateTime;
  lvWeekNum: Real;
  lvDay: Integer;
begin
  lvDate1 := Trunc(pvDate1);
  lvDate2 := Trunc(pvDate2);

  if DayOfWeek(pvDate1) = pvWeekDay then lvDate1 := IncDay(lvDate1, -1);
  if DayOfWeek(lvDate2) = pvWeekDay then lvDate2 := IncDay(lvDate2, +1);

  //相差的天数是是多少个星期
  Result := Trunc(WeekSpan(lvDate1, lvDate2));

  //如果最后一天是那天则加1，注意是pvDate2不是lvDate2
  if DayOfWeek(pvDate1) = pvWeekDay then Inc(Result)
  else if pvDate2 > pvDate1 then
    if DayOfWeek(pvDate2) = pvWeekDay then Inc(Result);
end;

type

  TWeekDay = 1..7;

  TWeekDays = set of TWeekDay;

function WeekDayCountEx(pvDate1, pvDate2: TDateTime; pvWeekDays: TWeekDays):
  Integer;
var
  lvDate1, lvDate2, lvTempDate: TDateTime;
begin
  lvDate1 := Trunc(pvDate1);
  lvDate2 := Trunc(pvDate2);
  lvTempDate := lvDate1;
  Result := 0;
  while lvTempDate <= lvDate2 do
  begin
    if DayOfWeek(lvTempDate) in pvWeekDays then Inc(Result);
    lvTempDate := IncDay(lvTempDate, 1);
  end;
end;

select * from bas_Material
  where CHECKSUM(*)
       NOT IN (SELECT CHECKSUM(*) FROM erpdata_zl.dbo.bas_Material)
找出bas_Material不存在在 erpdata_zl.dbo.bas_Material中的记录

--和行转列的应用效果差不多了，统计结果中可能会用到
--测试表
create table Test
(F1 varchar(10),
F2 varchar(10))

go

--插入数据
insert into Test
select 'jack' F1,'book1' F2
union
select 'jack' F1,'book2' F2
union
select 'jack' F1,'book3' F2
union
select 'Mary' F1,'book4' F2
union
select 'Mary' F1,'book5' F2
union
select 'Mike' F1,'book1' F2
union
select 'Mike' F1,'book5' F2
union
select 'Mike' F1,'book7' F2
union
select 'Mike' F1,'book9' F2
--一条动态SQL语句

go
--合并函数
CREATE FUNCTION MergeCharField(@Group varchar(255))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+rtrim(F2) FROM Test WHERE F1=@Group

RETURN(substring(@r,2,8000))
END
GO
--调用
select F1 [name],dbo.MergeCharField(F1) [book] from test group by F1
--删除测试环境
drop table test
drop FUNCTION MergeCharField
/*
name     book
--------------------------
jack       book1,book2,book3
Mary     book4,book5
Mike     book1,book5,book7,book9
*/