|
If it works then you can only improve the structure of the query otherwise it is an exercise in string concatenation.
If the where clause is the only dynamic piece and the elements are known you can use something like
Where (IsNull(@AttrID, -1) = -1 OR AttrID = @AttrID)
where the default value is null or -1. Caveat, too many of these (about 6 IIRC) MAY affect the query performance I believe. I use it all the time and have had up to 8 elements in the where clause and it worked fine. The only benefit is it is not dynamic, not a huge issue!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a stock time series data in which a numerical indicator fluctuates over time. If I want to find out for each stock that has its indicator now over 30, how many days since the last time this indicator is less than 30?
In other words, how many days since the last time the stock's indicator crosses above 30?
Just for your reference, currently I have the following:
SELECT ticker,
DATEDIFF(d,(SELECT MAX(DATE) FROM....),'12/21/2009')
WHERE Indicator > 30 AND Date = '12/21/2009'
I'd greatly appreciate any help! If possible please provide a sample code.
By the way, thank you Ashfield for your help on a similar question last time, later I was able to understand the code you gave me and utilized it in different ways. This is a bit more difficult for me though.
modified on Monday, December 21, 2009 12:06 AM
|
|
|
|
|
How about something like
select ticker,datediff(day,t.date,(select max(date) from table t1
where t1.ticker = t.ticker and t1.indicator < 30))
from table t
You may have to play about a bit, but it should be somewhere close
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thank you Ashfield, I'll test it out later and come back if I still have an issue.
|
|
|
|
|
i exported data in a table from an apache derby dbase to a sql script so i could run on MS SQL(Studio Express) to get the data in my table
but then there is this script for this table anytime i try running the script i get the following error msg.
before this the script would not even open the Studio when i try opening it and an err msg of "out of memmory would pop up"
please help me as to how i can solve this errror.
.
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Cannot execute script.
------------------------------
ADDITIONAL INFORMATION:
Exception of type 'System.OutOfMemoryException' was thrown. (mscorlib)
------------------------------
|
|
|
|
|
How big is that script? Have you considered loading and executing it piece by piece?
You can execute scripts from the commandline, without open the Management Studio. That's described here;
http://msdn.microsoft.com/en-us/library/ms165702.aspx
I are Troll
|
|
|
|
|
thanks for attending to my question.
the size is 65.2MB..
i am going to loading in pieces.. and post my response back...
|
|
|
|
|
I envisaged a low powered machine or something. I never imagined a 65mb script what a monster. I don't think I have ever seen such a beast.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
it can't be low powered machine(4GB RAM, 2.8GHz Duo Core)
i finally exported it to csv and ported it excel. i had all data in there so just wrote
some sql statemet to import the data..into my tables. the whole dbase is in apache
derby and i need all of them in a platform i am comfortable with.
thanks
|
|
|
|
|
Hey,
In my project I’m going to use distributed databases. My target is creation of sales system so I will distribute for example order table. Mainly I use horizontal partition.
So my question is about solutions for .NET?Do you know any framework that supports heterogenic distributed databases?I want to define partition strategy in separate class(or alternatively in separate class). Then framework should allow me insert record and decide which database should be used.
It’s my first distributed database project so please share your experience. I have no idea how to start.
|
|
|
|
|
piotr.zielinski wrote: I have no idea how to start.
Yup, that's how you start. Then you pick up a pen and start doing research[^]. Find an example of an application that you would like to build, make a list of definitions so that you know what's going on, try to divide the work into smaller pieces, etc.
You'll find that magic happens right after you have started
I are Troll
|
|
|
|
|
I know foundations- I'm looking for framework that allow me inject partition strategy at runtime.
|
|
|
|
|
|
What I need is, while this transaction is executing by one client, no other client can execute the code lines between the "begin transaction" and "commit transaction".
I thought this is the default behavior, but I was apparently wrong.
[Edit]
1. I would like everything within a transaction be executed in a "critical section", is that too much to ask? Just wondering.
2. The transaction is on the server and within a stored procedure.
[/Edit]
modified on Friday, December 18, 2009 10:10 AM
|
|
|
|
|
Short of having the first 'Begin Transaction' also disconnect all clients, I'm not sure how you would accomplish this. Some RDBMS support operational modes where client access can be retricted, but these are used more often for administrative purposes, rather than some type of application operation.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I don't mean disconnect all clients, other clients just need to wait until the transaction is done, like a "critical section" as I described below.
|
|
|
|
|
Is the transaction being executed at the client side or server side(within an sp for example)?
If at the client side, then you're going to need some kind of notification system to coordinate the operation.
If at the server side, then you at least need to put the transaction into a stored procedure or function, but I don't know how you would limit client access to it so that when one client makes a call to it, all other clients block until the first call completes.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
It is on the server side and it is within a stored procedure.
|
|
|
|
|
|
Create a "lock" table with one row in it. At the beginning of your transaction select this row for update. If you run the transaction with an isolation level of serializable then it will lock the row exclusively to your transaction. Any other transaction that tries to access the same table will block until (a) your transaction commits or (b) your transaction rolls back or (c) it times out.
|
|
|
|
|
Thanks. Your solution is definitely workable, a little more complicated than I prefer, but there is no other choice for me right now. Thanks again.
|
|
|
|
|
how about using one of those global @@ variables?
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
|
swjam wrote: @@
I think the official name for that is the "nipples" prefix...
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I think they look like the nose holes of that fat cop in Simpson...
--
Kein Mitleid Für Die Mehrheit
|
|
|
|
|
Take a look at the Service Broker[^] in SQL Server 2005. It looks like queues might do what you're looking for, specifically using the receive[^] command in conjunction with create queue.[^]. The queue will execute only one command at a time, so just add the appropriate sp calls to it, unless you could potentially be getting the same commands (which you want to block) from parts of the code you don't control, in which case the whole thing is moot.
|
|
|
|