July 18, 2014 at 10:10 am
I receive error message "Msg 4147, Level 15, State 1, Line 64" on my script when I run it. It tells me to use LEFT or RIGHT OUTER JOINS. Here is the script. Can someone please give me some guidance.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Trigger dbo.TG_Asgnmnt Script Date: 9/19/00 11:03:53 AM ******/
CREATE TRIGGER TG_Asgnmnt ON dbo.Asgnmnt FOR INSERT,UPDATE AS
/*
This trigger is designed to automatically select in round robin fashion
based on the groupname the next available assignee from that group.
This will only happen if the current assignee begins with '$Auto'
It is also designed to delete the row created by the TG_CallLog trigger if it
encounters an Asgnmnt record inserted by the Heat application containing a heatseq which is
only one number away from the one the CallLog created.
8/1/00. Modified update for PIN to test for heatseq and CallId
because HeatSeq is always 1 for AutoTickets.
9/17/01. Modified size of heatseq conversion to accomodate 10-digits.
*/
DECLARE @newassignee varchar(32),
@groupname varchar(20),
@heatseq int,
@HeatSeqBefore int,
@AutoHeatSeq int,
@email varchar(35),
@phone varchar(14),
@dummy varchar(255),
@Pager varchar(14),
@CallId varchar(8),
@autocount int,
@Assignee varchar(32),
@AssignedBy varchar(8),
@WhoAcknow varchar(8),
@WhoResolv varchar(8),
@UpdateString varchar(255)
/* Convert PINS to IDs. @UpdateString will only contain legitimate
changes to the data (e.g. If an Alpha Id was used instead of
a pin, no update string will be generated
*/
Select @UpdateString=Null,@AssignedBy=AssignedBy, @WhoAcknow=WhoAcknow, @WhoResolv=WhoResolv, @CallId=CallId, @heatseq = heatseq
From Inserted
If Update(AssignedBy)
Execute ConvertPin @AssignedBy, 'AssignedBy', @UpdateString Output
If Update(WhoAcknow)
Execute ConvertPin @WhoAcknow, 'WhoAcknow', @UpdateString Output
If Update(WhoResolv)
Execute ConvertPin @WhoResolv, 'WhoResolv', @UpdateString Output
If Len(@UpdateString)>0
Begin
Select @UpdateString = 'Update Asgnmnt Set ' + @UpdateString +
' WHERE heatseq=' + Convert(varchar(16),@heatseq) + ' AND CallID = ''' + @CallId + ''''
Execute(@UpdateString)
End
IF update(assignee)
BEGIN
SELECT @assignee = assignee,
@groupname = groupname,
@heatseq = heatseq,
@AutoHeatSeq = heatseq - 1,
@CallId = CallId
From Inserted
IF upper(substring(@assignee,1,5)) = '$AUTO'
(line 64) Begin
DECLARE c1 CURSOR for select a.assignee,
sort = isnull(b.last_asgmnt,'0000-00-00.00:00:00') + a.assignee,
email, phone , pager
from assignee a, VW_LAST_ASGNMNT b
where a.assignee *= b.assignee and upper(substring(a.assignee,1,5)) <> '$AUTO'
and substring(a.available,1,1) = 'Y' and groupname = @groupname
order by sort
open c1
fetch next from c1 into @newassignee,@dummy,@email,@phone,@pager
INSERT INTO RoundRobinLog (log)
VALUES (
+'Group:' + @groupname + ', Assignee:' + @assignee
+ ', New Assignee:' + @newassignee
+ ', HeatSeq:' + convert(char(20),@heatseq))
Close c1
DEALLOCATE c1
/* Begin change for Auto Ticket Gen */
IF @heatseq <> 1
UPDATE Asgnmnt SET assignee = @newassignee, phone = @phone, email = @email, pager = @pager
WHERE heatseq = @heatseq
ELSE
UPDATE Asgnmnt SET assignee = @newassignee,
phone = @phone, email = @email, pager = @pager
WHERE heatseq = @heatseq AND @Assignee = Assignee
AND upper(substring(Assignee,1,5)) = '$AUTO'
AND @GroupName = GroupName
/* End change for Auto Ticket Gen */
End
Else
Begin
/*
Is this an Insert or an Update? If it is an Insert then
@HeatSeqBefore will be null
*/
Select @HeatSeqBefore = HeatSeq
From Deleted
If @HeatSeqBefore is null
Begin
select @autocount = Count(1)
from Asgnmnt
where CallId = @CallId and HeatSeq > (@AutoHeatSeq - 5) and HeatSeq < (@AutoHeatSeq +1)
If @autocount > 0
Begin
INSERT INTO RoundRobinLog (log)
VALUES (
+'CallId:' + @CallId
+ ', AutoCount:' + convert(char(20),@AutoCount)
+ ', Target HeatSeq:' + convert(char(20),@AutoHeatSeq))
If (@AutoCount > 0)
Begin
Delete ASGNMNT
Where CallId = @CallId and HeatSeq > (@AutoHeatSeq - 5) and HeatSeq < (@AutoHeatSeq +1)
End
End
End
End
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
July 18, 2014 at 10:26 am
The problem is that you're using old outer join syntax. You just need to change it as it's no longer supported.
SELECT a.assignee,
sort = isnull(b.last_asgmnt, '0000-00-00.00:00:00') + a.assignee,
email,
phone,
pager
FROM assignee a
LEFT JOIN VW_LAST_ASGNMNT b ON a.assignee = b.assignee
WHERE a.assignee LIKE '$AUTO%' --As long as your collation is case insensitive.
AND a.available LIKE 'Y%'
AND groupname = @groupname
ORDER BY sort
Additional to this problem, I feel the need to advice you that this trigger will fail (silently) with multiple rows operations and will create some performance problems. I don't have enough information to indicate a better option and I'm sure this isn't the only problem that you'll have.
EDIT: I changed the code to remove the functions in your WHERE clause to allow them to use indexes if available.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply