RE: Aliases across a sub-select

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)

RE: Aliases across a sub-select

Author

Topic

John Obelenus
Starting Member

10 Posts

Posted - 2004-06-15 : 12:37:21

Hello.. i've got an interesting question...

Can I use column aliases across a sub-select statement... like SELECT COL AS A, (SELECT COL AS B WHERE B>A), B-A...

basically if I am unable to do this, I will be forced to use a cursor, something i don't necessarily want to do...

for my concrete example:

SELECT BUTTON.BUTTONNAME, BUTTON.CLICK AS BC, BUTTON.SESSIONID AS SID,
(SELECT TOP 1 BUTTON.CLICK AS NC VROM BUTTON WHERE BUTTON.SESSIONID=SID AND BUTTON.CLICK > BC ORDER BY BUTTON.CLICK DESC), DATEDIFF(ss, BC, NC)
FROM BUTTON INNER JOIN INIT ON BUTTON.SESSIONID=INIT.SESSIONID
WHERE BUTTON.BUTTONNAME = 'button1' AND INIT.TPID='0'

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-15 : 12:42:41

Why don't you just alias the table instead??

I think it would solve your problem.

and... Oh!!! never threaten me with a cursor

Corey

John Obelenus
Starting Member

10 Posts

Posted - 2004-06-15 : 12:51:25

yes... i was advised as such by a friend

i remember a professor telling me... in all his years of being a DBA he never needed to write a SELECT in a FROM statement for a table...

:) i've got my first... and I'm close... here is what I've got so far

SELECT BUTTON.BUTTONNAME, BUTTON.CLICK AS BC, SUM(DATEDIFF(ss, BUTTON.CLICK, BUTTON2.CLICK)) FROM BUTTON INNER JOIN INIT ON BUTTON.SESSIONID=INIT.SESSIONID INNER JOIN (SELECT TOP 1 CLICK, SESSIONID FROM BUTTON WHERE CLICK > BC ORDER BY BUTTON.CLICK DESC) BUTTON2 ON INIT.SESSIONID=BUTTON2.SESSIONID
WHERE BUTTON.BUTTONNAME='button1' AND INIT.TPID=0 AND EXITPAGE=0 GROUP BY BUTTON.BUTTONNAME

and the error is:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'BC'

soooooooooooooooooooooo close...

John Obelenus
Starting Member

10 Posts

Posted - 2004-06-15 : 12:59:18

UPDATE:

SELECT BUTTON.BUTTONNAME, BUTTON.CLICK AS BC, (DATEDIFF(ss, BUTTON.CLICK, BUTTON2.CLICK)) FROM BUTTON INNER JOIN INIT ON BUTTON.SESSIONID=INIT.SESSIONID INNER JOIN (SELECT TOP 100 PERCENT CLICK, SESSIONID FROM BUTTON ORDER BY BUTTON.CLICK DESC) BUTTON2 ON INIT.SESSIONID=BUTTON2.SESSIONID
WHERE BUTTON.BUTTONNAME='button1' AND INIT.TPID=0 AND EXITPAGE=0 AND BUTTON2.CLICK > BUTTON.CLICK

BUT:
the big problem (and reason i was trying to do it in there is) because I only want the first record of the sub-select that has a date higher than the inital record... i don't want all dates higher

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-15 : 12:59:31

I might have it... i aliased the table to 'button1':

SELECT
Button1.BUTTONNAME,
Button1.CLICK AS BC,
SUM(DATEDIFF(ss, Button1.CLICK, BUTTON2.CLICK))
FROM BUTTON as Button1
INNER JOIN INIT
ON BUTTON.SESSIONID=INIT.SESSIONID
INNER JOIN
(
SELECT TOP 1
CLICK,
SESSIONID
FROM BUTTON
WHERE CLICK > Button1.CLICK
ORDER BY BUTTON.CLICK DESC
) as Button2
ON INIT.SESSIONID=BUTTON2.SESSIONID
WHERE BUTTON1.BUTTONNAME='button1'
AND INIT.TPID=0
AND EXITPAGE=0
GROUP BY BUTTON1.BUTTONNAME

Corey

John Obelenus
Starting Member

10 Posts

Posted - 2004-06-15 : 13:20:01

I'm trying it... but... I can't bring aliases from outside a sub-select, inside the sub-select

John Obelenus
Starting Member

10 Posts

Posted - 2004-06-15 : 14:09:02

welllllll... in the goal of productivity im going to make a cursor to do it...

BUT i will be back here to see if we can make it cursor-less...

so keep pondering

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-15 : 14:20:33

One more try... I started over

Select
ButtonName,
Click,
clickDelay = sum(clickDelay)
From
(
Select
B1.ButtonName,
B1.Click,
clickDelay = DateDiff(ss,B1.Click,(Select max(Click) From button as B2 Where SessionId = B1.SessionId and Click > B1.Click))
From Button as B1
Inner Join Init
On B1.SessionId = Init.SessionId
Where B1.ButtonName = 'button1'
AND Init.TPID=0
AND ExitPage=0
) as detail
Group By ButtonName, Click

Corey

John Obelenus
Starting Member

10 Posts

Posted - 2004-06-15 : 14:54:12

WELL.... how about that... very close.. only a minor change.. but damn good...

SELECT BUTTONNAME, SUM(CLICKDELAY)/COUNT(BUTTONNAME)
FROM (
SELECT B1.BUTTONNAME, B1.CLICK, CLICKDELAY=DATEDIFF(ss, B1.CLICK,
(SELECT TOP 1 CLICK FROM BUTTON B2 WHERE SESSIONID=B1.SESSIONID AND B2.CLICK>B1.CLICK ORDER BY CLICK ASC)
FROM BUTTON B1 INNER JOIN INIT ON B1.SESSIONID=INIT.SESSIONID
WHERE INIT.TPID=0 AND EXITPAGE=0) DETAIL
GROUP BY BUTTONNAME

John Obelenus
Starting Member

10 Posts

Posted - 2004-06-15 : 14:55:36

thank you so very much

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-15 : 15:00:48

No problem... I KNEW a cursor wasn't the answer...

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources