April 20, 2010 at 10:01 am
And Congrats to your Bachelor degree, too!!!
BTW Lutz, getting married, not graduating...
April 20, 2010 at 10:09 am
lmu92 (4/17/2010)
When we started with c.u.r.s.o.r. stuff I didn't notice how similar the handling is. Now, seeing the set based code, it becomes more obvious.Therefore, I'd recommend the next step:
If you modify the structure of PermitsInserted to have one row per DataID and 11 columns to hold the related import status and also change #inv_PrevPWDConvData to match the same concept, we would not need to do the aggregation (pivot) for the status update which will speed this step up even more.
Once that is done we should look at proper indexing and you'd be amazed how much we still can squeeze out of this process.
If I read this right, we can go ahead and get rid of both the temp tables that we are using and just use the PermitsInserted table to track everything, then at the end, create the CTE pivoted table based on the values in the permitsinserted table, then do the updates which were in 3 different statements, all the way down to 1...am I following you right?
April 20, 2010 at 10:17 am
gregory.anderson (4/20/2010)
And Congrats to your Bachelor degree, too!!!
BTW Lutz, getting married, not graduating...
Ooops, sorry for the misunderstanding... :blush:
April 20, 2010 at 10:23 am
gregory.anderson (4/20/2010)
Ok, back to the fun...How does this code block work (the MAX part) when it is going to return a non-integer value for the error mesage?
MAX(CASE WHEN Recordindicator =1 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev1,
I'm also still struggling with this first cte so I need to look at it and think about it more...edit (the group by part)
straight from BOL (BokksOnLine)
MAX can be used with numeric, character, and datetime columns, but not with bit columns. Aggregate functions and subqueries are not permitted.
If you replace
THEN ErrorMessage ELSE NULL END
-- with
THEN ErrorMessage ELSE '' END
You should be able to get rid of the aggregation warning you mentioned.
April 20, 2010 at 10:28 am
gregory.anderson (4/20/2010)
...
If I read this right, we can go ahead and get rid of both the temp tables that we are using and just use the PermitsInserted table to track everything, then at the end, create the CTE pivoted table based on the values in the permitsinserted table, then do the updates which were in 3 different statements, all the way down to 1...am I following you right?
Yup. That's exactly what the final solution should look like.
And you'll see, even on your machine there will be some significant improvement...
April 20, 2010 at 1:05 pm
lmu92 (4/20/2010)
gregory.anderson (4/20/2010)
...
If I read this right, we can go ahead and get rid of both the temp tables that we are using and just use the PermitsInserted table to track everything, then at the end, create the CTE pivoted table based on the values in the permitsinserted table, then do the updates which were in 3 different statements, all the way down to 1...am I following you right?
Yup. That's exactly what the final solution should look like.
And you'll see, even on your machine there will be some significant improvement...
Ok, here's what I've done so far.
I changed the structure of the PermitsInserted table to have four columns, ID, RecordIndicator, ErrorID, and ErrorMessage.
The first validity check statement will now insert directly into the PWDPermitConversionErrors table instead of going to the #temp table (you mentioned going to the PermitsInserted table, but I made it so the OUTPUT statement will write to that...see I'm learning).
Then, I changed the "previous" section to make it follow this same process.
But, the processing time has gone up from the 2:02 last time, to a 2:44 this time.
I'm now working on modifying both inserts (for failed and good records) so they are in the same statement like you suggested, we'll see what I can come up with now.
BTW - here's what the code looks like so far:
Use IARTS
Go
/* -- test setup to reset tables
UPDATE PWDPermitConversionData2
SET conversionstatus=0,
conversionstatusprev1 = null,
conversionstatusprev2 = null,
conversionstatusprev3 = null,
conversionstatusprev4 = null,
conversionstatusprev5 = null,
conversionstatusprev6 = null,
conversionstatusprev7 = null,
conversionstatusprev8 = null,
conversionstatusprev9 = null,
conversionstatusprev10 = null
FROM PWDPermitConversionData2
TRUNCATE TABLE PWDPermit2
TRUNCATE TABLE PWDPermitConversionErrors2
*/
DECLARE
@User VARCHAR(8),
@True BIT,
@False BIT,
@PermitType_Org INT,
@PermitType_Ind INT,
@PermitType_Temp INT,
@PermitType_Sticker INT,
@PermitStatus_RU INT,
@PermitDeviceType_Placard INT,
@PermitDeviceType_Sticker INT,
@PermitDeviceType_Unknown INT,
@Today DATETIME,
@MinDate DATETIME,
@MaxDate DATETIME,
@DateMinus1Year DATETIME
SET @User = 'PWDConv'
SET @True = 0x1
SET @False = 0x0
SET @Today = GETDATE()
SET @MinDate = CONVERT(DATETIME, '1901-01-01')
SET @MaxDate = CONVERT(DATETIME, '2100-01-01')
SET @DateMinus1Year = DATEADD(yy, -1, @Today)
SELECT @PermitType_Org = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Organization'
SELECT @PermitType_Ind = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Permanent'
SELECT @PermitType_Temp = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Temporary'
SELECT @PermitType_Sticker = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Sticker'
SELECT @PermitStatus_RU = PWDPermitStatusID From IARTS..PWDPermitStatus Where LookupValue = 'RtrndUnDel'
SELECT @PermitDeviceType_Placard = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Placard'
SELECT @PermitDeviceType_Sticker = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Sticker'
SELECT @PermitDeviceType_Unknown = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Unknown'
--------------------------------------------------------------------------------------------------------
-- Create the table that will be used to track what was actually inserted
--------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PermitsInserted]') AND type in (N'U'))
DROP TABLE [dbo].[PermitsInserted]
Create Table IARTS..PermitsInserted
(
[PWDPermitConversionDataID] Int Not Null,
[RecordIndicator] Int Not Null,
[ErrorID] Int Not Null,
[ErrorMessage] varchar(1000) Null
)
--------------------------------------------------------------------------------------------------------
-- populate intermeditate table
--------------------------------------------------------------------------------------------------------
Insert Into IARTS..PWDPermitConversionErrors2
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
Output
Inserted.PWDPermitConversionDataID,
0,
Inserted.ErrorID,
Inserted.ErrorMessage
Into IARTS..PermitsInserted
SELECT PWDPermitConversionDataID,
0 AS [RecordIndicator],
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' Or [PermitNumber] = ''THEN 5000
WHEN [PermitPrefix] NOT IN ('D','M','V','S')THEN 5001--'','P', 'H') THEN 5001
WHEN ([Status] NOT IN('RP','01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99', NULL) AND [Status] LIKE '%[^0-9]%') THEN 5002
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0)THEN 5003
ELSE 5004
End As [ErrorID],
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' Or [PermitNumber] = ''THEN 'non-numeric [PermitNumber]'
WHEN [PermitPrefix] NOT IN ('D','M','V','S')THEN 'invalid [PermitPrefix]'--,'','P', 'H') THEN 'invalid [PermitPrefix]'
WHEN ([Status] NOT IN('RP','01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99', NULL) AND [Status] LIKE '%[^0-9]%') THEN 'invalid [Status]'
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0)THEN 'invalid [ExpirationDate]'
ELSE 'unknown error'
End As [ErrorMessage]
FROMPWDPermitConversionData2
WHEREConversionStatus = 0
And
(([PermitNumber] LIKE '%[^0-9]%' Or [PermitNumber] = '')--select non-numeric values
OR [PermitPrefix] NOT IN ('D','M','V','S','P', 'H') -- invalid prefix
OR ([Status] NOT IN('RP','01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') AND [Status] LIKE '%[^0-9]%') -- invalid status
OR (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) = 0) -- invalid ExpirationDate
)
--------------------------------------------------------------------------------------------------------
-- Insert the primary record data inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
INSERT INTO PWDPermit2
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate],
[PWDPermitConversionDataID],
[RecordIndicator]
)
OUTPUT
INSERTED.PWDPermitConversionDataID,
INSERTED.RecordIndicator,
1,
Null
INTO IARTS..PermitsInserted
SELECTCASE WHEN PermitNumber = '' THEN NULL
ELSE CAST(PermitNumber AS INT)
END,
CustomerNumber,
CASE PermitPrefix
WHEN 'D' THEN @PermitType_Org
WHEN 'M' THEN @PermitType_Ind
WHEN 'V' THEN @PermitType_Temp
WHEN 'S' THEN @PermitType_Sticker
ELSE PermitPrefix -- This will make it fail for sure
END,
CASE [Status]
WHEN 'RP' THEN @PermitStatus_RU
ELSE CAST([Status] AS INT)
END,
CASE WHEN PermitPrefix = 'S' THEN @PermitDeviceType_Sticker
WHEN PermitPrefix IN ('D', 'M', 'V') THEN @PermitDeviceType_Placard
ELSE @PermitDeviceType_Unknown
END,
CASE WHEN Location = '' OR Location IS NULL THEN '999'
ELSE Location
END,
CASE WHEN HCExam = '' OR HCExam IS NULL THEN '999'
ELSE HCExam
END,
CASE WHEN HCStation = '' OR HCStation IS NULL THEN '99'
ELSE HCStation
END,
CAST(IssueDate AS DATETIME),
CASE WHEN ExpirationDate = 'NONE' OR ExpirationDate = '' OR ExpirationDate = 'NOE' THEN @MaxDate
ELSE CAST(ExpirationDate AS DATETIME)
END,
CASE WHEN InvalidDLNumber = 'Y' THEN @True
ELSE @False
END,
@False,
@User,
@Today,
PWDPermitConversionDataID,
0
FROM PWDPermitConversionData2
WHERENOT EXISTS
(
Select1
From IARTS..PWDPermitConversionErrors2 pce
Where pce.PWDPermitConversionDataID = PWDPermitConversionData2.PWDPermitConversionDataID
Andpce.RecordIndicator = 0
)
AND CustomerNumber > 0 -- which also cover NOT NULL
AND ConversionStatus = 0
AND PermitPrefix NOT IN ('P', 'H')
AND [Status] <> '06'
AND
(
(
(
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END IS NULL
OR
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END > @DateMinus1Year
)
AND PermitPrefix <> 'M'
)
OR
(
PermitPrefix = 'M'
)
)
End Try
Begin Catch
Print ''
Print 'Insert failed'
Print '----------------------------------------'
Print Cast(Error_Message() as varchar(max))
End Catch
--------------------------------------------------------------------------------------------------------
-- Insert the previous permits
--------------------------------------------------------------------------------------------------------
Insert Into IARTS..PWDPermitConversionErrors2
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
Output
Inserted.PWDPermitConversionDataID,
Inserted.RecordIndicator,
Inserted.ErrorID,
Inserted.ErrorMessage
Into IARTS..PermitsInserted
SelectPWDPermitConversionDataID,
RecordIndicator,
Case
When PrevPermNum Like '%[^0-9]%' Or PrevPermNum = ''Then 5000
When PrevPermPref Not In ('M', 'V', 'S', '', Null)Then 5001
When (PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')Then 5002
When (PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)Then 5003
Else 5004
EndAs [ErrorID],
Case
When PrevPermNum Like '%[^0-9]%' Or PrevPermNum = ''Then 'non-numeric [PermitNumber]'
When PrevPermPref Not In ('M', 'V', 'S', '', Null)Then 'invalid [PermitPrefix]'
When (PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')Then 'invalid [Status]'
When (PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)Then 'invalid [ExpirationDate]'
Else 'unknown error'
EndAs [ErrorMessage]
From
(
SelectPWDPermitConversionDataID,
CustomerNumber,
n As [RecordIndicator],
Casen
When 1Then PrevPermPref1
When 2Then PrevPermPref2
When 3Then PrevPermPref3
When 4Then PrevPermPref4
When 5Then PrevPermPref5
When 6Then PrevPermPref6
When 7Then PrevPermPref7
When 8Then PrevPermPref8
When 9Then PrevPermPref9
When 10Then PrevPermPref10
EndAs [PrevPermPref],
Casen
When 1Then PrevPermNum1
When 2Then PrevPermNum2
When 3Then PrevPermNum3
When 4Then PrevPermNum4
When 5Then PrevPermNum5
When 6Then PrevPermNum6
When 7Then PrevPermNum7
When 8Then PrevPermNum8
When 9Then PrevPermNum9
When 10Then PrevPermNum10
EndAs [PrevPermNum],
Casen
When 1Then PrevPermStatus1
When 2Then PrevPermStatus2
When 3Then PrevPermStatus3
When 4Then PrevPermStatus4
When 5Then PrevPermStatus5
When 6Then PrevPermStatus6
When 7Then PrevPermStatus7
When 8Then PrevPermStatus8
When 9Then PrevPermStatus9
When 10Then PrevPermStatus10
EndAs [PrevPermStatus],
Casen
When 1Then PrevPermReturn1
When 2Then PrevPermReturn2
When 3Then PrevPermReturn3
When 4Then PrevPermReturn4
When 5Then PrevPermReturn5
When 6Then PrevPermReturn6
When 7Then PrevPermReturn7
When 8Then PrevPermReturn8
When 9Then PrevPermReturn9
When 10Then PrevPermReturn10
EndAs [PrevPermReturn],
Casen
When 1Then PrevPermExpiration1
When 2Then PrevPermExpiration2
When 3Then PrevPermExpiration3
When 4Then PrevPermExpiration4
When 5Then PrevPermExpiration5
When 6Then PrevPermExpiration6
When 7Then PrevPermExpiration7
When 8Then PrevPermExpiration8
When 9Then PrevPermExpiration9
When 10Then PrevPermExpiration10
EndAs [PrevPermExpiration],
Casen
When 1Then ConversionStatusPrev1
When 2Then ConversionStatusPrev2
When 3Then ConversionStatusPrev3
When 4Then ConversionStatusPrev4
When 5Then ConversionStatusPrev5
When 6Then ConversionStatusPrev6
When 7Then ConversionStatusPrev7
When 8Then ConversionStatusPrev8
When 9Then ConversionStatusPrev9
When 10Then ConversionStatusPrev10
EndAs [ConversionStatusPrev]
FromPWDPermitConversionData2 p
Cross Apply
(
Selectnumber As [n]From master..spt_values Where [Type] = 'P' And number > 0 And number < 11
) tally
Wherep.CustomerNumber > 0
Andp.PermitPrefix = 'M'
)cte
WhereLen(PrevPermNum) > 0
And(ConversionStatusPrev IS NULL Or ConversionStatusPrev = 0)
And
(
PrevPermNum Like '%[^0-9]%'
OrPrevPermPref Not In ('M', 'V', 'S', '', Null)
Or(PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')
Or(PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)
)
--------------------------------------------------------------------------------------------------------
-- Insert the primary record data inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
Insert Into IARTS..PWDPermit2
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate],
[PWDPermitConversionDataID],
[RecordIndicator]
)
Output
Inserted.PWDPermitConversionDataID,
Inserted.RecordIndicator,
1,
Null
Into IARTS..PermitsInserted
SelectCast(PrevPermNum as int),
CustomerNumber,
CasePrevPermPref
When 'M'Then @PermitType_Ind
When 'V'Then @PermitType_Temp
When 'S'Then @PermitType_Sticker
Else Null -- To create an error
End,
CasePrevPermStatus
When 'RP'Then @PermitStatus_RU
Else Cast(PrevPermStatus as int)
End,
Case
When PrevPermPref = 'M' Or PrevPermPref = 'V'Then @PermitDeviceType_Placard
When PrevPermPref = 'S'Then @PermitDeviceType_Sticker
When PrevPermPref = 'D'Then Null -- To create an error because we shouldn't be converting orgs
Else @PermitDeviceType_Unknown
End,
'999',
'999',
'99',
Case
When PrevPermExpiration = 'NONE' Or PrevPermExpiration = '' Or PrevPermExpiration = 'NOE'Then @MinDate
When Cast(PrevPermExpiration as DateTime) > @TodayThen DateAdd(mm, -1, @Today)
Else Cast(PrevPermExpiration as DateTime)
End,
Case
When PrevPermExpiration = 'NONE' Or PrevPermExpiration = '' Or PrevPermExpiration = 'NOE'Then @MaxDate
Else Cast(PrevPermExpiration as DateTime)
End,
@False,
CasePrevPermReturn
When 'Y'Then @True
Else @False
End,
@User,
@Today,
PWDPermitConversionDataID,
RecordIndicator
From
(
SelectPWDPermitConversionDataID,
CustomerNumber,
n As [RecordIndicator],
Casen
When 1Then PrevPermPref1
When 2Then PrevPermPref2
When 3Then PrevPermPref3
When 4Then PrevPermPref4
When 5Then PrevPermPref5
When 6Then PrevPermPref6
When 7Then PrevPermPref7
When 8Then PrevPermPref8
When 9Then PrevPermPref9
When 10Then PrevPermPref10
EndAs [PrevPermPref],
Casen
When 1Then PrevPermNum1
When 2Then PrevPermNum2
When 3Then PrevPermNum3
When 4Then PrevPermNum4
When 5Then PrevPermNum5
When 6Then PrevPermNum6
When 7Then PrevPermNum7
When 8Then PrevPermNum8
When 9Then PrevPermNum9
When 10Then PrevPermNum10
EndAs [PrevPermNum],
Casen
When 1Then PrevPermStatus1
When 2Then PrevPermStatus2
When 3Then PrevPermStatus3
When 4Then PrevPermStatus4
When 5Then PrevPermStatus5
When 6Then PrevPermStatus6
When 7Then PrevPermStatus7
When 8Then PrevPermStatus8
When 9Then PrevPermStatus9
When 10Then PrevPermStatus10
EndAs [PrevPermStatus],
Casen
When 1Then PrevPermReturn1
When 2Then PrevPermReturn2
When 3Then PrevPermReturn3
When 4Then PrevPermReturn4
When 5Then PrevPermReturn5
When 6Then PrevPermReturn6
When 7Then PrevPermReturn7
When 8Then PrevPermReturn8
When 9Then PrevPermReturn9
When 10Then PrevPermReturn10
EndAs [PrevPermReturn],
Casen
When 1Then PrevPermExpiration1
When 2Then PrevPermExpiration2
When 3Then PrevPermExpiration3
When 4Then PrevPermExpiration4
When 5Then PrevPermExpiration5
When 6Then PrevPermExpiration6
When 7Then PrevPermExpiration7
When 8Then PrevPermExpiration8
When 9Then PrevPermExpiration9
When 10Then PrevPermExpiration10
EndAs [PrevPermExpiration],
Casen
When 1Then ConversionStatusPrev1
When 2Then ConversionStatusPrev2
When 3Then ConversionStatusPrev3
When 4Then ConversionStatusPrev4
When 5Then ConversionStatusPrev5
When 6Then ConversionStatusPrev6
When 7Then ConversionStatusPrev7
When 8Then ConversionStatusPrev8
When 9Then ConversionStatusPrev9
When 10Then ConversionStatusPrev10
EndAs [ConversionStatusPrev]
FromPWDPermitConversionData2 p
Cross Apply
(
Selectnumber As [n]From master..spt_values Where [Type] = 'P' And number > 0 And number < 11
) tally
Wherep.CustomerNumber > 0
Andp.PermitPrefix = 'M'
)cte
WhereNot Exists
(
Select1
FromIARTS..PWDPermitConversionErrors2 pce
Wherepce.PWDPermitConversionDataID = cte.PWDPermitConversionDataID
Andpce.RecordIndicator = cte.RecordIndicator
)
AndCustomerNumber > 0
And(ConversionStatusPrev IS NULL Or ConversionStatusPrev = 0)
AndLen(PrevPermNum) > 0
AndPrevPermPref Not In ('P', 'H')
AndPrevPermPref In ('M', 'V', 'S')
AndPrevPermStatus In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99')
And(PrevPermExpiration In ('NONE', '', 'NOE') Or IsDate(PrevPermExpiration) = 1)
End Try
Begin Catch
Print ''
Print 'Insert failed'
Print '----------------------------------------'
Print Cast(Error_Message() as varchar(max))
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the previous permits
--------------------------------------------------------------------------------------------------------
;WITH cte AS
(
SELECT
PWDPermitConversionDataID,
Max(Case When RecordIndicator = 0 Then ErrorID Else Null End) As [ConversionStatus],
Max(Case When RecordIndicator = 0 Then ErrorMessage Else Null End) As [ConversionErrorMessage],
MAX(CASE WHEN Recordindicator =1 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev1,
MAX(CASE WHEN Recordindicator =1 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev1,
MAX(CASE WHEN Recordindicator =2 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev2,
MAX(CASE WHEN Recordindicator =2 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev2,
MAX(CASE WHEN Recordindicator =3 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev3,
MAX(CASE WHEN Recordindicator =3 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev3,
MAX(CASE WHEN Recordindicator =4 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev4,
MAX(CASE WHEN Recordindicator =4 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev4,
MAX(CASE WHEN Recordindicator =5 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev5,
MAX(CASE WHEN Recordindicator =5 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev5,
MAX(CASE WHEN Recordindicator =6 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev6,
MAX(CASE WHEN Recordindicator =6 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev6,
MAX(CASE WHEN Recordindicator =7 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev7,
MAX(CASE WHEN Recordindicator =7 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev7,
MAX(CASE WHEN Recordindicator =8 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev8,
MAX(CASE WHEN Recordindicator =8 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev8,
MAX(CASE WHEN Recordindicator =9 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev9,
MAX(CASE WHEN Recordindicator =9 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev9,
MAX(CASE WHEN Recordindicator =10 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev10,
MAX(CASE WHEN Recordindicator =10 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev10
FROM IARTS..PermitsInserted
GROUP BY PWDPermitConversionDataID
)
Update IARTS..PWDPermitConversionData2
SetConversionStatus = (Case When cte.ConversionStatus IS NULL Then 0 Else cte.ConversionStatus End),
ConversionErrorMessage = cte.ConversionErrorMessage,
ConversionStatusPrev1 = cte.ConversionStatusPrev1,
ConversionErrorMessagePrev1 = cte.ConversionErrorMessagePrev1,
ConversionStatusPrev2 = cte.ConversionStatusPrev2,
ConversionErrorMessagePrev2 = cte.ConversionErrorMessagePrev2,
ConversionStatusPrev3 = cte.ConversionStatusPrev3,
ConversionErrorMessagePrev3 = cte.ConversionErrorMessagePrev3,
ConversionStatusPrev4 = cte.ConversionStatusPrev4,
ConversionErrorMessagePrev4 = cte.ConversionErrorMessagePrev4,
ConversionStatusPrev5 = cte.ConversionStatusPrev5,
ConversionErrorMessagePrev5 = cte.ConversionErrorMessagePrev5,
ConversionStatusPrev6 = cte.ConversionStatusPrev6,
ConversionErrorMessagePrev6 = cte.ConversionErrorMessagePrev6,
ConversionStatusPrev7 = cte.ConversionStatusPrev7,
ConversionErrorMessagePrev7 = cte.ConversionErrorMessagePrev7,
ConversionStatusPrev8 = cte.ConversionStatusPrev8,
ConversionErrorMessagePrev8 = cte.ConversionErrorMessagePrev8,
ConversionStatusPrev9 = cte.ConversionStatusPrev9,
ConversionErrorMessagePrev9 = cte.ConversionErrorMessagePrev9,
ConversionStatusPrev10 = cte.ConversionStatusPrev10,
ConversionErrorMessagePrev10 = cte.ConversionErrorMessagePrev10
FROM PWDPermitConversionData2 p
Inner JOIN cte ON p.PWDPermitConversionDataID = cte.PWDPermitConversionDataID
Drop Table IARTS..PermitsInserted
April 20, 2010 at 1:26 pm
Quick question, can you reuse CTE's in 2 separate statements inside a procedure?
April 20, 2010 at 1:59 pm
gregory.anderson (4/20/2010)
Quick question, can you reuse CTE's in 2 separate statements inside a procedure?
Nope. You'd have to use an intermediate table to hold the results (either SELECT INTO or INSERT INTO).
Something like
;WITH cte AS
(
SELECT cols
FROM table
WHERE condition
)
SELECT *
INTO intermed_or_temp_table
FROM CTE
April 21, 2010 at 8:36 am
Hey Lutz, can you give me a hand with this? I have converted this over to one insert into the errors table for both primary and previous permits, and also one insert into the final table for both primary and previous, but it keeps erroring out on me saying "Conversion failed when converting the varchar value 'Y' (or 'N') to data type bit". I only have 2 bit fields in the final table "InvalidDLNumber" and "Returned". For the insert statement, I have the case statement for both of those like this:
CasePrevInvalidDLNumber
When 'Y'Then @True
When 'N'Then @False
Else @False
End,
CasePrevPermReturn
When 'Y'Then @True
When 'N'Then @False
Else @False
End,
By the way, here is the entire converted code:
Use IARTS
Go
/* -- test setup to reset tables
UPDATE PWDPermitConversionData2
SET conversionstatus=0,
conversionstatusprev1 = null,
conversionstatusprev2 = null,
conversionstatusprev3 = null,
conversionstatusprev4 = null,
conversionstatusprev5 = null,
conversionstatusprev6 = null,
conversionstatusprev7 = null,
conversionstatusprev8 = null,
conversionstatusprev9 = null,
conversionstatusprev10 = null
FROM PWDPermitConversionData2
TRUNCATE TABLE PWDPermit2
TRUNCATE TABLE PWDPermitConversionErrors2
*/
DECLARE
@User VARCHAR(8),
@True BIT,
@False BIT,
@PermitType_Org INT,
@PermitType_Ind INT,
@PermitType_Temp INT,
@PermitType_Sticker INT,
@PermitStatus_RU INT,
@PermitDeviceType_Placard INT,
@PermitDeviceType_Sticker INT,
@PermitDeviceType_Unknown INT,
@Today DATETIME,
@MinDate DATETIME,
@MaxDate DATETIME,
@DateMinus1Year DATETIME
SET @User = 'PWDConv'
SET @True = 0x1
SET @False = 0x0
SET @Today = GETDATE()
SET @MinDate = CONVERT(DATETIME, '1901-01-01')
SET @MaxDate = CONVERT(DATETIME, '2100-01-01')
SET @DateMinus1Year = DATEADD(yy, -1, @Today)
SELECT @PermitType_Org = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Organization'
SELECT @PermitType_Ind = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Permanent'
SELECT @PermitType_Temp = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Temporary'
SELECT @PermitType_Sticker = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Sticker'
SELECT @PermitStatus_RU = PWDPermitStatusID From IARTS..PWDPermitStatus Where LookupValue = 'RtrndUnDel'
SELECT @PermitDeviceType_Placard = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Placard'
SELECT @PermitDeviceType_Sticker = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Sticker'
SELECT @PermitDeviceType_Unknown = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Unknown'
--------------------------------------------------------------------------------------------------------
-- Create the table that will be used to track what was actually inserted
--------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PermitsInserted]') AND type in (N'U'))
DROP TABLE [dbo].[PermitsInserted]
Create Table IARTS..PermitsInserted
(
[PWDPermitConversionDataID] Int Not Null,
[RecordIndicator] Int Not Null,
[ErrorID] Int Not Null,
[ErrorMessage] varchar(1000) Null
)
--------------------------------------------------------------------------------------------------------
-- Insert the permits that will fail conversion
--------------------------------------------------------------------------------------------------------
Insert Into IARTS..PWDPermitConversionErrors2
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
Output
Inserted.PWDPermitConversionDataID,
Inserted.RecordIndicator,
Inserted.ErrorID,
Inserted.ErrorMessage
Into IARTS..PermitsInserted
SelectPWDPermitConversionDataID,
RecordIndicator,
Case
When PrevPermNum Like '%[^0-9]%' Or PrevPermNum = ''Then 5000
When PrevPermPref Not In ('M', 'V', 'S', '', Null)Then 5001
When (PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')Then 5002
When (PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)Then 5003
Else 5004
EndAs [ErrorID],
Case
When PrevPermNum Like '%[^0-9]%' Or PrevPermNum = ''Then 'non-numeric [PermitNumber]'
When PrevPermPref Not In ('M', 'V', 'S', '', Null)Then 'invalid [PermitPrefix]'
When (PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')Then 'invalid [Status]'
When (PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)Then 'invalid [ExpirationDate]'
Else 'unknown error'
EndAs [ErrorMessage]
From
(
SelectPWDPermitConversionDataID,
CustomerNumber,
n As [RecordIndicator],
Casen
When 0Then PermitPrefix
When 1Then PrevPermPref1
When 2Then PrevPermPref2
When 3Then PrevPermPref3
When 4Then PrevPermPref4
When 5Then PrevPermPref5
When 6Then PrevPermPref6
When 7Then PrevPermPref7
When 8Then PrevPermPref8
When 9Then PrevPermPref9
When 10Then PrevPermPref10
EndAs [PrevPermPref],
Casen
When 0Then PermitNumber
When 1Then PrevPermNum1
When 2Then PrevPermNum2
When 3Then PrevPermNum3
When 4Then PrevPermNum4
When 5Then PrevPermNum5
When 6Then PrevPermNum6
When 7Then PrevPermNum7
When 8Then PrevPermNum8
When 9Then PrevPermNum9
When 10Then PrevPermNum10
EndAs [PrevPermNum],
Casen
When 0Then [Status]
When 1Then PrevPermStatus1
When 2Then PrevPermStatus2
When 3Then PrevPermStatus3
When 4Then PrevPermStatus4
When 5Then PrevPermStatus5
When 6Then PrevPermStatus6
When 7Then PrevPermStatus7
When 8Then PrevPermStatus8
When 9Then PrevPermStatus9
When 10Then PrevPermStatus10
EndAs [PrevPermStatus],
Casen
When 0Then 0
When 1Then PrevPermReturn1
When 2Then PrevPermReturn2
When 3Then PrevPermReturn3
When 4Then PrevPermReturn4
When 5Then PrevPermReturn5
When 6Then PrevPermReturn6
When 7Then PrevPermReturn7
When 8Then PrevPermReturn8
When 9Then PrevPermReturn9
When 10Then PrevPermReturn10
EndAs [PrevPermReturn],
Casen
When 0Then ExpirationDate
When 1Then PrevPermExpiration1
When 2Then PrevPermExpiration2
When 3Then PrevPermExpiration3
When 4Then PrevPermExpiration4
When 5Then PrevPermExpiration5
When 6Then PrevPermExpiration6
When 7Then PrevPermExpiration7
When 8Then PrevPermExpiration8
When 9Then PrevPermExpiration9
When 10Then PrevPermExpiration10
EndAs [PrevPermExpiration],
Casen
When 0Then ConversionStatus
When 1Then ConversionStatusPrev1
When 2Then ConversionStatusPrev2
When 3Then ConversionStatusPrev3
When 4Then ConversionStatusPrev4
When 5Then ConversionStatusPrev5
When 6Then ConversionStatusPrev6
When 7Then ConversionStatusPrev7
When 8Then ConversionStatusPrev8
When 9Then ConversionStatusPrev9
When 10Then ConversionStatusPrev10
EndAs [ConversionStatusPrev]
FromPWDPermitConversionData2 p
Cross Apply
(
Selectnumber As [n]From master..spt_values Where [Type] = 'P' And number >= 0 And number < 11
) tally
Wherep.CustomerNumber > 0
Andp.PermitPrefix = 'M'
)cte
WhereLen(PrevPermNum) > 0
And(ConversionStatusPrev IS NULL Or ConversionStatusPrev = 0)
And
(
PrevPermNum Like '%[^0-9]%'
OrPrevPermPref Not In ('M', 'V', 'S', '', Null)
Or(PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')
Or(PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)
)
--------------------------------------------------------------------------------------------------------
-- Insert the permits inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
Insert Into IARTS..PWDPermit2
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate],
[PWDPermitConversionDataID],
[RecordIndicator]
)
Output
Inserted.PWDPermitConversionDataID,
Inserted.RecordIndicator,
1,
Null
Into IARTS..PermitsInserted
SelectCast(PrevPermNum as int),
CustomerNumber,
CasePrevPermPref
When 'D'Then @PermitType_Org
When 'M'Then @PermitType_Ind
When 'V'Then @PermitType_Temp
When 'S'Then @PermitType_Sticker
Else Null -- To create an error
End,
CasePrevPermStatus
When 'RP'Then @PermitStatus_RU
Else Cast(PrevPermStatus as int)
End,
Case
When PrevPermPref In ('D', 'M', 'V')Then @PermitDeviceType_Placard
When PrevPermPref = 'S'Then @PermitDeviceType_Sticker
Else @PermitDeviceType_Unknown
End,
Case When PrevLocation = '' Or PrevLocation IS NULL Then '999'
Else PrevLocation
End,
Case When PrevHCExam = '' Or PrevHCExam IS NULL Then '999'
Else PrevHCExam
End,
Case When PrevHCStation = '' Or PrevHCStation IS NULL Then '99'
Else PrevHCStation
End,
Case
When PrevIssueDate = 'NONE' Or PrevIssueDate = '' Or PrevIssueDate = 'NOE'Then @MinDate
Else Cast(PrevPermExpiration as DateTime)
End,
Case
When PrevPermExpiration = 'NONE' Or PrevPermExpiration = '' Or PrevPermExpiration = 'NOE'Then @MaxDate
Else Cast(PrevPermExpiration as DateTime)
End,
CasePrevInvalidDLNumber
When 'Y'Then @True
When 'N'Then @False
Else @False
End,
CasePrevPermReturn
When 'Y'Then @True
When 'N'Then @False
Else @False
End,
@User,
@Today,
PWDPermitConversionDataID,
RecordIndicator
From
(
SelectPWDPermitConversionDataID,
CustomerNumber,
n As [RecordIndicator],
Casen
When 0Then PermitPrefix
When 1Then PrevPermPref1
When 2Then PrevPermPref2
When 3Then PrevPermPref3
When 4Then PrevPermPref4
When 5Then PrevPermPref5
When 6Then PrevPermPref6
When 7Then PrevPermPref7
When 8Then PrevPermPref8
When 9Then PrevPermPref9
When 10Then PrevPermPref10
EndAs [PrevPermPref],
Casen
When 0Then PermitNumber
When 1Then PrevPermNum1
When 2Then PrevPermNum2
When 3Then PrevPermNum3
When 4Then PrevPermNum4
When 5Then PrevPermNum5
When 6Then PrevPermNum6
When 7Then PrevPermNum7
When 8Then PrevPermNum8
When 9Then PrevPermNum9
When 10Then PrevPermNum10
EndAs [PrevPermNum],
Casen
When 0Then [Status]
When 1Then PrevPermStatus1
When 2Then PrevPermStatus2
When 3Then PrevPermStatus3
When 4Then PrevPermStatus4
When 5Then PrevPermStatus5
When 6Then PrevPermStatus6
When 7Then PrevPermStatus7
When 8Then PrevPermStatus8
When 9Then PrevPermStatus9
When 10Then PrevPermStatus10
EndAs [PrevPermStatus],
Casen
When 0Then 0
When 1Then PrevPermReturn1
When 2Then PrevPermReturn2
When 3Then PrevPermReturn3
When 4Then PrevPermReturn4
When 5Then PrevPermReturn5
When 6Then PrevPermReturn6
When 7Then PrevPermReturn7
When 8Then PrevPermReturn8
When 9Then PrevPermReturn9
When 10Then PrevPermReturn10
EndAs [PrevPermReturn],
Casen
When 0Then IssueDate
--When 1Then (Case When PrevPermExpiration1 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration1) = 0 Then 0 When PrevPermExpiration1 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration1) = 1 And Cast(PrevPermExpiration1 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration1 as DateTime) End)
--When 2Then (Case When PrevPermExpiration2 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration2) = 0 Then 0 When PrevPermExpiration2 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration2) = 1 And Cast(PrevPermExpiration2 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration2 as DateTime) End)
--When 3Then (Case When PrevPermExpiration3 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration3) = 0 Then 0 When PrevPermExpiration3 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration3) = 1 And Cast(PrevPermExpiration3 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration3 as DateTime) End)
--When 4Then (Case When PrevPermExpiration4 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration4) = 0 Then 0 When PrevPermExpiration4 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration4) = 1 And Cast(PrevPermExpiration4 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration4 as DateTime) End)
--When 5Then (Case When PrevPermExpiration5 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration5) = 0 Then 0 When PrevPermExpiration5 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration5) = 1 And Cast(PrevPermExpiration5 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration5 as DateTime) End)
--When 6Then (Case When PrevPermExpiration6 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration6) = 0 Then 0 When PrevPermExpiration6 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration6) = 1 And Cast(PrevPermExpiration6 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration6 as DateTime) End)
--When 7Then (Case When PrevPermExpiration7 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration7) = 0 Then 0 When PrevPermExpiration7 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration7) = 1 And Cast(PrevPermExpiration7 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration7 as DateTime) End)
--When 8Then (Case When PrevPermExpiration8 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration8) = 0 Then 0 When PrevPermExpiration8 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration8) = 1 And Cast(PrevPermExpiration8 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration8 as DateTime) End)
--When 9Then (Case When PrevPermExpiration9 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration9) = 0 Then 0 When PrevPermExpiration9 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration9) = 1 And Cast(PrevPermExpiration9 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration9 as DateTime) End)
--When 10Then (Case When PrevPermExpiration10 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration10) = 0 Then 0 When PrevPermExpiration10 In ('NONE', '', 'NOE') Then @MinDate When IsDate(PrevPermExpiration10) = 1 And Cast(PrevPermExpiration10 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration10 as DateTime) End)
When 1Then PrevPermExpiration1
When 2Then PrevPermExpiration2
When 3Then PrevPermExpiration3
When 4Then PrevPermExpiration4
When 5Then PrevPermExpiration5
When 6Then PrevPermExpiration6
When 7Then PrevPermExpiration7
When 8Then PrevPermExpiration8
When 9Then PrevPermExpiration9
When 10Then PrevPermExpiration10
End As [PrevIssueDate],
Casen
When 0Then ExpirationDate
When 1Then PrevPermExpiration1
When 2Then PrevPermExpiration2
When 3Then PrevPermExpiration3
When 4Then PrevPermExpiration4
When 5Then PrevPermExpiration5
When 6Then PrevPermExpiration6
When 7Then PrevPermExpiration7
When 8Then PrevPermExpiration8
When 9Then PrevPermExpiration9
When 10Then PrevPermExpiration10
EndAs [PrevPermExpiration],
Casen
When 0Then Location
Else '999'
End As [PrevLocation],
Casen
When 0Then HCExam
Else '999'
End As [PrevHCExam],
Casen
When 0Then HCStation
Else '99'
End As [PrevHCStation],
Casen
When 0Then InvalidDLNumber
Else @False
End As [PrevInvalidDLNumber],
Casen
When 0Then ConversionStatus
When 1Then ConversionStatusPrev1
When 2Then ConversionStatusPrev2
When 3Then ConversionStatusPrev3
When 4Then ConversionStatusPrev4
When 5Then ConversionStatusPrev5
When 6Then ConversionStatusPrev6
When 7Then ConversionStatusPrev7
When 8Then ConversionStatusPrev8
When 9Then ConversionStatusPrev9
When 10Then ConversionStatusPrev10
EndAs [ConversionStatusPrev]
FromPWDPermitConversionData2 p
Cross Apply
(
Selectnumber As [n]From master..spt_values Where [Type] = 'P' And number >= 0 And number < 11
) tally
Wherep.CustomerNumber > 0
Andp.PermitPrefix = 'M'
)cte
WhereNot Exists
(
Select1
FromIARTS..PWDPermitConversionErrors2 pce
Wherepce.PWDPermitConversionDataID = cte.PWDPermitConversionDataID
Andpce.RecordIndicator = cte.RecordIndicator
)
AndCustomerNumber > 0
And(ConversionStatusPrev IS NULL Or ConversionStatusPrev = 0)
AndLen(PrevPermNum) > 0
AndPrevPermPref Not In ('P', 'H')
And
(
Case When RecordIndicator > 0 And PrevPermPref In ('M', 'V', 'S') Then 1
When RecordIndicator = 0 And PrevPermPref In ('D', 'M', 'V', 'S') Then 1
Else 0 End = 1
)
And
(
Case When RecordIndicator > 0 And PrevPermStatus In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') Then 1
When RecordIndicator = 0 And PrevPermStatus In ('RP', '01', '02', '03', '04', '05', '07', '08', '09', '10', '98', '99') Then 1
Else 0 End = 1
)
And
(
Case When PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0 Then 0
When RecordIndicator = 0 And PrevPermPref = 'M' And IsDate(PrevPermExpiration) = 1 Then 1
When RecordIndicator = 0 And PrevPermPref <> 'M' And IsDate(PrevPermExpiration) = 1 And Convert(DateTime, PrevPermExpiration) >= @DateMinus1Year Then 1
When RecordIndicator = 0 And PrevPermPref <> 'M' And IsDate(PrevPermExpiration) = 1 And Convert(DateTime, PrevPermExpiration) < @DateMinus1Year Then 0
--When RecordIndicator > 0 And (PrevPermExpiration In ('NONE', '', 'NOE') Or IsDate(PrevPermExpiration) = 1) Then 1
--When RecordIndicator = 0 And PrevPermExpiration <> 'NONE' And PrevPermExpiration <> 'NOE' And PrevPermExpiration <> '' Then 1
Else 0 End = 1
)
End Try
Begin Catch
Print ''
Print 'Insert failed'
Print '----------------------------------------'
Print Cast(Error_Message() as varchar(max))
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the previous permits
--------------------------------------------------------------------------------------------------------
;WITH cte AS
(
SELECT
PWDPermitConversionDataID,
Max(Case When RecordIndicator = 0 Then ErrorID Else Null End) As [ConversionStatus],
Max(Case When RecordIndicator = 0 Then ErrorMessage Else Null End) As [ConversionErrorMessage],
MAX(CASE WHEN Recordindicator = 1 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev1,
MAX(CASE WHEN Recordindicator = 1 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev1,
MAX(CASE WHEN Recordindicator = 2 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev2,
MAX(CASE WHEN Recordindicator = 2 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev2,
MAX(CASE WHEN Recordindicator = 3 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev3,
MAX(CASE WHEN Recordindicator = 3 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev3,
MAX(CASE WHEN Recordindicator = 4 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev4,
MAX(CASE WHEN Recordindicator = 4 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev4,
MAX(CASE WHEN Recordindicator = 5 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev5,
MAX(CASE WHEN Recordindicator = 5 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev5,
MAX(CASE WHEN Recordindicator = 6 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev6,
MAX(CASE WHEN Recordindicator = 6 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev6,
MAX(CASE WHEN Recordindicator = 7 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev7,
MAX(CASE WHEN Recordindicator = 7 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev7,
MAX(CASE WHEN Recordindicator = 8 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev8,
MAX(CASE WHEN Recordindicator = 8 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev8,
MAX(CASE WHEN Recordindicator = 9 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev9,
MAX(CASE WHEN Recordindicator = 9 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev9,
MAX(CASE WHEN Recordindicator = 10 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev10,
MAX(CASE WHEN Recordindicator = 10 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev10
FROM IARTS..PermitsInserted
GROUP BY PWDPermitConversionDataID
)
Update IARTS..PWDPermitConversionData2
SetConversionStatus = (Case When cte.ConversionStatus IS NULL Then 0 Else cte.ConversionStatus End),
ConversionErrorMessage = cte.ConversionErrorMessage,
ConversionStatusPrev1 = cte.ConversionStatusPrev1,
ConversionErrorMessagePrev1 = cte.ConversionErrorMessagePrev1,
ConversionStatusPrev2 = cte.ConversionStatusPrev2,
ConversionErrorMessagePrev2 = cte.ConversionErrorMessagePrev2,
ConversionStatusPrev3 = cte.ConversionStatusPrev3,
ConversionErrorMessagePrev3 = cte.ConversionErrorMessagePrev3,
ConversionStatusPrev4 = cte.ConversionStatusPrev4,
ConversionErrorMessagePrev4 = cte.ConversionErrorMessagePrev4,
ConversionStatusPrev5 = cte.ConversionStatusPrev5,
ConversionErrorMessagePrev5 = cte.ConversionErrorMessagePrev5,
ConversionStatusPrev6 = cte.ConversionStatusPrev6,
ConversionErrorMessagePrev6 = cte.ConversionErrorMessagePrev6,
ConversionStatusPrev7 = cte.ConversionStatusPrev7,
ConversionErrorMessagePrev7 = cte.ConversionErrorMessagePrev7,
ConversionStatusPrev8 = cte.ConversionStatusPrev8,
ConversionErrorMessagePrev8 = cte.ConversionErrorMessagePrev8,
ConversionStatusPrev9 = cte.ConversionStatusPrev9,
ConversionErrorMessagePrev9 = cte.ConversionErrorMessagePrev9,
ConversionStatusPrev10 = cte.ConversionStatusPrev10,
ConversionErrorMessagePrev10 = cte.ConversionErrorMessagePrev10
FROM PWDPermitConversionData2 p
Inner JOIN cte ON p.PWDPermitConversionDataID = cte.PWDPermitConversionDataID
Drop Table IARTS..PermitsInserted
Once I get this fixed, I just have to compare the resulting tables to make sure they match the first conversion...
April 21, 2010 at 8:55 am
And when I comment those two case statements out and just put 2 @False values for those fields the insert works fine.
Edit: It's getting wierder (is that a word), when I comment just 1 of them out, it fails, but it doesn't matter which one...
Edit2: Ok, it was because of the subquery for those fields...
April 21, 2010 at 11:47 am
gregory.anderson (4/21/2010)
And when I comment those two case statements out and just put 2 @False values for those fields the insert works fine.Edit: It's getting wierder (is that a word), when I comment just 1 of them out, it fails, but it doesn't matter which one...
Edit2: Ok, it was because of the subquery for those fields...
Seems like you really make some progress!!
As far as I can see you define [PrevInvalidDLNumber] using the CASE statement, you should either have 'Y' or 'N' as a result (which you use in your outer SELECT to set it to TRUE or FALSE, or you set TRUE/FALSE in the subquery right away. Otherwise it will fail with the error you noticed.
April 21, 2010 at 11:49 am
Ok, I have everything working now.
Doing the first and second inserts with a subqueries for each statement containing the pivoted rows, it took 2:36 the first try.
I then took the subquery (the bigger one that contained more data) and made that into a table variable, then have both inserts just grab from @tablename, on the first try of this run, it went over 16 minutes so I stopped. Why so long? Is the insert into the table variable really that slow? (It produces about 6.1 million rows)
Next, I tried using a #temp table instead of the @table variable, this took 3:45. (Hmm, remind me never to use table variables)
Next, I implemented the two subqueries (or previously the #temp table) into two separate CTE's, 1 for the failed records insert, and 1 for the successful records insert. The first try with this resulted in a run of...hmmm inserted 0 records.
I guess I have more tweaking to do with the cte version.
Anyway, here's the working code:
Use IARTS
Go
/* -- test setup to reset tables
UPDATE PWDPermitConversionData2
SET conversionstatus=0,
conversionstatusprev1 = null,
conversionstatusprev2 = null,
conversionstatusprev3 = null,
conversionstatusprev4 = null,
conversionstatusprev5 = null,
conversionstatusprev6 = null,
conversionstatusprev7 = null,
conversionstatusprev8 = null,
conversionstatusprev9 = null,
conversionstatusprev10 = null
FROM PWDPermitConversionData2
TRUNCATE TABLE PWDPermit2
TRUNCATE TABLE PWDPermitConversionErrors2
*/
DECLARE
@User VARCHAR(8),
@True BIT,
@False BIT,
@PermitType_Org INT,
@PermitType_Ind INT,
@PermitType_Temp INT,
@PermitType_Sticker INT,
@PermitStatus_RU INT,
@PermitDeviceType_Placard INT,
@PermitDeviceType_Sticker INT,
@PermitDeviceType_Unknown INT,
@Today DATETIME,
@MinDate DATETIME,
@MaxDate DATETIME,
@DateMinus1Year DATETIME
SET @User = 'PWDConv'
SET @True = 0x1
SET @False = 0x0
SET @Today = GETDATE()
SET @MinDate = CONVERT(DATETIME, '1901-01-01')
SET @MaxDate = CONVERT(DATETIME, '2100-01-01')
SET @DateMinus1Year = DATEADD(yy, -1, @Today)
SELECT @PermitType_Org = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Organization'
SELECT @PermitType_Ind = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Permanent'
SELECT @PermitType_Temp = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Temporary'
SELECT @PermitType_Sticker = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Sticker'
SELECT @PermitStatus_RU = PWDPermitStatusID From IARTS..PWDPermitStatus Where LookupValue = 'RtrndUnDel'
SELECT @PermitDeviceType_Placard = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Placard'
SELECT @PermitDeviceType_Sticker = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Sticker'
SELECT @PermitDeviceType_Unknown = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Unknown'
--------------------------------------------------------------------------------------------------------
-- Create the table that will be used to track what was actually inserted
--------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PermitsInserted]') AND type in (N'U'))
DROP TABLE [dbo].[PermitsInserted]
Create Table IARTS..PermitsInserted
(
[PWDPermitConversionDataID] Int Not Null,
[RecordIndicator] Int Not Null,
[ErrorID] Int Not Null,
[ErrorMessage] varchar(1000) Null
)
--------------------------------------------------------------------------------------------------------
-- Insert the permits that will fail conversion
--------------------------------------------------------------------------------------------------------
Insert Into IARTS..PWDPermitConversionErrors2
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
Output
Inserted.PWDPermitConversionDataID,
Inserted.RecordIndicator,
Inserted.ErrorID,
Inserted.ErrorMessage
Into IARTS..PermitsInserted
SelectPWDPermitConversionDataID,
RecordIndicator,
Case
When PrevPermNum Like '%[^0-9]%' Or PrevPermNum = ''Then 5000
When PrevPermPref Not In ('D', 'M', 'V', 'S')Then 5001
When (PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')Then 5002
When (PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)Then 5003
Else 5004
EndAs [ErrorID],
Case
When PrevPermNum Like '%[^0-9]%' Or PrevPermNum = ''Then 'non-numeric [PermitNumber]'
When PrevPermPref Not In ('D', 'M', 'V', 'S')Then 'invalid [PermitPrefix]'
When (PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')Then 'invalid [Status]'
When (PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)Then 'invalid [ExpirationDate]'
Else 'unknown error'
EndAs [ErrorMessage]
From
(
SelectPWDPermitConversionDataID,
CustomerNumber,
n As [RecordIndicator],
Casen
When 0Then PermitPrefix
When 1Then PrevPermPref1
When 2Then PrevPermPref2
When 3Then PrevPermPref3
When 4Then PrevPermPref4
When 5Then PrevPermPref5
When 6Then PrevPermPref6
When 7Then PrevPermPref7
When 8Then PrevPermPref8
When 9Then PrevPermPref9
When 10Then PrevPermPref10
EndAs [PrevPermPref],
Casen
When 0Then PermitNumber
When 1Then PrevPermNum1
When 2Then PrevPermNum2
When 3Then PrevPermNum3
When 4Then PrevPermNum4
When 5Then PrevPermNum5
When 6Then PrevPermNum6
When 7Then PrevPermNum7
When 8Then PrevPermNum8
When 9Then PrevPermNum9
When 10Then PrevPermNum10
EndAs [PrevPermNum],
Casen
When 0Then [Status]
When 1Then PrevPermStatus1
When 2Then PrevPermStatus2
When 3Then PrevPermStatus3
When 4Then PrevPermStatus4
When 5Then PrevPermStatus5
When 6Then PrevPermStatus6
When 7Then PrevPermStatus7
When 8Then PrevPermStatus8
When 9Then PrevPermStatus9
When 10Then PrevPermStatus10
EndAs [PrevPermStatus],
Casen
When 0Then ExpirationDate
When 1Then PrevPermExpiration1
When 2Then PrevPermExpiration2
When 3Then PrevPermExpiration3
When 4Then PrevPermExpiration4
When 5Then PrevPermExpiration5
When 6Then PrevPermExpiration6
When 7Then PrevPermExpiration7
When 8Then PrevPermExpiration8
When 9Then PrevPermExpiration9
When 10Then PrevPermExpiration10
EndAs [PrevPermExpiration],
Casen
When 0Then ConversionStatus
When 1Then ConversionStatusPrev1
When 2Then ConversionStatusPrev2
When 3Then ConversionStatusPrev3
When 4Then ConversionStatusPrev4
When 5Then ConversionStatusPrev5
When 6Then ConversionStatusPrev6
When 7Then ConversionStatusPrev7
When 8Then ConversionStatusPrev8
When 9Then ConversionStatusPrev9
When 10Then ConversionStatusPrev10
EndAs [ConversionStatusPrev]
FromPWDPermitConversionData2 p
Cross Apply
(
Selectnumber As [n]From master..spt_values Where [Type] = 'P' And number >= 0 And number < 11
) tally
Wherep.CustomerNumber > 0
And
(
Case When n > 0 And p.PermitPrefix = 'M' Then 1
When n = 0 Then 1
Else 0
End = 1
)
)cte
Where(ConversionStatusPrev IS NULL Or ConversionStatusPrev = 0)
AndLen(PrevPermNum) > 0
And
(
PrevPermNum Like '%[^0-9]%'
OrPrevPermPref Not In ('D', 'M', 'V', 'S')
Or(PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')
Or(PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)
)
--------------------------------------------------------------------------------------------------------
-- Insert the permits inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
Insert Into IARTS..PWDPermit2
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate],
[PWDPermitConversionDataID],
[RecordIndicator]
)
Output
Inserted.PWDPermitConversionDataID,
Inserted.RecordIndicator,
1,
Null
Into IARTS..PermitsInserted
SelectCast(PrevPermNum as int),
CustomerNumber,
CasePrevPermPref
When 'D'Then @PermitType_Org
When 'M'Then @PermitType_Ind
When 'V'Then @PermitType_Temp
When 'S'Then @PermitType_Sticker
End,
CasePrevPermStatus
When 'RP'Then @PermitStatus_RU
Else Cast(PrevPermStatus as int)
End,
Case
When PrevPermPref In ('D', 'M', 'V')Then @PermitDeviceType_Placard
When PrevPermPref = 'S'Then @PermitDeviceType_Sticker
Else @PermitDeviceType_Unknown
End,
Case When PrevLocation = '' Or PrevLocation IS NULL Then '999'
Else PrevLocation
End,
Case When PrevHCExam = '' Or PrevHCExam IS NULL Then '999'
Else PrevHCExam
End,
Case When PrevHCStation = '' Or PrevHCStation IS NULL Then '99'
Else PrevHCStation
End,
PrevIssueDate,
Case
When PrevPermExpiration In ('NONE', '', 'NOE') Or IsDate(PrevPermExpiration) = 0Then @MaxDate
Else Cast(PrevPermExpiration as DateTime)
End,
PrevInvalidDLNumber,
PrevPermReturn,
@User,
@Today,
PWDPermitConversionDataID,
RecordIndicator
From
(
SelectPWDPermitConversionDataID,
CustomerNumber,
n As [RecordIndicator],
Casen
When 0Then PermitPrefix
When 1Then PrevPermPref1
When 2Then PrevPermPref2
When 3Then PrevPermPref3
When 4Then PrevPermPref4
When 5Then PrevPermPref5
When 6Then PrevPermPref6
When 7Then PrevPermPref7
When 8Then PrevPermPref8
When 9Then PrevPermPref9
When 10Then PrevPermPref10
EndAs [PrevPermPref],
Casen
When 0Then PermitNumber
When 1Then PrevPermNum1
When 2Then PrevPermNum2
When 3Then PrevPermNum3
When 4Then PrevPermNum4
When 5Then PrevPermNum5
When 6Then PrevPermNum6
When 7Then PrevPermNum7
When 8Then PrevPermNum8
When 9Then PrevPermNum9
When 10Then PrevPermNum10
EndAs [PrevPermNum],
Casen
When 0Then [Status]
When 1Then PrevPermStatus1
When 2Then PrevPermStatus2
When 3Then PrevPermStatus3
When 4Then PrevPermStatus4
When 5Then PrevPermStatus5
When 6Then PrevPermStatus6
When 7Then PrevPermStatus7
When 8Then PrevPermStatus8
When 9Then PrevPermStatus9
When 10Then PrevPermStatus10
EndAs [PrevPermStatus],
Case
When n = 0Then @False
When n = 1 And PrevPermReturn1 = 'Y' Then @True
When n = 2 And PrevPermReturn2 = 'Y' Then @True
When n = 3 And PrevPermReturn3 = 'Y' Then @True
When n = 4 And PrevPermReturn4 = 'Y' Then @True
When n = 5 And PrevPermReturn5 = 'Y' Then @True
When n = 6 And PrevPermReturn6 = 'Y' Then @True
When n = 7 And PrevPermReturn7 = 'Y' Then @True
When n = 8 And PrevPermReturn8 = 'Y' Then @True
When n = 9 And PrevPermReturn9 = 'Y' Then @True
When n = 10 And PrevPermReturn10 = 'Y' Then @True
Else @False
EndAs [PrevPermReturn],
Casen
When 0Then IssueDate
When 1Then (Case When PrevPermExpiration1 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration1) = 0 Then @MinDate When PrevPermExpiration1 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration1) = 1 And Cast(PrevPermExpiration1 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration1 as DateTime) End)
When 2Then (Case When PrevPermExpiration2 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration2) = 0 Then @MinDate When PrevPermExpiration2 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration2) = 1 And Cast(PrevPermExpiration2 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration2 as DateTime) End)
When 3Then (Case When PrevPermExpiration3 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration3) = 0 Then @MinDate When PrevPermExpiration3 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration3) = 1 And Cast(PrevPermExpiration3 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration3 as DateTime) End)
When 4Then (Case When PrevPermExpiration4 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration4) = 0 Then @MinDate When PrevPermExpiration4 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration4) = 1 And Cast(PrevPermExpiration4 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration4 as DateTime) End)
When 5Then (Case When PrevPermExpiration5 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration5) = 0 Then @MinDate When PrevPermExpiration5 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration5) = 1 And Cast(PrevPermExpiration5 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration5 as DateTime) End)
When 6Then (Case When PrevPermExpiration6 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration6) = 0 Then @MinDate When PrevPermExpiration6 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration6) = 1 And Cast(PrevPermExpiration6 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration6 as DateTime) End)
When 7Then (Case When PrevPermExpiration7 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration7) = 0 Then @MinDate When PrevPermExpiration7 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration7) = 1 And Cast(PrevPermExpiration7 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration7 as DateTime) End)
When 8Then (Case When PrevPermExpiration8 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration8) = 0 Then @MinDate When PrevPermExpiration8 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration8) = 1 And Cast(PrevPermExpiration8 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration8 as DateTime) End)
When 9Then (Case When PrevPermExpiration9 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration9) = 0 Then @MinDate When PrevPermExpiration9 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration9) = 1 And Cast(PrevPermExpiration9 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration9 as DateTime) End)
When 10Then (Case When PrevPermExpiration10 Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration10) = 0 Then @MinDate When PrevPermExpiration10 In ('NONE', '', 'NOE', Null) Then @MinDate When IsDate(PrevPermExpiration10) = 1 And Cast(PrevPermExpiration10 as DateTime) > @TodayThen DateAdd(yy, -1, @Today) Else Cast(PrevPermExpiration10 as DateTime) End)
End As [PrevIssueDate],
Casen
When 0Then ExpirationDate
When 1Then PrevPermExpiration1
When 2Then PrevPermExpiration2
When 3Then PrevPermExpiration3
When 4Then PrevPermExpiration4
When 5Then PrevPermExpiration5
When 6Then PrevPermExpiration6
When 7Then PrevPermExpiration7
When 8Then PrevPermExpiration8
When 9Then PrevPermExpiration9
When 10Then PrevPermExpiration10
EndAs [PrevPermExpiration],
Casen
When 0Then Location
Else '999'
End As [PrevLocation],
Casen
When 0Then HCExam
Else '999'
End As [PrevHCExam],
Casen
When 0Then HCStation
Else '99'
End As [PrevHCStation],
Case
When n = 0And InvalidDLNumber = 'Y' Then @True
Else @False
End As [PrevInvalidDLNumber],
Casen
When 0Then ConversionStatus
When 1Then ConversionStatusPrev1
When 2Then ConversionStatusPrev2
When 3Then ConversionStatusPrev3
When 4Then ConversionStatusPrev4
When 5Then ConversionStatusPrev5
When 6Then ConversionStatusPrev6
When 7Then ConversionStatusPrev7
When 8Then ConversionStatusPrev8
When 9Then ConversionStatusPrev9
When 10Then ConversionStatusPrev10
EndAs [ConversionStatusPrev]
FromPWDPermitConversionData2 p
Cross Apply
(
Selectnumber As [n]From master..spt_values Where [Type] = 'P' And number >= 0 And number < 11
) tally
Wherep.CustomerNumber > 0
And
(
Case When n > 0 And p.PermitPrefix = 'M' Then 1
When n = 0 Then 1
Else 0
End = 1
)
)cte
WhereNot Exists
(
Select1
FromIARTS..PWDPermitConversionErrors2 pce
Wherepce.PWDPermitConversionDataID = cte.PWDPermitConversionDataID
Andpce.RecordIndicator = cte.RecordIndicator
)
AndCustomerNumber > 0
And(ConversionStatusPrev IS NULL Or ConversionStatusPrev = 0)
AndLen(PrevPermNum) > 0
AndPrevPermPref Not In ('P', 'H')
And
(
Case When RecordIndicator > 0 And PrevPermPref In ('M', 'V', 'S') Then 1
When RecordIndicator = 0 And PrevPermPref In ('D', 'M', 'V', 'S') Then 1
Else 0 End = 1
)
And
(
Case When RecordIndicator > 0 And PrevPermStatus In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') Then 1
When RecordIndicator = 0 And PrevPermStatus In ('RP', '01', '02', '03', '04', '05', '07', '08', '09', '10', '98', '99') Then 1
Else 0 End = 1
)
And
(
Case When PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0 Then 0
When RecordIndicator = 0 And PrevPermPref = 'M' And IsDate(PrevPermExpiration) = 1 Then 1
When RecordIndicator = 0 And PrevPermPref <> 'M' And IsDate(PrevPermExpiration) = 1 And Convert(DateTime, PrevPermExpiration) >= @DateMinus1Year Then 1
When RecordIndicator = 0 And PrevPermPref <> 'M' And IsDate(PrevPermExpiration) = 1 And Convert(DateTime, PrevPermExpiration) < @DateMinus1Year Then 0
Else 1 End = 1
)
End Try
Begin Catch
Print ''
Print 'Insert failed'
Print '----------------------------------------'
Print Cast(Error_Message() as varchar(max))
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the previous permits
--------------------------------------------------------------------------------------------------------
;WITH cte AS
(
SELECT
PWDPermitConversionDataID,
Max(Case When RecordIndicator = 0 Then ErrorID Else Null End) As [ConversionStatus],
Max(Case When RecordIndicator = 0 Then ErrorMessage Else Null End) As [ConversionErrorMessage],
MAX(CASE WHEN Recordindicator = 1 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev1,
MAX(CASE WHEN Recordindicator = 1 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev1,
MAX(CASE WHEN Recordindicator = 2 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev2,
MAX(CASE WHEN Recordindicator = 2 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev2,
MAX(CASE WHEN Recordindicator = 3 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev3,
MAX(CASE WHEN Recordindicator = 3 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev3,
MAX(CASE WHEN Recordindicator = 4 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev4,
MAX(CASE WHEN Recordindicator = 4 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev4,
MAX(CASE WHEN Recordindicator = 5 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev5,
MAX(CASE WHEN Recordindicator = 5 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev5,
MAX(CASE WHEN Recordindicator = 6 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev6,
MAX(CASE WHEN Recordindicator = 6 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev6,
MAX(CASE WHEN Recordindicator = 7 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev7,
MAX(CASE WHEN Recordindicator = 7 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev7,
MAX(CASE WHEN Recordindicator = 8 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev8,
MAX(CASE WHEN Recordindicator = 8 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev8,
MAX(CASE WHEN Recordindicator = 9 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev9,
MAX(CASE WHEN Recordindicator = 9 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev9,
MAX(CASE WHEN Recordindicator = 10 THEN ErrorID ELSE NULL END) AS ConversionStatusPrev10,
MAX(CASE WHEN Recordindicator = 10 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev10
FROM IARTS..PermitsInserted
GROUP BY PWDPermitConversionDataID
)
Update IARTS..PWDPermitConversionData2
SetConversionStatus = (Case When cte.ConversionStatus IS NULL Then 0 Else cte.ConversionStatus End),
ConversionErrorMessage = cte.ConversionErrorMessage,
ConversionStatusPrev1 = cte.ConversionStatusPrev1,
ConversionErrorMessagePrev1 = cte.ConversionErrorMessagePrev1,
ConversionStatusPrev2 = cte.ConversionStatusPrev2,
ConversionErrorMessagePrev2 = cte.ConversionErrorMessagePrev2,
ConversionStatusPrev3 = cte.ConversionStatusPrev3,
ConversionErrorMessagePrev3 = cte.ConversionErrorMessagePrev3,
ConversionStatusPrev4 = cte.ConversionStatusPrev4,
ConversionErrorMessagePrev4 = cte.ConversionErrorMessagePrev4,
ConversionStatusPrev5 = cte.ConversionStatusPrev5,
ConversionErrorMessagePrev5 = cte.ConversionErrorMessagePrev5,
ConversionStatusPrev6 = cte.ConversionStatusPrev6,
ConversionErrorMessagePrev6 = cte.ConversionErrorMessagePrev6,
ConversionStatusPrev7 = cte.ConversionStatusPrev7,
ConversionErrorMessagePrev7 = cte.ConversionErrorMessagePrev7,
ConversionStatusPrev8 = cte.ConversionStatusPrev8,
ConversionErrorMessagePrev8 = cte.ConversionErrorMessagePrev8,
ConversionStatusPrev9 = cte.ConversionStatusPrev9,
ConversionErrorMessagePrev9 = cte.ConversionErrorMessagePrev9,
ConversionStatusPrev10 = cte.ConversionStatusPrev10,
ConversionErrorMessagePrev10 = cte.ConversionErrorMessagePrev10
FROM PWDPermitConversionData2 p
Inner JOIN cte ON p.PWDPermitConversionDataID = cte.PWDPermitConversionDataID
April 21, 2010 at 1:13 pm
Ok,
a few things you can change:
[PrevIssueDate]:
Don't do the comparison inside the CASE statement of the subquery.
Move it to the outer SELECT. Also, I don't think you need to do the "Not In (...)" test since IsDate(PrevPermExpiration) = 0 will be true for all of them and the assigned value (@MinDate) is identical.
change
Case When RecordIndicator > 0 And PrevPermPref In ('M', 'V', 'S') Then 1
When RecordIndicator = 0 And PrevPermPref In ('D', 'M', 'V', 'S') Then 1
Else 0 End = 1
to
Case When RecordIndicator = 0 And PrevPermPref ='D' Then 1
When RecordIndicator >= 0 And PrevPermPref In ('M', 'V', 'S') Then 1
Else 0 End = 1
-- makes it a little easier to read from my point of view
change
Case When RecordIndicator > -1 And PrevPermStatus In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') Then 1
When RecordIndicator = 0 And PrevPermStatus In ('RP', '01', '02', '03', '04', '05', '07', '08', '09', '10', '98', '99') Then 1
Else 0 End
--to
Case When RecordIndicator = 0 And PrevPermStatus ='06' Then 1
When RecordIndicator > -1 And PrevPermStatus In ('RP', '01', '02', '03', '04', '05', '07', '08', '09', '10', '98', '99') Then 1
Else 0 End
-- again,readability
change
Case When PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0 Then 0
When RecordIndicator = 0 And PrevPermPref = 'M' And IsDate(PrevPermExpiration) = 1 Then 1
When RecordIndicator = 0 And PrevPermPref <> 'M' And IsDate(PrevPermExpiration) = 1 And Convert(DateTime, PrevPermExpiration) >= @DateMinus1Year Then 1
When RecordIndicator = 0 And PrevPermPref <> 'M' And IsDate(PrevPermExpiration) = 1 And Convert(DateTime, PrevPermExpiration) < @DateMinus1Year Then 0
Else 1 End = 1
to
Case When PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0 Then 0
When RecordIndicator = 0 And PrevPermPref = 'M' And IsDate(PrevPermExpiration) = 1 Then 1
When RecordIndicator = 0 And PrevPermPref <> 'M' And IsDate(PrevPermExpiration) = 1 And PrevPermExpiration >= convert(char(8),@DateMinus1Year,112) Then 1
When RecordIndicator = 0 And PrevPermPref <> 'M' And IsDate(PrevPermExpiration) = 1 And PrevPermExpiration < convert(char(8),@DateMinus1Year,112) Then 0
Else 1 End = 1
-- I converted the value of @DateMinus1Year to a character instead each and every row of the table. Should perform better and will support indexes if we figure we need some...)
Furthermore, please attach the actual execution plan as .sqplan file (enable "IncludeActualExecutionPlan", find the three relevant queries (insert errors, insert results, update) right click on each plan and save it as sqlplan).
After doing so, please disable and add "SET statistics time,io ON" rsp. "SET statistics time,io OFF] at the beginning rsp. the end of the batch. This will also help to find room for improvement.
Talking about table variables: it's been treated as it would hold only one row. This may lead to a very poor execution plan. So it needs to be tested if it would be useful to have a temp table or a permanent table instead...
April 22, 2010 at 6:57 am
I made most of those changes that you suggested.
Attached are the execution plans.
Here are the timings.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitType'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 18 ms.
Table 'PWDPermitType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitStatus'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 20 ms.
Table 'PWDPermitDeviceType'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 20 ms.
Table 'PWDPermitDeviceType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitDeviceType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'sysschobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 219 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 55 ms.
Table 'Worktable'. Scan count 2, logical reads 1323444, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PWDPermitConversionData2'. Scan count 3, logical reads 315233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PermitsInserted'. Scan count 0, logical reads 1079, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PWDPermitConversionErrors2'. Scan count 0, logical reads 1078, physical reads 0, read-ahead reads 0, lob logical reads 1073, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6798 ms, elapsed time = 3470 ms.
(1073 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitConversionErrors2'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1323444, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PWDPermitConversionData2'. Scan count 3, logical reads 315233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PermitsInserted'. Scan count 0, logical reads 969183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PWDPermit2'. Scan count 0, logical reads 978755, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31439 ms, elapsed time = 18821 ms.
(966672 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitConversionData2'. Scan count 1, logical reads 1096477, physical reads 0, read-ahead reads 0, lob logical reads 12385890, lob physical reads 75376, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 1664467, physical reads 200, read-ahead reads 0, lob logical reads 1073, lob physical reads 0, lob read-ahead reads 0.
Table 'PermitsInserted'. Scan count 1, logical reads 2518, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 28828 ms, elapsed time = 71554 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
(562995 row(s) affected)
April 22, 2010 at 7:38 am
How can the elapsed time be shorter than the CPU time?
Viewing 15 posts - 76 through 90 (of 112 total)
You must be logged in to reply to this topic. Login to reply