Calculation in View - 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

Transact-SQL (2000)

Calculation in View

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2004-11-03 : 10:49:09

Does anybody can help to me in this view?
I want another column where I will have "CalcDiscTax"-((("CalcDiscTax")* "Discounted")/100)

CREATE VIEW dbo.[Taxable Discounted Items]
AS
SELECT dbo.arhPOSInvoiceLineitems.Price,
dbo.arhPOSInvoiceLineitems.Taxable,
dbo.arhPOSInvoiceLineitems.[Ship Date],
dbo.arhPOSInvoice.Comments,
SUBSTRING(dbo.arhPOSInvoice.Comments, 10, 2)
AS Discounted, dbo.arhPOSInvoice.[Invoice Number],
dbo.arhPOSInvoiceLineitems.Price * 0.07 AS CalcDiscTax
FROM dbo.arhPOSInvoice INNER JOIN
dbo.arhPOSInvoiceLineitems ON
dbo.arhPOSInvoice.[Invoice Number] = dbo.arhPOSInvoiceLineitems.[Invoice Number]
WHERE (dbo.arhPOSInvoiceLineitems.Taxable = 1) AND
(dbo.arhPOSInvoice.Comments IS NOT NULL)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 10:56:41

as you probably have noticed, you cannot do this:

select 1+1 as A, A+1 as B

Because A has not been previous defined, so it cannot be used to calculate B.

But you CAN do this:

select A, A+1 as B
from
(select 1+1 as A) tmp

Since you have defined A clearly in a previous layer in the query. Does this help give you your answer so you can figure it out?

- Jeff

ilimax
Posting Yak Master

164 Posts

Posted - 2004-11-11 : 13:54:44

jsmith8858, this does not work ...

****************************************************

I have join here which confuse me too ...
So I have;

Select Table1.A,Table2.A, BB, BB+1 as C,Table2.D,Table2.E from
(Select SUBSTRING(Table1.B,10,2) As BB FROM Table1) from Table1 INNER JOIN Table2
ON Table1.A=Table2.A
Where Table1.B is not null And Table2.D=1

************************************

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 14:08:45

you have incorrect syntax, and you are not returning everything you need from table1.

step by step:

#1.

quote:

Select Table1.A,Table2.A, BB, BB+1 as C,Table2.D,Table2.E from
(Select SUBSTRING(Table1.B,10,2) As BB FROM Table1) from Table1 INNER JOIN Table2
ON Table1.A=Table2.A
Where Table1.B is not null And Table2.D=1

note the highlighted part -- you are only returning your substring expression from Table1, not the column "A" as well. So how can the outer query know what "A" is ? it's not being returned. Also, how can the JOIN work on column "A" if it is not being returned?

#2.

quote:

Select Table1.A,Table2.A, BB, BB+1 as C,Table2.D,Table2.E from
(Select SUBSTRING(Table1.B,10,2) As BB FROM Table1) from Table1 INNER JOIN Table2
ON Table1.A=Table2.A
Where Table1.B is not null And Table2.D=1

that is incorrect syntax -- look again at the example I gave you. You've already specific what to pull your results from -- the subquery -- but then you have a second FROM in your select. You need to remove that. But, again, looking at my example, note that I have the subquery an alias of "tmp" -- otherwise there is no way to refer to the contents of the subquery because it has no name! (it is NOT the same as table1)

Fixing all of that (and adding indents so you can see what is going on easier), you get:


Select 
   tmp.A, Table2.A, tmp.BB, tmp.BB+1 as C,Table2.D,Table2.E 
from
  (Select A,  SUBSTRING(Table1.B,10,2) As BB 
   FROM Table1) from Table1 tmp
INNER JOIN 
  Table2 ON Table1tmp.A=Table2.A
Where 
  Table1tmp.B is not null And Table2.D=1

Does this make sense?

- Jeff

ilimax
Posting Yak Master

164 Posts

Posted - 2004-11-11 : 15:37:07

Thanks,

You are right man,looks fine now. Unfortunatelly I have "Server Time Out" message so I do not get resault.
Subquereis are always slow and I was thinking that could be bad, but I tried.My table has about 2,000,000 records.

However, thanks for your help.

Ilimax

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 15:40:03

do you have any indexes on your table? how many rows are you trying to return?

- Jeff

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-11 : 16:10:42

Jeff
Looking at his WHERE statement would indexes make any difference anyway?? Im interested to know your thoughts as I would say no
I would hazard a guess that taxable is a bit

WHERE (dbo.arhPOSInvoiceLineitems.Taxable = 1) AND
(dbo.arhPOSInvoice.Comments IS NOT NULL)

Andy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 16:13:41

i missed the original query, but that leads to the second half of my question -- how many rows is he trying to return here? all 2,000,000 in a single SELECT ?

Also, if there is a lack of indexes/primary keys, that will affect the JOIN between the tables, of course.

- Jeff

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-11 : 16:25:28

Ah of course I didnt even look at the JOIN

Thanks Jeff

ilimax
Posting Yak Master

164 Posts

Posted - 2004-11-11 : 16:58:20

I did not see ... there are many replays ... thanks ...

Actually I do not have indexes on these 2 columns where I have "Where" Clause....One is "Bit" and second one is "ntext", so I can not make indexes on these 2 fields. I have index in both table on join field.

Unfortunatelly I have to pull all data where "Where" clause has True, because I can not send parameter to View. If I can send parameter to View so I will pull data by date. I alrady have queries in Access for making my reports, but I was thinking I will increase the speed with making this view on the Server.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-11 : 20:30:32

Have you run this via QA & looked at the execution plan

What kind of results are you looking at, because from your WHERE clause you could be looking at least 1,000,000 (bit 1 or 0, Comments NULL or it isnt - its 50/50), even if these columns where indexed i very much doubt the optimiser would use them.

Is there not a date field that could be used (invoice date/period) so can be run for set days/months/years. Or is this a one off hit that can be done overnight & will never/hardly be repeated

Andy

ilimax
Posting Yak Master

164 Posts

Posted - 2004-11-15 : 12:55:35

quote:
Originally posted by AndyB13

Have you run this via QA & looked at the execution plan

What kind of results are you looking at, because from your WHERE clause you could be looking at least 1,000,000 (bit 1 or 0, Comments NULL or it isnt - its 50/50), even if these columns where indexed i very much doubt the optimiser would use them.

Is there not a date field that could be used (invoice date/period) so can be run for set days/months/years. Or is this a one off hit that can be done overnight & will never/hardly be repeated

Andy

I can not send parameter to veiw ...am I right? Parameter for date will be in query later on ... "Select bla bla from Veiw where date between ..."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-15 : 14:37:34

if you write a view, and assume the client will filter as needed, don't base your performance on

SELECT * FROM YourView

base it on filtering as the clients typically would. For example, if the view will often be filtered on columns A, B, and C, ensure that the base tables have indexes on those columns and test the performance of things like this:

SELECT * FROM YourView WHERE ColA = 12

I have written views in the past that will time out if you just SELECT from them, making them seem extremely inefficient. but that is because they combine so much data and are written so geenrcialyl that they don't filter very much -- it is up to the client to do it. so, if you just try to open one of those views w/o a condition, they will never run and people might think "what kind of crap is this????". But, then, when they use the views properly -- filtering for a reasonable date range, for example -- they return data in a few seconds.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-15 : 15:37:47

quote:
Originally posted by jsmith8858

and people might think "what kind of crap is this????".

Say it ain't so! Do people really say that about your code?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-15 : 16:00:30

I'm sure at my last job every day some one looks at code I've written and says "what the heck.....???"

It doesn't mean that it's crap (though I'm sure in some cases it is), it often means they don't understand it.

You know, they might think to themselves thoughts like "Why the hell does he have a table of Numbers in his database!" or "Doesn't this guy know you never should use a CROSS JOIN !"

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-15 : 16:21:04

lol...

gotta admit I was floored the first time I saw the table of numbers thing...

"How Convenient"*

*C.L.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources