DML Usage in SQL 2008
DML Usage in SQL 2008
(OP)
In this thread i need to discuss about the usage of DML commands in SQL 2008.
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS Contact USThanks. We have received your request and will respond promptly. Come Join Us!Are you a
Computer / IT professional? Join Tek-Tips Forums!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines |
|
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Register now while it's still free!
Already a member? Close this window and log in.
RE: DML Usage in SQL 2008
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
RE: DML Usage in SQL 2008
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
RE: DML Usage in SQL 2008
Thanks, once again.