problem with calculated column in a view

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)

problem with calculated column in a view

Author

Topic

alinka345
Starting Member

4 Posts

Posted - 2005-06-13 : 09:48:08

Hello,

I am very new to SQL so plz bare with me. I am working in Micrisoft SQL Server. I wrote a view...but it's executing with errors, because some of my alias columns are calculated early in the view, and later on the in view, I am trying to call those columns to do another calculation...For example, notice the CASE WHEN THEN statement for QUANTITY COLUMN, where I use the "Amount_Ind" field to do calcuations. Then later on in the view, I try to use the Quantity column to do another calculation for another column. Because the Quantity column was an alias based on calculations, the 2nd part of the query gives me an error "invalid column name"...Could you please help? I was told this will probably need to be a stored procedure...but since I'm new to this, I have no idea how to put it into stored procedure...

Here's the code:

SELECT Departments.Department, DESKS.Desk_ID, DESKS.Description, Trades.TicketNumber, Trades.Ticker, Trades.Cusip,
Trades.Trace, Trades.BuySell,
'Quantity' =
CASE
WHEN [Amount_Ind] = 0 THEN ([Amount])
WHEN [Amount_Ind] = 1 THEN ([Amount]/10)
WHEN [Amount_Ind] = 2 THEN ([Amount]/100)
WHEN [Amount_Ind] = 3 THEN ([Amount]/1000)
WHEN [Amount_Ind] = 4 THEN ([Amount]/10000)
WHEN [Amount_Ind] = 5 THEN ([Amount]/100000)
WHEN [Amount_Ind] = 6 THEN ([Amount]/1000000)
ELSE 'Please Check'
END,
'Price2' =
CASE
WHEN [Price_Ind]= 0 THEN [Price]
WHEN [Price_Ind]= 1 THEN ([Price]/10)
WHEN [Price_Ind]= 2 THEN ([Price]/100)
WHEN [Price_Ind]= 3 THEN ([Price]/1000)
WHEN [Price_Ind]= 4 THEN ([Price]/10000)
WHEN [Price_Ind]= 5 THEN ([Price]/100000)
WHEN [Price_Ind]= 6 THEN ([Price]/1000000)
WHEN [Price_Ind]= 7 THEN ([Price]/10000000)
WHEN [Price_Ind]= 8 THEN ([Price]/10000000)
ELSE 99999
END,
[Date] AS Date2, Trades.Time, Trades.AsOfTime, [AsOfDate] AS AsOfDate2,
Trades.CustomerAccount, Trades.DFA_BrokerCode, Trades.Status, Trades.ConfirmNumber, Trades.RejectMessage,
Trades.RecType, TRADE_TYPE.Description,
'[Canceled Trade Fee]' =
CASE
WHEN Trades.[RecType] = 'XTT' THEN 1.5
ELSE 0 END,
'[Corrections]' =
CASE
WHEN Trades.[RecType] = 'CTT' THEN 1.5
ELSE 0 END,
'[Late As Of Rpt Fee]' =
CASE
WHEN Trades.[RecType] = 'PXT' THEN 3
WHEN Trades.[RecType] = 'PCT' THEN 3
WHEN Trades.[Date] > Trades.[AsOfDate] THEN 3
ELSE 0 END,

'[Trade Report Fee]' =
CASE
WHEN [Quantity]<201000 THEN 0.475
WHEN [Quantity]<1000000 THEN ([Quantity]*0.000002375)
ELSE 2.375 END,
[Canceled Trade Fee]+[Corrections]+[Late As Of Rpt Fee]+[Trade Report Fee] AS [Total Trace Fee]
FROM Departments
RIGHT JOIN
(TRADE_TYPE
INNER JOIN
(DESKS
RIGHT JOIN Trades
ON DESKS.Bloomber_Desk_ID = Trades.Trader)
ON TRADE_TYPE.RecType = Trades.RecType)
ON Departments.Department = DESKS.Department

Kristen
Test

22859 Posts

Posted - 2005-06-13 : 10:17:30

I don't think you can reference columns from earlier in the SELECT list by their aliases (except in an ORDER BY clause) - so to do what you are after you'd have to repeat the "Quantity" logical all over again.

Can you define column aliases using single quotes? I didn't know that, but I would advise against it - use [MyAlias] type brackets instead.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-13 : 11:13:51

This is a common question; it seems to pop up about once per month.

To write this:


Select 
  <something> as A, <any expression involving A> as B
from 
  tbl

you simply do this:


select 
  A, <any expression involving A> as B
from
  (select <something> as A from tbl) tmp

- Jeff

alinka345
Starting Member

4 Posts

Posted - 2005-06-15 : 17:28:25

Hi jsmith8858,

I am very confused at yorresponse. Please keep in mind that I'm very new to SQL or any coding. I was told that I need to put every thing into stored procedure and declare a table variable in my stored procedure and join to that. Can someone help me write that? I need real instructions as again, I am new to SQL. Any help is appreciated greatly!!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-15 : 18:19:51

quote:

I was told that I need to put every thing into stored procedure and declare a table variable in my stored procedure and join to that.

It sounds like you need to talk to the person who told you to do this to find out what they actually mean.

And to put this into a stored procedure, you just do this:

CREATE PROC PutSprocNameHere
AS

YourQueryGoesHere

Take a look at CREATE PROC in SQL Server Books Online for additional information about creating stored procedures.

Tara

alinka345
Starting Member

4 Posts

Posted - 2005-06-15 : 20:04:00

Why is it that I can't get a normal response with helpful step by step instruction? I thought that's what this forum is for. I've been told so on the forum and I have searched for help for a few days now. I am a complete newbie to this and have never programmed in my life...please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-15 : 20:06:25

I don't understand what you don't understand in my post. I showed you how to put your query in a stored procedure. What part didn't you understand about that? I'll post it again though in case you missed it:

CREATE PROC PutSprocNameHere
AS

YourQueryGoesHere

Tara

alinka345
Starting Member

4 Posts

Posted - 2005-06-15 : 20:20:28

Tara,

This syntax you give is easy, but my original question was that the code that I created so far is WRONG - it's giving an invalid column error. Please see my original post above. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-15 : 20:22:36

Could you post the exact error?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-15 : 21:18:25

Again:

in a select, you cannot refer to an expression's alias. If you say

SELECT 1+2 as X

you cannot say:

SELECT 1+2 as X, X+1 as Y

because X is not fully defined yet. That is essentially the problem you are having. You need to do this in two steps: step 1, define X, and then in step 2, you can use X.

To write a SELECT in two steps, you essentially SELECT from another SELECT. In the FROM clause, you can replace a table name with another SELECT statement as long as you give is an alias:

select tmp.*
from
(select 1+2 as X) tmp

Take a minute or two to dissect the above statement and get a feel for it. notice that the FROM clause is another select statement. This is called a Derived table - it is not a real table, just a temporary virtual set of rows/columns (just like a table) produced on the fly from another SELECT.

The key is that in the outer select, the expression X is fully defined now. So, we can do this:

SELECT tmp.*, tmp.x+1 as Y
from
(select 1+2 as x) tmp

Can you apply this to your situation?

Don't get mad if we are trying to point you in the right direction and have you learn some new concepts and to able to apply the solution on your own, That is by far the best way to learn.

- Jeff

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-16 : 00:35:07

alinka345,

You shouldn't complain about any help that you get or don't get on these kinds of discussion forums. Do you remember how much money you paid to get onto this forum? That's right: zero. Do you know how much money the people who post answers here get to dispense professional advice that has taken years to accumulate? That's right again: zero. The most that the people on this forum or any other similar forum owe you is to not be rude or insulting. Other than that, just be happy that there is a small group of busy professionals (who are not professors, teachers, or tutors) that gladly take time out of their workdays to give out FREE advice. Try finding a doctor or lawyer who would do the same. Therefore, since what you are receiving is FREE, and since most of those who post advice here obtained their knowledge through years of reading, schooling, trial and error, and making their own mistakes, don't be surprised if they point you towards literature that you should read yourself, because they had to learn it the same way. Normally, the cliche "you get what you pay for" holds true. Here, you get a lot more than what you pay for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources