Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Variables in Derived Tables


Variables in Derived Tables

Author
Message
jim.powers
jim.powers
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1343 Visits: 839
I have the following derived table (from a much larger SELECT statement):

SELECT t.HPROPERTY AS HPROP,
CASE WHEN t.dtleasefrom BETWEEN s.begdate AND s.enddate AND t.dtrenewdate IS NULL
AND ISNULL(p.dtapply, t.dtsigndate) IN (SELECT *
FROM dbo.GetCalendarTable(@Season,553))
AND ISNULL(l.renewal_transfer, 'No') <> 'Yes'
THEN 'New Leases'
WHEN t.dtleaseto > s.enddate AND t.dtleasefrom <= s.enddate AND t.dtrenewdate
IS NOT NULL AND t.dtrenewdate IN (SELECT *
FROM dbo.GetCalendarTable(@Season,553))
THEN 'Renewals'
.
.
.


You can see I'm calling a function called GetCalendarTable. I have to pass in the season we are projecting for as well as the property identifier. The Season is easy. I have it defined before I even start into the select. The property is the hard part. For testing purposes, I'm using it as follows (hard coding the property code):

SELECT *
FROM dbo.GetCalendarTable(@Season,553)


What I need to do when I call the function is:

SELECT *
FROM dbo.GetCalendarTable(@Season,@Property)


Each time this derived table runs, it needs to run the function for the particular property it is working on at the time. The only way I know of to set the value of the @Property variable in a SELECT statement is by doing something that apparently doesn't work in a derived table:

SELECT @Property = t.HPROPERTY AS HPROP,
.
.
.


Any other ideas would be greatly appreciated.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
Look up Apply, specifically Cross Apply, in Books Online. That should do what you need.

With that, you can use a value in the main query/table as an input parameter for a function.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
jim.powers
jim.powers
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1343 Visits: 839
Well, I have looked at the documentation but I can't make heads or tails of how a cross apply would work in this particular case. If you look at the first code snippet, you'll see how I'm using the results from the function - in the WHEN part of the CASE statement. Incidentally, the first field of my SELECT statement (t.HPROPERTY) is what I need to pass to the function, replacing the 553 hard coded in the function call.
jim.powers
jim.powers
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1343 Visits: 839
Well, this isn't exactly what I wanted to do since it doesn't seem to be as clean as I would like but...

I took the statement from the function and put it directly in my SELECT as a Correlated Subquery:

SELECT t.HPROPERTY AS HPROP,
CASE WHEN t.dtleasefrom BETWEEN s.begdate AND s.enddate AND t.dtrenewdate IS NULL
AND ISNULL(p.dtapply, t.dtsigndate) IN
(SELECT CalendarDate
FROM (SELECT CONVERT(SMALLDATETIME, CONVERT(VARCHAR(4), YEAR(DATEADD(yy, -1, begdate)))
+ '0901') +
N3.NUM * 100 +
N2.NUM * 10 +
N1.NUM AS CalendarDate,
begdate
FROM (SELECT 0 AS NUM UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) AS N1,
(SELECT 0 AS NUM UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) AS N2,
(SELECT 0 AS NUM UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS N3,
SEASONS
WHERE (hprop = t.HPROPERTY AND shortdesc = @Season)) GenCalendar
WHERE CalendarDate < CONVERT(SMALLDATETIME, CONVERT(VARCHAR(4), YEAR(begdate)) + '0901')
)
AND ISNULL(l.renewal_transfer, 'No') <> 'Yes'
THEN 'New Leases'
WHEN t.dtleaseto > s.enddate AND t.dtleasefrom <= s.enddate AND t.dtrenewdate
IS NOT NULL AND t.dtrenewdate IN (SELECT *
FROM dbo.GetCalendarTable(@Season,553))
THEN 'Renewals'


This allows me to use the outer table's t.HPROPERTY value as I need.

Of course, if someone knows how to make the other way work, I'm all eyes.
Tomm Carr
Tomm Carr
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 787
I think that one of the methods you could use to make this code a lot easier to work with is to write out just exactly what it is doing in plain English statements. For example:
CASE WHEN t.dtleasefrom BETWEEN s.begdate AND s.enddate AND t.dtrenewdate IS NULL
AND ISNULL(p.dtapply, t.dtsigndate) IN ...

could be written "when the lease date lies within the time period specified by the begin and end date, the renewal date is null and the apply date (or the sign date if apply date is null) is within the same fiscal year as the begin date..."

One of the advantages of doing that is you should see immediately that to check if a date is within the same year as another date, there is no reason to generate a list of 365 sequential dates and use the IN operator. Just generate the date of the first day of the target fiscal year and the date of the first day of the next fiscal year and use BETWEEN.

Provide an English explanation of every parameter and column used in your query and I'm sure you will be able to very much simplify the query. Post it here and we will help, if you wish, but just the effort of doing it will allow you to answer many of your own questions.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search