| Author |
Topic |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2005-02-08 : 13:11:29
|
| Can you declare a local variable in a view?MBeal |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-02-08 : 13:59:52
|
I don't think so, but what good would it do anyway?Corey "If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain |
 |
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-02-08 : 14:02:43
|
| You can declare a local variable in a cursor.Semper fi, Xerxes, USMC (Ret) |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-02-08 : 14:05:22
|
you and cursors...   (not [sniped]... I'm hunting cursor using yaks  )Corey"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2005-02-08 : 14:05:55
|
| For reporting purposes, I create views all the time. It's an easy way to extract out data from our tables and put them together in a meaningful way for Crystal Reports or ODBC connections for Excel workbooks. Anyway, from time to time I have a need to update the date ranges and it seemed like it would be an easy way to update them if I could declare a variable and update all of them at once without having to do an edit replace to update the values.I haven't used cursors yet, I am interested in doing so but I just haven't had an opportunity.Thanks!MBeal |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-08 : 14:07:15
|
| A view can only contain a single select statement (or more with unions).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-02-08 : 14:10:28
|
quote:
I haven't used cursors yet, I am interested in doing so but I just haven't had an opportunity
If this is true, you should not use them, and should not be interested in doing so!What kind of views are these that you have them dependent on a daterange?I suppose if you could create a permenant table and store the date range in there. then maybe you could join that in the view. Then you would only have one place to update the ranges.You could also do something similar with a udf.I didn't think that views typically employed a specific date range though. It would seem more versatile to not restrict the view, and use parameters in the query or sproc using the view.Corey"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-08 : 14:18:23
|
quote:
Anyway, from time to time I have a need to update the date ranges and it seemed like it would be an easy way to update them if I could declare a variable and update all of them at once without having to do an edit replace to update the values.
??? Update the date ranges? not sure what you mean -- are you updating the Views themselves, or data in tables? if you are updating the views themselves, i.e., by changing dates hard-coded into the where clause, then my advice is: DONT DO THIS! views should not have date ranges and such hard-coded. they should just return data. the clients that use the views should be in charge of filtering or summarizing the data from the views as needed.if you want something that accepts paramters and returns data filtered based on those, use a stored procedure. Sounds like thats really what you might want to learn to start using -- and both crystal and excel can use them (though you may need a little VBA coding to get this done in Excel).As I've said here a few times -- use Views to create generic building blocks for the SQL statements in either clients or stored procs. But don't make them too specific.- Jeff |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2005-02-08 : 14:20:29
|
| Thank you.MBeal |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-02-08 : 14:43:30
|
quote:
Originally posted by Seventhnight
you and cursors... (not [sniped]... I'm hunting cursor using yaks )Corey"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain
Stop it! Uncle! You're killing me with laughter! Semper fi, Xerxes, USMC(Ret.)"If cursors we're nickels, I could park in town--all year!" |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-02-08 : 16:51:36
|
Can someone change Xerxes title to "Cursing Yak Master"  I'll get my coat.................... |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-02-08 : 17:03:09
|
| heheh... I would approve Corey"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-02-08 : 17:13:59
|
| Uh, how would anyone know the 'cursing' referred to cursor not profanity (which we can't use in the Marine Corps anymore anyway)?Hey, I'm in favor of it!Semper fi, Xerxes, USMC(Ret.)----------------------------------------------"If cursors were nickels, I could park in town--all year!" |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-02-08 : 17:22:11
|
| Cursoring Yak MasterCorey"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-02-08 : 17:25:08
|
| OK! Semper fi, Xerxes, USMC(Ret.)----------------------------------------------"If cursors were nickels, I could park in town--all year!" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-08 : 18:41:14
|
| some discipline and you'll be cursor free in no time X Go with the flow & have fun! Else fight the flow |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-02-08 : 20:58:33
|
quote:
Originally posted by spirit1
some discipline and you'll be cursor free in no time X Go with the flow & have fun! Else fight the flow
Thanks, Mladen. hope is eternal!Semper fi, Xerxes, USMC(Ret.)----------------------------------------------Acclaimed Cursoring Yak Master"If cursors were nickels, I could park in town--all year!" |
|
|
|
|
Declare Local Variable