Code Help Please

I did not write this stored procedure but have been asked to edit the code and display new data. The column name is H.BilletUOM. It only contains only three values. B71,B72, and B73. I was able to select the column in the stored procedure easily so the data is available for the report but the next step is where I am having difficulty. If the value is B71 then I want to display 28ft. B72 display 34ft. B73 display 40ft. Can someone please take a look at this code and try to help me figure this out. I highlighted the H.BilletUOM in red so you can quickly see where I am selecting this column. Any assistance is greatly appreciated. I am new to SQL and have not started classes yet. Thanks again.

CREATE PROCEDURE SP_Melt_HeatReport_HeatList
@.HeatNo varchar(50) = NULL,
@.Date varchar(50) = NULL
AS

UPDATE WebPageData
SET Hits = Hits + 1
WHERE SPName = 'SP_Melt_HeatReport_HeatList'

DECLARE @.Site varchar(100) SET @.Site = dbo.UDF_SystemParameter('Site')
DECLARE @.SiteName varchar(100) SET @.SiteName = dbo.UDF_SystemParameter('SiteName')

DECLARE @.MinHeatNo int SET @.MinHeatNo = CSCMelting.dbo.UDF_SystemParameter('MinClevelandHeat')
DECLARE @.MaxHeatNo int SET @.MaxHeatNo = CSCMelting.dbo.UDF_SystemParameter('MaxClevelandHeat')

DECLARE @.HeatsPerPage int SET @.HeatsPerPage = 40

IF @.Date IS NOT NULL
BEGIN
SELECT @.MinHeatNo = MIN(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @.Date AND HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo

SELECT @.MaxHeatNo = MAX(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @.Date AND HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo

SELECT @.HeatNo = @.MinHeatNo
SELECT @.HeatsPerPage = (@.MaxHeatNo - @.MinHeatNo) / 10 + 1
END

IF PATINDEX('%,%', @.HeatNo) > 0
SET @.HeatNo = SUBSTRING(@.HeatNo ,PATINDEX('%,%', @.HeatNo) + 1, 100)

DECLARE @.ColorRed varchar(7) SET @.ColorRed = '#FF0000'
DECLARE @.ColorRedDark varchar(7) SET @.ColorRedDark = '#AA0000'
DECLARE @.ColorBlue varchar(7) SET @.ColorBlue = '#0000FF'
DECLARE @.ColorGreen varchar(7) SET @.ColorGreen = '#008800'
DECLARE @.ColorYellow varchar(7) SET @.ColorYellow = '#999900'
DECLARE @.ColorWhite varchar(7) SET @.ColorWhite = '#FFFFFF'
DECLARE @.ColorBlack varchar(7) SET @.ColorBlack = '#000000'
DECLARE @.ColorGray varchar(7) SET @.ColorGray = '#eeeeee'

IF @.HeatNo IS NULL OR @.HeatNo = '' OR ISNUMERIC(@.HeatNo) = 0 OR @.HeatNo = '0'
SELECT @.HeatNo = MAX(HeatNo) - (@.HeatsPerPage * 10) + 10
FROM CSCMelting.dbo.MS_HeatLog
WHERE HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo

SELECT Tag = 1, Parent = NULL,
[Title!1!Title1] = 'Charter Steel ' + @.SiteName + ' Melting',
[Title!1!Title2] = 'LEVEL II HEAT REPORTS'
FOR XML EXPLICIT

SELECT Tag = 1, Parent = NULL,
[PageVars!1!Site] = @.Site,
[PageVars!1!WebPageID] = 20500,
[PageVars!1!HeatNo] = @.HeatNo,
[PageVars!1!HeatNoNext] = @.HeatNo + (@.HeatsPerPage * 10) ,
[PageVars!1!HeatNoPrev] = @.HeatNo - (@.HeatsPerPage * 10) ,
[PageVars!1!HeatNoLast] = 0,
[PageVars!1!HomeLink] = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20500'
FOR XML EXPLICIT

SELECT HeatNo,
Job = JOB_NUMBER,
RowColor = CASE WHEN (Heatno /10) % 2 = 0 THEN @.ColorGray ELSE @.ColorWhite END,
Grade = CONVERT(varchar(20), GRADE + ' ' + CS_CMDESC),
Status = CASE Status WHEN 4 THEN
CASE WHEN ChemCert = 1 THEN 'Certified'
WHEN EXISTS(SELECT * FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo AND Location = '99') THEN 'Inspection'
WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN 'CCM'
ELSE 'Inspected'
END
WHEN 3 THEN 'CCM'
WHEN 2 THEN 'LRF'
WHEN 1 THEN 'EAF'
ELSE ''
END, --+ CASE WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN ' (PB)' ELSE '' END,
H.NonConforming,
PourBackTons = (SELECT MIN(PourBackAmt) / 2000 FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo),
Location = (SELECT MAX(Location) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntCounted = H.BilletsMade1 + H.BilletsMade2 + H.BilletsMade3 + H.BilletsMade4,
ScrapCount = (SELECT ScrapCount FROM CSCMelting.dbo.VW_Prod_ScrapBilletsByHeat Sc WHERE Sc.Heatno = H.HeatNo),
CntInv = (SELECT COUNT(*) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntRolled = (SELECT SUM(Quantity) FROM CSCRolling.dbo._Lot WHERE Heat = H.HeatNo),
CntSched = (SELECT SUM(IC_QUANTITY) FROM CSCRolling.dbo._Lot_Scheduled WHERE IC_LOT_NUMBER = H.HeatNo),

CCM.SEQ_COUNTER,
SeqHeat = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER AND CCM2.REPORT_COUNTER <= R.REPORT_COUNTER), --CCM.SEQ_HEAT_COUNTER,
SeqTotal = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER),

RptDate = CONVERT(varchar(10), RptDate, 120),
EndTapTime = H.EndTapTime,
H.BilletUOM,

ConsumptionsLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20501&HeatNo=' + CONVERT(varchar(20), HeatNo),
FCERepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 400),0),
FCERepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20100&HeatNo=' + CONVERT(varchar(20), HeatNo),
-- VADRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
-- VADRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),

LRFRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
LRFRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),

VODRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 800),0),
VODRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),

CCMRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 1100),0),
CCMRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20300&HeatNo=' + CONVERT(varchar(20), HeatNo),
RunoutOrderLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20301&HeatNo=' + CONVERT(varchar(20), HeatNo)

INTO #Heats
FROM CSCMelting.dbo.MS_HeatLog H
LEFT JOIN CSCMelting.dbo.REPORTS R ON R.HEAT_ID = H.HeatNo AND R.AREA_ID = 1100
LEFT JOIN CSCMelting.dbo.REP_CCM CCM ON CCM.REPORT_COUNTER = R.REPORT_COUNTER
WHERE HeatNo >= @.HeatNo AND HeatNo < @.HeatNo + (@.HeatsPerPage * 10) AND HeatNo % 10 = 0

SELECT *,
EndTapTimeStr = CONVERT(varchar(25), EndTapTime, 100),
MissChemStr = CASE WHEN NonConforming = 1 THEN 'Y' ELSE '' END,
PourBackTonsStr = CASE WHEN PourBackTons = 0 THEN '' ELSE CONVERT(varchar(10), PourBackTons) + 'tn' END,
CntCountedStr = NULLIF(CntCounted, 0),
ScrapCountStr = NULLIF(ScrapCount, 0),
CntInvStr = NULLIF(CntInv, 0),
CntRolledStr = NULLIF(CntRolled, 0),
CntSchedStr = NULLIF(CntSched, 0),
HeatSeqStr = '' + char(CONVERT(int, SEQ_COUNTER) % 26 + 65) + ' - ' + CASE WHEN SeqTotal <= 1 THEN 'single' ELSE CONVERT(varchar(2), SeqHeat) + ' of ' + CONVERT(varchar(2), SeqTotal) END,
StatusColor = CASE
WHEN PourBackTons > 0 THEN @.ColorBlack
WHEN Status = 'EAF' THEN @.ColorBlue
WHEN Status = 'LRF' THEN @.ColorGreen
WHEN Status = 'CCM' THEN @.ColorYellow
WHEN Status = 'Inspection' THEN @.ColorRedDark
WHEN Status = 'Inspected' THEN @.ColorRed
WHEN Status = 'Certified' THEN @.ColorBlack
ELSE ''
END
FROM #Heats Heats
ORDER BY HeatNo
FOR XML AUTO

print @.HeatNo + (@.HeatsPerPage * 10) + 10

DROP TABLE #Heats
GO

Look at the "status" field above your billetUOM field. It's using a case statement. You'll want to perform similar stuff against your billetUOM field.

http://msdn2.microsoft.com/en-us/library/ms181765.aspx|||

I came up with this code and it still is still giving me syntax errors. Could you please take a look and see what I am doing wrong? Thank you very much for any help.

H.BilletUOM = CASE H.BilletUOM WHEN B71 THEN '28ft.'
WHEN B72 THEN '34ft.'
WHEN B73 THEN '40ft.'
ELSE ''
END,

|||I think you need to put your criteria in quotes as well.

WHEN 'B71' THEN...
WHEN 'B72' THEN...
etc..

coalesce

Hi,
How can i display a integer value to it's description i a lookuptable? I did
something like this, but i still got the integer value.
select coalesce(myIntColumn, (select MyIntDescription from descriptions)
from payments
What is wrong with this?First of all, a parenth. is missing..

> select coalesce(myIntColumn, (select MyIntDescription from descriptions))
> from payments
Second, you should know that you are only able to get back ONE Value. If
dont know if your query mentioned above can handle this, i think it will
return more than one value.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jason" <jasonlewis@.hotrmail.com> schrieb im Newsbeitrag
news:ug%235OHlRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can i display a integer value to it's description i a lookuptable? I
> did
> something like this, but i still got the integer value.
> select coalesce(myIntColumn, (select MyIntDescription from descriptions)
> from payments
>
> What is wrong with this?
>|||coalesce isnt designed for what you aare trying to do ( check BOL )
why not just join to your lookup table ? e.g.
select MYINTDESCRIPTION from PAYMENTS join DESCRIPTIONS on
PAYMENTS.MYINTCOLUMN = DESCRIPTIONS.INTCOLUMN
I would assume that the integer value in "descriptions" table is unique, so
put a clustered primary key on that...|||I assume you want a JOIN here:
SELECT P.myIntColumn, D.myIntDescription
FROM Payments AS P
INNER JOIN Descriptions AS D
ON P.myIntColumn = D.myIntColumn
David Portas
SQL Server MVP
--|||Jason
Use Northwind
select
coalesce(( select orderid from orders where orderid = 10248),
(select top 1 orderid from [order details]
where orderid = 10249
)
)
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ug%235OHlRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can i display a integer value to it's description i a lookuptable? I
did
> something like this, but i still got the integer value.
> select coalesce(myIntColumn, (select MyIntDescription from descriptions)
> from payments
>
> What is wrong with this?
>

Clustered Index Scan Operator has like a warning sign

When i did a "Display Execution Plan", I noticed there was a yellow
triangular sign with an exclamation mark for the Clustered Index Scan
Operator. Why is that so ?
So this is what i see
select * from table1 where date1 = '2007-11-10 11:00' -- This query shows a
clustered Index Scan operator without the warning sign
select * from table1 where date1 > '2007-11-10 11:00' -- This query shows a
clustered Index Scan operator with the warning sign
Do I need to do something to make the warning sign go ?Try updating statistics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:%23jhqgDOSIHA.5264@.TK2MSFTNGP02.phx.gbl...
> When i did a "Display Execution Plan", I noticed there was a yellow
> triangular sign with an exclamation mark for the Clustered Index Scan
> Operator. Why is that so ?
> So this is what i see
> select * from table1 where date1 = '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator without the warning sign
> select * from table1 where date1 > '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator with the warning sign
> Do I need to do something to make the warning sign go ?|||Hi Hassan,
If you hover the mouse cursor over the node with the warning, it should
display an explanation of the warning. Your two queries both look fine - it
will depend on what the actual warning is.
Cheers,
Jim
"Hassan" <hassan@.test.com> wrote in message
news:%23jhqgDOSIHA.5264@.TK2MSFTNGP02.phx.gbl...
> When i did a "Display Execution Plan", I noticed there was a yellow
> triangular sign with an exclamation mark for the Clustered Index Scan
> Operator. Why is that so ?
> So this is what i see
> select * from table1 where date1 = '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator without the warning sign
> select * from table1 where date1 > '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator with the warning sign
> Do I need to do something to make the warning sign go ?

Clustered Index Scan Operator has like a warning sign

When i did a "Display Execution Plan", I noticed there was a yellow
triangular sign with an exclamation mark for the Clustered Index Scan
Operator. Why is that so ?
So this is what i see
select * from table1 where date1 = '2007-11-10 11:00' -- This query shows a
clustered Index Scan operator without the warning sign
select * from table1 where date1 > '2007-11-10 11:00' -- This query shows a
clustered Index Scan operator with the warning sign
Do I need to do something to make the warning sign go ?Try updating statistics.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:%23jhqgDOSIHA.5264@.TK2MSFTNGP02.phx.gbl...
> When i did a "Display Execution Plan", I noticed there was a yellow
> triangular sign with an exclamation mark for the Clustered Index Scan
> Operator. Why is that so ?
> So this is what i see
> select * from table1 where date1 = '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator without the warning sign
> select * from table1 where date1 > '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator with the warning sign
> Do I need to do something to make the warning sign go ?|||Hi Hassan,
If you hover the mouse cursor over the node with the warning, it should
display an explanation of the warning. Your two queries both look fine - it
will depend on what the actual warning is.
Cheers,
Jim
"Hassan" <hassan@.test.com> wrote in message
news:%23jhqgDOSIHA.5264@.TK2MSFTNGP02.phx.gbl...
> When i did a "Display Execution Plan", I noticed there was a yellow
> triangular sign with an exclamation mark for the Clustered Index Scan
> Operator. Why is that so ?
> So this is what i see
> select * from table1 where date1 = '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator without the warning sign
> select * from table1 where date1 > '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator with the warning sign
> Do I need to do something to make the warning sign go ?