File Squishing, String Theory, and Fickle Miss SQA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

File Squishing, String Theory, and Fickle Miss SQA

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-04-12 : 23:19:47

Please attend to my tale of woe.....

1.) I'm attempting to squish (watch your step, please!) a huge Error Log that was passed to me. As you may surmise, I'm trying to glean just the necessary information.

2.) The gleaning involves performing some updates with string a few times then stuffing the entire turkey into a fresh table.

3.) Problem is that Fickle Miss SQA (The Query Analyzer) won't process my updates. In fact, she won't go past where I've marked "imaginary SQA stoppage"

4.) I usually test processes in SQA prior to assemblage within the DTS package.

5.) If you see a better way to do this, please don't hesitate to offer up your suggestion!

sample data:

INSERT ERR_LOG Value ('ScamCO', '0001369', 'Payor=ScamCo, PCPNum/Name=0001369/Fudd, Elmer not found in PROVPLAN')
INSERT ERR_LOG Value ('Sporxx', '0009999', 'Payor=Sporxx, PCPNum/Name=0009999/Duck, Daffy not found in PROVPLAN')
INSERT ERR_LOG Value ('Blurgg', '0888880', 'Payor=Blurgg, PCPNum/Name=0888880/Vark, Aard not found in PROVPLAN')
INSERT ERR_LOG Value ('Yechh!', '0000002', 'Payor=Yechh!, PCPNum/Name=0000002/Glopp, Dudley not found in PROVPLAN')
INSERT ERR_LOG Value ('ScamCO', '4444469', 'Benefit Plan not found in BENEFITS')

--- tab_Err_Log_X is the dBase5 file receptor
if exists
(select * from Xerxes.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_ErrLog_X]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [Xerxes].[dbo].[tab_ErrLog_X]

create table [Xerxes].[dbo].[tab_ErrLog_X] (
[Payor] varchar (06) NULL,
[MemberNum] varchar (15) NULL,
[ErrMsg] varchar (100) NULL
)
GO
---Load ErrLog_X from the dBase5 creature
INSERT INTO tab_ErrLog_X
(Payor, MemberNum, ErrMsg)
SELECT Payor, MemberNum, ErrMsg
FROM Err_Log
GROUP BY Payor, MemberNum, ErrMsg
ORDER BY Payor, MemberNum, ErrMsg
;
--- the Working file
if exists
(select * from Xerxes.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_NewProcess_ErrLog_Work]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [Xerxes].[dbo].[tab_NewProcess_ErrLog_Work]

create table [Xerxes].[dbo].[tab_NewProcess_ErrLog_Work] (
[Payor] nvarchar (06) NULL,
[ErrMsg] nvarchar (100) NULL,
[PCPNum] nvarchar (80) NULL,
[PCPName] nvarchar (80) NULL,
[KEEP] nvarchar (80) NULL,
[KEEP2] nvarchar (80) NULL,
[KEEP3] nvarchar (80) NULL,
[CountOfErrMsg] float NULL,
[MsgType] nvarchar (8)
)
GO
---the new output file
if exists
(select * from Xerxes.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_NewProcess_ErrLog]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [Xerxes].[dbo].[tab_NewProcess_ErrLog]

create table [Xerxes].[dbo].[tab_NewProcess_ErrLog] (
[Payor] nvarchar (06) NULL,
[PCPNum] nvarchar (80) NULL,
[PCPName] nvarchar (80) NULL,
[CountOfErrMsg] float NULL,
[MsgType] nvarchar (8) NULL
)
;
SELECT Payor, ErrMsg,Count(ErrMsg) AS CountOfErrMsg
FROM tab_ErrLog_X
GROUP BY Payor,ErrMsg
ORDER BY CountOfErrMsg DESC
;
----------------imaginary SQA stoppage here----------------
GO
SELECT * FROM [Xerxes].[dbo].[tab_NewProcess_ErrLog_Work]
GO
---Error type determination
update [Xerxes].[dbo].[tab_NewProcess_ErrLog_Work]
set MsgType =
case
when CHARINDEX('PROVPLAN',(ltrim(ErrMsg))) <> 0 then 'PROVPLAN'
when CHARINDEX('PLANTYPE',(ltrim(ErrMsg))) <> 0 then 'PLANTYPE'
when CHARINDEX('BENEFITS',(ltrim(ErrMsg))) <> 0 then 'BENEFITS'
else 'HUH?'
end

SELECT PCPNum = ' ', PCPName = ' ', KEEP = ' '
;
GO
---String Theory: You cannot fly a kite without one
UPDATE tab_NewProcess_ErrLog_Work
SET KEEP = substring([ErrMsg],charindex('Name=',[ErrMsg])+5,len([ErrMsg]) - charindex('Name=',[ErrMsg]))
WHERE MsgType = 'PROVPLAN'
GO
UPDATE tab_NewProcess_ErrLog_Work
SET PCPName = substring([KEEP],charindex('/', [KEEP])+1,abs(charindex('not',[KEEP]) - charindex('/',[KEEP])-1))
WHERE MsgType = 'PROVPLAN'
GO
UPDATE tab_NewProcess_ErrLog_Work
SET PCPNum = rtrim(ltrim(left([KEEP], (abs(charindex ('/', [KEEP])))- 1)))
WHERE MsgType = 'PROVPLAN'
GO
---Bob the Builder: can we fix it? I hope to heck we can!
INSERT INTO tab_NewProcess_ErrLog
(Payor, PCPNum, PCPName, CountOfErrMsg, MsgType)
SELECT Payor, PCPNum, PCPName, CountOfErrMsg, MsgType
FROM tab_NewProcess_ErrLog_Work
GROUP BY Payor, PCPName, PCPNum, CountOfErrMsg, MsgType
ORDER BY Payor, PCPName, PCPNum, CountOfErrMsg, MsgType
;

Any help would be greatly appreciated!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-13 : 02:33:53

do you get any errors?

Go with the flow & have fun! Else fight the flow

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-13 : 03:04:02

How big is this table - tab_NewProcess_ErrLog_Work??
How long does this take on its own in QA??

SELECT * FROM [Xerxes].[dbo].[tab_NewProcess_ErrLog_Work]

It could be that the table is that big that it is waiting to complete the SELECT

As Mladen asked - did you get any errors?

Andy

Beauty is in the eyes of the beerholder

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-04-13 : 18:44:59

Andy & Mladen. My answer: "Nope. No errors." Do I have enough GO's? Is there a better way to write what I wrote? tab_NewProcess_ErrLog_Work has about 19K records.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-14 : 07:14:59

you had any luck with this? other than sql taking some time to complete i don't see any reason why would it stop...
when it stops does it stop the whole script execution or does it keep running and running and running... ??
there are enogh go's to go around.

Go with the flow & have fun! Else fight the flow

bbondi
Starting Member

20 Posts

Posted - 2005-04-14 : 18:13:24

Hi,
I'm not a SQL expert by a long shot, but your problem sounds familiar. I have some queries I'm working with that never finish on my PC. I have just 256 Meg of RAM, I suspect that as the problem. When I add a where condition that severly restricts the output it works fine!
I put a like condition in the where clause:
where CommDemo.status not in ('C','D') and CommDemo.Demo_Id like '%007'

Good luck,
Bob

Do the Social Engineering teams need QA? Naw, let 'em have fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources