Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
DML Usage in SQL 2008
- Thread starter VIJSQL01
- Start date
- Thread starter
- #3
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
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
- Thread starter
- #4
- Status
Similar threads
- Locked
- Question