Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DML Usage in SQL 2008

DML Usage in SQL 2008

DML Usage in SQL 2008

In this thread i need to discuss about the usage of DML commands in SQL 2008.

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
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

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.



RE: DML Usage in SQL 2008

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

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.

Join Us             Close