April 19, 2002 at 9:07 am
Dears,
I have a query in SQL, in which there is many sub-queries. For example, suppose I have the following sub-query:
(select max(trade_date) from tblbudget where code=dbo.company.code) AS T1,
(select Max(traces) from tblbudget where code=dbo.company.code and trade_date=(select max(trade_date) from tblbudget where code=dbo.company.code)) AS MAX_TRACE
As you see, I have to use T1 subquesry in other sub-queries, again and again. I want to know if there is any way to use the returned result of T1 in sub-queries, and not the equivalent amont of it. In this way, I tried to use
(select Max(traces) from tblbudget where code=dbo.company.code and trade_date=T1) AS MAX_TRACE
But it failed.
What can I do? As you see, as I have to use T1 in subsequent subdirectories, this will step down the overall speed.
Any comments with be greatly appreciated,
Mohammed
April 19, 2002 at 9:28 am
Try to build a query string (@sqltext) and then execute it thru the EXEC (@sqltext) or SP_EXECUTESQL @sqltext
April 19, 2002 at 1:42 pm
Dear ezadoyen@ssd.com,
Thanks for your reply. Please as I'm a new SQL user, please explain more about your answer.
And, is it possible to use such a variable (@SQLtext) WITHIN the main query (and not outside of it)?
Thanks again,
Mohammed
April 19, 2002 at 3:05 pm
For the @SQLText part:
If you using the same value over and over again create a variable to hold this value:
declare @Maxdate as datetime,
@SQLText as varchar(3000)
set @Maxdate = (select max(trade_date) from tblbudget where code=dbo.company.code)
select @SqlText = 'select Max(traces) from tblbudget where code=dbo.company.code and trade_date = ' + @Maxdate
SP_EXECUTESQL @SqlText
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply