Taking apart my results - SQL Server Forums

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

SQL Server Development (2000)

Taking apart my results

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-02-09 : 08:42:46

We have a SQL table that is used to collect automated alarms from our manufacturing computers. When I run a query the results would look something like this:

Select ALM_NATIVETIMELAST, ALM_DESCR
From no1pm.tbl_1pmAlarm
Where bla bla

ALM_NATIVETIMELAST ALM_DESCR
2/9/2005 6:55:53 [PM1 ] Fix32.PM1.KIS_PID310_SP.F_CV set to .20 by PM1::1PM OPERATOR

2/9/2005 6:55:47 [PM1 ] Fix32.PM1.DSR_PID300_SP.F_CV set to .36 by PM1::1PM OPERATOR

2/9/2005 6:55:21 [PM1 ] Fix32.PM1.WET_DRY_STR_CALC_AM.F_CV set to 1 by FULPC200::SYSTEM ADMINISTRATOR

What I would like to do is make this data more descriptive. Unfortunately I have no way of controlling the way the data is inserted in the table since it is from a preprogrammed software. So I am forced to try and pick apart the results.

I would like to see it displayed something like:

ALM_NATIVETIMELAST ALM_DESCR

2/9/2005 6:55:53 Kymene flow set point set to .20 by OPERATOR

Any thoughts on how I would accomplish this? I think I would have to set up an alias for each word for exampe "KIS_PID310_SP" actually means "Kymene flow set point".

I don't have any experience doing this.

Any help you can provide would be great!

Your limits are only as far as you set your boundries....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-09 : 08:54:05

Try this

Select Replace(ALM_NATIVETIMELAST,'KIS_PID310_SP','Kymene flow set point'), ALM_DESCR
From no1pm.tbl_1pmAlarm
Where bla bla

Madhivanan

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-02-09 : 10:34:29

That worked awesome. But of course now I want to do more than one replace. The BOL only shows an example with one replace. What would be the syntax for multiple Select Replace statements?

For example:
KIS_PID310_SP will become Kymene flow set point
but I also want
DSR_PID300_SP to read Resin flow set point

I tried:

Select ALM_NATIVETIMELAST As [Date/Time],
Replace(ALM_DESCR,'KIS_PID310_SP','Kymene Flow Set Point'),
Replace(ALM_DESCR,'DSR_PID300_SP','Resin Flow Set Point') as [Description]

But it seemed to replace ALL results with KIS_PID310_SP or DSR_PID300_SP with Kymene Flow Set Point.

Thanks!!

Your limits are only as far as you set your boundries....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 10:40:28

maybe you should create a new table that has the names (KIS_PID310_SP, DSR_PID300_SP) for keys and descriptions
and the join to that table on names.
probably the best way to go about this...

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

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-02-09 : 11:05:31

k. I see where your going with this. It makes sense. The problem is how would I do the join? The data in this ALARM table under ALM_DESCR is in this format:

[PM1 ] Fix32.PM1.KIS_PID310_SP.F_CV set to .20 by PM1::1PM OPERATOR

The table I create would have a tag name column and description description column.

For example:

Table Name: tbl_1PMiFIXTagNames

iFIXTagNames iFIXTagDescriptions
KIS_PID310_SP Kymene Flow Set Point
DSR_PID300_SP Resin Flow Set Point

I would need a way to JOIN and compare iFIXTagNames column with ALM_DESCR column. The data would not be exact. Can you do a JOIN using a LIKE statement ??

the produces NULL values:

Select a.ALM_NATIVETIMELAST As [Date/Time], e.iFIXTagDescriptions as [Description]

From no1pm.tbl_1pmAlarm a
LEFT JOIN tbl_1PMiFIXTagNames e
ON (a.ALM_DESCR = e.iFIXTagNames)

Thanks!

Your limits are only as far as you set your boundries....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 11:09:22

you can do a join like this:

t1 join t2 on left(t1.col1, 5) = t2.col1
or
t1 join t2 on t2.col1 like t1.col1 + '%'

probably should give you some ideas, no?

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

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-09 : 11:34:05

Just noticed somthing, what about parsename


Eg:
SELECT PARSENAME(LEFT('Fix32.PM1.KIS_PID310_SP.F_CV set to .20 by PM1::1PM OPERATOR',CHARINDEX(' ','Fix32.PM1.KIS_PID310_SP.F_CV set to .20 by PM1::1PM OPERATOR')),2)

Or 
t1 JOIN t2 ON PARSENAME(LEFT(t1.Col1,CHARINDEX(' ',t1.Col1)),2) = t2.Col1

Andy

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-02-09 : 15:15:05

I'm still a bit confused. I'm not understanding parts of the JOIN you have shown here. Plus I did try running it and got back no results. Here is what I ended with:

Select a.ALM_NATIVETIMELAST As [Date/Time], e.iFIXTagDescriptions as [Description]

From no1pm.tbl_1pmAlarm a
JOIN tbl_1PMiFIXTagNames e
ON LEFT(a.ALM_DESCR, 5) = e.iFIXTagNames

Where (ALM_DESCR Like '%KIS_PID310_SP%' OR
ALM_DESCR Like '%DSR_PID300_SP%' OR
ALM_DESCR Like '%MACH_SPD%' OR
ALM_DESCR Like '%SHEET_WIDTH_INCH%' OR
ALM_DESCR Like '%BASIS_WT%' OR
ALM_DESCR Like '%dry_STR_LBS_PERTON%' OR
ALM_DESCR Like '%WET_STR_LBS_PERTON%' OR
ALM_DESCR Like '%WET_STR_LBS_PER_GAL%' OR
ALM_DESCR Like '%WET_STR_SOLIDS%' OR
ALM_DESCR Like '%DRY_STR_LBS_PER_GAL%' OR
ALM_DESCR Like '%DRY_STR_SOLIDS%' OR
ALM_DESCR like '%WET_DRY_STR_CALC_AM%')

AND ALM_MSGTYPE = 'Operator'

AND (ALM_NATIVETIMELAST >= '2/8/2005 10:21:24' AND
ALM_NATIVETIMELAST <= '2/9/2005 10:21:24' )

Order by ALM_NATIVETIMELAST DESC

I did populate the iFIXTagNames Table with the data shown above and the appropiate descriptions. What is the '5' for in the JOIN? Is that a character space? Sorry for not following this but I am hoping someone can help me understand it.

Regarding the Parsename; if I were to use that option I would need it to reference all the tags names listed in the WHERE clause not just one or two. Thats why I was hoping there was a way to compare. Having known that, do you think a ParseName could still be used? Or should I say would be practical?

Thanks.

Your limits are only as far as you set your boundries....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 15:21:40

5 is the part of the left function:
it returns first 5 chars of the value passed into the function.
select left('123456789', 5)
returns '12345'
that was just my example, you'll have to see on what length you'll join. if it's variable use
charindex or patindex to get the correct length. look those functions in BOL.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-10 : 01:25:52

quote:
Originally posted by jpiscit1

That worked awesome. But of course now I want to do more than one replace. The BOL only shows an example with one replace. What would be the syntax for multiple Select Replace statements?

For example:
KIS_PID310_SP will become Kymene flow set point
but I also want
DSR_PID300_SP to read Resin flow set point

I tried:

Select ALM_NATIVETIMELAST As [Date/Time],
Replace(ALM_DESCR,'KIS_PID310_SP','Kymene Flow Set Point'),
Replace(ALM_DESCR,'DSR_PID300_SP','Resin Flow Set Point') as [Description]

But it seemed to replace ALL results with KIS_PID310_SP or DSR_PID300_SP with Kymene Flow Set Point.

Thanks!!

Your limits are only as far as you set your boundries....

Try this

Select ALM_NATIVETIMELAST As [Date/Time],
Replace(Replace(ALM_DESCR,'KIS_PID310_SP','Kymene Flow Set Point'),
'DSR_PID300_SP','Resin Flow Set Point') as [Description]

Madhivanan

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-10 : 03:34:39

Here's an example using parsename


CREATE TABLE #Alarms (ALM_NATIVETIMELAST varchar(100), ALM_DESCR varchar(100))
INSERT #Alarms (ALM_NATIVETIMELAST, ALM_DESCR)
SELECT '2/9/2005 6:55:53 [PM1 ]', 'Fix32.PM1.KIS_PID310_SP.F_CV set to .20 by PM1::1PM OPERATOR' UNION
SELECT '2/9/2005 6:55:47 [PM1 ]', 'Fix32.PM1.DSR_PID300_SP.F_CV set to .36 by PM1::1PM OPERATOR' UNION 
SELECT '2/9/2005 6:55:21 [PM1 ]', 'Fix32.PM1.WET_DRY_STR_CALC_AM.F_CV set to 1 by FULPC200::SYSTEM ADMINISTRATOR'

CREATE TABLE #AlarmCodes (Code varchar(100), [Description] varchar(100))
INSERT #AlarmCodes (Code, [Description])
SELECT 'KIS_PID310_SP', 'Kymene flow set point' UNION
SELECT 'DSR_PID300_SP', 'Resin flow set point' UNION
SELECT 'WET_DRY_STR_CALC_AM', 'Whatever this is'

SELECT a.ALM_NATIVETIMELAST, c.Code, c.[Description], a.ALM_DESCR
FROM #Alarms a INNER JOIN #AlarmCodes c
	ON PARSENAME(LEFT(a.ALM_DESCR,CHARINDEX(' ',a.ALM_DESCR)),2) = c.Code

DROP TABLE #Alarms
DROP TABLE #AlarmCodes

I only suggested parsename as it has 4 parts (well 5 but the last one is after the space) to the string eg - part4.part3.part2.part1 if your data is always guaranteed that it is like that then this will work.

Andy

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-02-10 : 12:36:13

Excellent! You guys were a tremendous help here. I actually like all the ideas listed and was able to recreate most of your suggestions. However, because this is a production environment and they want things done IMMEDIATELY, I picked the simplest approach. I think I will revisit this in the near future and standardize on the alternate table and/or the PARSENAME choice. Thanks again guys!!!

Here's what I ended with for now:

CREATE PROCEDURE [no1pm].[wetdrySPHist] @StartDate datetime, @Enddate datetime

AS

--This procedure is specific to the Wet and Dry Strenth Calculations page. The alarms are searched and renamed to
--something that can be interpreted by the operator through the SELECT REPLACE command..

Select ALM_NATIVETIMELAST As [Date/Time],
Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
Replace(ALM_DESCR,'[PM1 ] Fix32.PM1.KIS_PID310_SP.F_CV','Kymene Flow Set Point'),
'[PM1 ] Fix32.PM1.DSR_PID300_SP.F_CV','Resin Flow Set Point'),
'[PM1 ] Fix32.PM1.WET_DRY_STR_CALC_AM.F_CV set to 0', 'Wet/Dry Calc set to Manual'),
'[PM1 ] Fix32.PM1.WET_DRY_STR_CALC_AM.F_CV set to 1', 'Wet/Dry Calc set to Auto'),
'[PM1 ] Fix32.PM1.dry_STR_LBS_PERTON.F_CV', 'Dry Strength lbs/ton'),
'[PM1 ] Fix32.PM1.WET_STR_LBS_PERTON.F_CV', 'Wet Strength lbs/ton'),
'[PM1 ] Fix32.PM1.BASIS_WT.F_CV' , 'Manual Basis Weight'),
'[PM1 ] Fix32.PM1.SHEET_WIDTH_INCH.F_CV', 'Manual Sheet Width'),
'[PM1 ] Fix32.PM1.MACH_SPD.F_CV', 'Manual Machine Speed')as [Description]

From
no1pm.tbl_1pmAlarm

Where (ALM_DESCR Like '%KIS_PID310_SP%' OR
ALM_DESCR Like '%DSR_PID300_SP%' OR
ALM_DESCR Like '%MACH_SPD%' OR
ALM_DESCR Like '%SHEET_WIDTH_INCH%' OR
ALM_DESCR Like '%BASIS_WT%' OR
ALM_DESCR Like '%dry_STR_LBS_PERTON%' OR
ALM_DESCR Like '%WET_STR_LBS_PERTON%' OR

ALM_DESCR Like '%WET_STR_LBS_PER_GAL%' OR
ALM_DESCR Like '%WET_STR_SOLIDS%' OR
ALM_DESCR Like '%DRY_STR_LBS_PER_GAL%' OR
ALM_DESCR Like '%DRY_STR_SOLIDS%' OR
ALM_DESCR like '%WET_DRY_STR_CALC_AM%' )
AND ALM_MSGTYPE = 'Operator' AND
(ALM_NATIVETIMELAST >= @StartDate AND
ALM_NATIVETIMELAST <= @Enddate )

Order by ALM_NATIVETIMELAST DESC
GO

Your limits are only as far as you set your boundries....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-10 : 12:41:42

now that's a lot of replaces