you might get a better response in forum183: Microsoft SQL Server: Programming

and you will ~definitely~ get a better response if you actually asked a question



 

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

hi,

   I need to retrive data from a two tables joining each other. The tables has fields like state name, YYYYMM, HPI_12M_change and others. There is one column called Period no which has period numbers like -155 to 259. Here 259 is maximum period and The period will be updated monthly when we process new data. hence the period would be 260 next time. I need to retrive the data for the periods ranging between 193 and maximim period. I dont want to specify the maximum period as 259 instead i want mantion as Max(Period_no).Because i need to give this sample data to marketing folks every month after we process the data. Since the data volume is huge, i want to automate it by writing some stored procedure, instead of writing query manually everytime. I tried to write a query as mentioned below....

Select distinct G.state_name, S.YYYYMM, S.HPI_12M_Change from REAS_HPI_STATE_Final S, REAS_STATE_BE G
where S.Tier_Code in(11)
and G.STATE_Code=S.State_Code
and S.period_no between 193 and MAX(Period_No)

When i tried above, i got the error message saying ....

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

i understood the error and thought of defining some variable for Min and Max period number and supply it in he begining of SP execution. I tried this way...

alter procedure Test1 @MIN_Period as integer='', @MAX_Period as integer=''
as Begin
Begin
select distinct G.state_name, S.YYYYMM, S.HPI_1M_Change from REAS_HPI_STATE_Final S, REAS_STATE_BE G
where S.Tier_Code in(11)
and S.period_no between @MIN_Period and @MAX_Period
and G.STATE_Code=S.State_Code
order by S.YYYYMM
end
end;

after that i tried to create a view for this query and use bcp command to generate some .xls files. As we know, view doesnt allow us to declare any variables.

My whole intention is, i have to generate few data files by executing BCP command where the query would be stored in view.
I suspect that, i may be wrong with my writing the query(1st) or do i need to something else. Please advice on this.

Thanks,
VJ

 

Thanks r937, i have opened a new thread in 'forum183: Microsoft SQL Server: Programming: Microsoft SQL Server: Programming' and posted my query. Hopefully i will get solution for my problem soon.

Thanks, once again.