Update a query - 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

General SQL Server Forums

New to SQL Server Programming

Update a query

Author

Topic

vavs
Starting Member

24 Posts

Posted - 2010-08-31 : 10:20:50

I have a procedure that queries a table. I need to update the query based on a case statement. I am having some issues. I keep getting an error 170 when I check the syntax of the procedure. Here is the code:
/*
CREATE PROCEDURE usp_TMIINV

AS

declare @DecIn Int
declare @DecOut Int
Set @DecIn = 1

Select TAG#, dbo.FRINFM.GRADE,Gauge, Width, Weight,Cast(Weight/2000.00 as Float) as Tons,Status,Cast([Cost-Cwt] as Decimal(4,2)) as [Cost-Cwt],dbo.tblGrades.Type as GaugeType, Space(2) as Type, Cast(0.00 as float)as DecIn,
Cast(0.00 as float) as DecOut, Cast(0.00 as float) as DecAvg, Space(6) as GaugeFinal, Cast(Weight*[Cost-Cwt]/100 as money) as InvValue
From dbo.FRINFM
Join dbo.tblGrades
On dbo.FRINFM.GRADE = dbo.tblGrades.Grade

Select Case
When charindex('/',Gauge) > 0 then

DecIn = (substring(Gauge,1,(charindex('/',Gauge)-1))) as DecIn
Else
End
Where (Status=3) or (Status = 4)

GO
*/

I originally want to update both the DecIn and the DecOut fields. The DecAvg field needs to be calculated from this point. Also to make this even more complex, I need to have this whole thing create a new table that I can update with a final variable.

dmckinney
Starting Member

3 Posts

Posted - 2010-09-01 : 04:41:42

Your case syntax is completely wrong...take a look in Books online. SQL <> VB!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 05:55:27

I think you need to give us a bit more info.

What's the purpose of the stored proc? Your syntax for the CASE statement is completely wrong. What are the variables for? (@decIN, @devOut) -- you haven't done anything. with them.

I've reformatted you sql a little and moved the case to where it would make sense to me. Don't think we can help more without a description of what you want from the procedure. Some sample data and expected result would be good too.



CREATE PROCEDURE usp_TMIINV

AS

DECLARE @DecIn INT
DECLARE @DecOut INT
SET @DecIn = 1

SELECT
	-- TAG# /*What is this for? Is this a column in one of the tables*/
	, dbo.FRINFM.GRADE
	, Gauge
	, Width
	, Weight
	, CAST(Weight/2000.00 AS FLOAT) AS Tons
	, Status
	, CAST([Cost-Cwt] AS DECIMAL(4,2)) AS [Cost-Cwt]
	, dbo.tblGrades.Type AS GaugeType
	, SPACE(2) AS TYPE
	, CAST(0.00 AS FLOAT) AS DecIn
	, CAST(0.00 AS FLOAT) AS DecOut
	, CAST(0.00 AS FLOAT) AS DecAvg
	, SPACE(6) AS GaugeFinal
	, CAST(Weight*[Cost-Cwt]/100 AS MONEY) AS InvValue 

	, CASE
		WHEN CHARINDEX('/',Gauge) > 0 THEN (SUBSTRING(Gauge,1,(CHARINDEX('/',Gauge)-1)))
		ELSE ''
	END AS DecIn

FROM
	dbo.FRINFM
	JOIN dbo.tblGrades ON dbo.FRINFM.GRADE = dbo.tblGrades.Grade
WHERE
	[status] IN (3, 4)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vavs
Starting Member

24 Posts

Posted - 2010-09-01 : 11:33:02

Charlie,

Thank you thank you thank you. This is exactly what I was looking to do. I have two questions and an answer. First the answer to the TAG# question. TAG# is a field in the FRINFM table. It is like a part #.

Question 1. Should I create a new table in this procedure or have a table out there that gets updated with the new information. Whichever way, I need to create views in SQL to allow my users to link to the data.

Question 2. There is a field DecAvg which is a calculated field. The calculation is (DecIn + DecOut)/2. I tried to write that into the select statement but I am gathering this is the wrong spot. Can you direct me to the right place to put in a calculated field?

This is going to make a huge difference in how my company gets data. The processing power has just increase 50 fold.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 12:07:37

Hi vavs,

Can you explain where DecIn and DecOut come from?

As far as I can see DecIn is either 0.00 (as a float) or its a a value derived from GAUGE in some way.

I think you need to provide sample data and required output now before we can help more. I'm not sure how much of what I posted works for you or not.

Can you post some sample tables and some sample data? It doesn't have to be much, just enough to model your real data.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vavs
Starting Member

24 Posts

Posted - 2010-09-01 : 12:27:53

Charlie,
DecIn and DecOut are derived from the Gauge field. The problem I have is the Gauge field is a text field. In the case sample the Gauge field had .019/.021 in it. What I was doing was trying to take the characters to the left of the / and put them in DecIn, then take the characters to the right of the / and put them in DecOut. In order to get DecAvg, I need to change the characters to numbers and average the two. That is why I am looking to do a calculated field.

BTW I found out how to create the table easily in SQL. I used this syntax

select * into tableA
from tableB
where 1=2

If you want me to provide data the structure of the important fields is
Gauge char(30)
DecIn Decimal(1,3)
DecOut Decimal(1,3)
DecAvg Decimal(1,3)

Gauge can take many forms. I will just use the .019/.021 as type 1. It can take any one of 15 variations. I will deal with the complexity of that when I code the procedure.
Sample results
Gauge DecIn DecOut DecAvg
.019/.021 .019 .021 .020
.010/.020 .010 .020 .015
.005/.005 .005 .005 .005
.020/.018 .020 .018 .019

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 12:36:07

well first off -- you shouldn't create a new base table in your procedure. The next time you run it it will fail as the table all-ready exists.

I'd use a derived table to generate your DecIn and DecOut and then an outer query to generate the average. Here's a really simple example


SELECT
	innerT.[Id] AS [Id]
	, (innerT.[decIn] + innerT.[decOut]) / 2 AS [average]
FROM
	(
		  SELECT 1 AS [ID], CAST(0 AS FLOAT) AS [decIn], CAST(9 AS FLOAT) AS [decOut]
	UNION SELECT 2 AS [ID], CAST(23.3 AS FLOAT) AS [decIn], CAST(2 AS FLOAT) AS [decOut]
	)
	AS innerT

Except you would have you existing query as the inner query (the derived table) to generate the decOut and decIn fields.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources