Using Local Variables - 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)

Using Local Variables

Author

Topic

augustin_p
Starting Member

21 Posts

Posted - 2002-04-11 : 00:37:35

Im using something like below

select UName, (PF + HRA - DA) AS TOTAL , ADDRESS FROM USER

what do i need to do if i want to use the alias name 'total' in some other calculation in the same query. do i have to use '(PF + HRA - DA) ' again.

Also, how do i get a serial no with in the query results like below (in a single query):

SlNo Name
1 Mr.X
2 Mr. Y

Nazim
A custom title

1408 Posts

Posted - 2002-04-11 : 00:54:39

Yes, you have to reuse (pf+hra-DA) again . when you alias it on a column it basically acts as a label rather then as a Alias in Sql Server(the only place you car reuse the alias is in order by).

quote:

what do i need to do if i want to use the alias name 'total' in some other calculation in the same query. do i have to use '(PF + HRA - DA) ' again.

you can use this Reader's Challenge II logic to come up with serial no.
Here we call it the Rank Operator

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-11 : 00:56:30

Augustin_P,

You will have to use '(PF + HRA - DA) ' again.

May I suggest that you make a simply view first and then you can use the "Total" column easily.

Makes it very easy to read as well as giving you the luxury to change the underlying view calculation.

HTH

DavidM

Tomorrow is the same day as Today was the day before.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-11 : 03:54:20

You might be able to move such calculations into a subquery on the FROM (a derived table in BOL parlance). Because the query optimizer in SQL Server works on the query globally rather than in blocks, this shouldn't dent the performance. This example is rather fatuous since I can't think of anything concrete right now:

SELECT 36+w+x*y*z, a + (36+w+x*y*z), b + (36+w+x*y*z), c + (36+w+x*y*z)
FROM Table
ORDER BY 36+w+x*y*z

to

SELECT doodah, a+doodah, b+doodah, c+doodah
FROM (SELECT 36+w+x*y*z AS doodah, a, b, c FROM Table) t
ORDER BY doodah

Edited by - Arnold Fribble on 04/11/2002 03:56:36

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-11 : 04:27:15

quote:

Here we call it the Rank Operator

Rank is one of the undocumented operators that SQL Server 2000 can generate as part of an execution plan. However, it doesn't get generated here... in fact, I've never seen it generated in a context where it is obviously useful. Here's a Northwind example where it clearly isn't useful:


SELECT *
FROM Orders a
WHERE OrderID = (
    SELECT MAX(OrderID)
    FROM Orders b
    WHERE a.CustomerID = b.CustomerID
      AND OrderDate = (
        SELECT MAX(OrderDate)
        FROM Orders c
        WHERE a.CustomerID = c.CustomerID) -- oops, I meant b, not a!
  )

The fact that this generates a different plan from when the commented line is changed to WHERE b.CustomerID = c.CustomerID), despite the fact that a.CustomerID and b.CustomerID must be equal is probably an indication of some deficiency in the unification logic:


SELECT *
FROM Orders a
WHERE OrderID = (
    SELECT MAX(OrderID)
    FROM Orders b
    WHERE a.CustomerID = b.CustomerID
      AND OrderDate = (
        SELECT MAX(OrderDate)
        FROM Orders c
        WHERE b.CustomerID = c.CustomerID)
  )

But it doesn't really explain why the rank operator is getting inserted into the plan. Putting both conditions in, the plan is the same as for the first query, but the rank operator has mysteriously disappeared.


SELECT *
FROM Orders a
WHERE OrderID = (
    SELECT MAX(OrderID)
    FROM Orders b
    WHERE a.CustomerID = b.CustomerID
      AND OrderDate = (
        SELECT MAX(OrderDate)
        FROM Orders c
        WHERE a.CustomerID = c.CustomerID
          AND b.CustomerID = c.CustomerID)
  )

All very odd.

Edited by - Arnold Fribble on 04/11/2002 04:32:09

augustin_p
Starting Member

21 Posts

Posted - 2002-04-11 : 04:51:47

<quote>
you can use this Reader's Challenge II logic to come up with serial no.
Here we call it the Rank Operator

SELECT SlNo=Count(*), P.name
FROM Users P
INNER JOIN Users P1
ON P.Name >= P1.Name
GROUP BY P.name
ORDER BY 1
--------------------------------------------------------------

Edited by - Nazim on 04/11/2002 01:05:49
[/quote]

Nazim,

Thanks for your post. I tried the SlNo logic you have posted. I had 37 records. The numbering was fine till 31. After that, the numbers werent sequential. i have posted the query results below. What might be the problem?

SlNo MaterialDescription
----------- --------------------------------------------------
1 8REPORT TESTING - JAYASEELAN
2 asfdsadfdasf
3 awdas
4 Belts
5 BORIC ACID
6 BUSH FOR SPRING P.NO.86 [EH-II]
7 Hydrogen nitrate
8 jayaseelan's 21-03-2002
9 mat2000
10 Material02
11 Material04
12 Material08
13 Material10
14 Material12
15 Material13
16 Material14
17 material2002
18 materialelect
19 materialpan
20 nothing jayas
21 one
22 others material
23 pankajam
24 panks2002
25 Pepsi
26 Prasanna
27 PRASANNA DONT WORRY
28 Prasanna2
29 prasannaA002
30 Scrap Material
31 Superb - Working Fine: Prasanna Says
32 tesing - 22march
36 Testing Issue Dept
37 TESTING JAYASEELAN
38 Testing Materail
39 ultrajas
105 testing

Thanks,
prasanna

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-11 : 05:19:33

Presumably, MaterialDescription isn't unique -- you seem to have a lot called "testing", for example. If you change the Count(*) to Count(DISTINCT P1.MaterialDescription) it will rank the descriptions. If the rows in the table are what you want to rank, change the join condition to the primary key.

Also, be aware that this sort of query doesn't scale very well in SQL Server -- it will take O(n^2) time. If you have 1000s of MaterialDescription's this is going to be one slow query! If you want to do this on a big table, you're better off creating the ranking as an identity value in a temporary table. Compare the performance of these two on Northwind's Orders table, which only has 830 rows!


SELECT o.ShipName, COUNT(DISTINCT o1.ShipName) AS rank
FROM Orders o
INNER JOIN Orders o1 ON o.ShipName >= o1.ShipName
GROUP BY o.ShipName
ORDER BY o.ShipName


SELECT ShipName, IDENTITY(int, 1, 1) AS rank
INTO #ShipRank
FROM Orders
GROUP BY ShipName
ORDER BY ShipName

SELECT *
FROM #ShipRank
ORDER BY rank

DROP TABLE #ShipRank

Edit: Eek! This doesn't always work!! INSERT INTO seems to perform the getidentity before any sorting, so the values from the IDENTITY function reflect the order that the rows were retreived instead of the sort order. Create the temporary by hand and use an INSERT.


CREATE TABLE #ShipRank (ShipName nvarchar(40), rank int IDENTITY(1,1))

INSERT INTO #ShipRank (ShipName)
SELECT ShipName
FROM Orders
GROUP BY ShipName
ORDER BY ShipName

SELECT *
FROM #ShipRank
ORDER BY rank

DROP TABLE #ShipRank

Edited by - Arnold Fribble on 04/11/2002 06:37:44

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources