|
Why have you posted this question in the SQL forum when it is clearly a C# question?
Paul Marfleet
|
|
|
|
|
Why would you do this also...??
|
|
|
|
|
Hi i have one query this is giving o/p:
But Probs is its taking long time to produce the o/p:
any one give me the solution to fetch quickily for the below query:
My query:
Select CO_NAME,Sector,Analyst,edate,CurrentSector,CurrentConSec,Sec_Recomm,Sec_Confidence,CurrentMarket,
CurrentConMark,LastMarket,LastConMark,ClosingMarketPrice AS ClosingPrice,
CurrentClosingMarketPrice AS CurrentClosingPrice,
((CurrentClosingMarketPrice)/(ClosingMarketPrice)) AS Performance,AnnId
from
(SELECT first 100 MASTER.CO_NAME, NewCompany.Co_Name AS Company, SectorMaster.Sector_Name AS Sector,
UserMaster.UserName AS Analyst, AnalystViews.DOE AS edate, AnalystViews.Sec_Recomm AS CurrentSector,
AnalystViews.Sec_Confidence AS CurrentConSec,
(SELECT first 1 Q.Sec_Recomm FROM AnalystViews Q ,MeetingSchedule INNER JOIN MeetingSchedule M ON
Q.AnnounceId = M.AnnounceId WHERE (Q."ACTIVE" = 'T') AND (Q.Completed = 'Y') AND
M.CO_CODE = MeetingSchedule.CO_CODE ORDER BY Q.DOE) ,
AnalystViews.Sec_Confidence,AnalystViews.Mark_Recomm AS CurrentMarket,
AnalystViews.Sec_Confidence AS CurrentConMark ,AnalystViews.Mark_Recomm AS LastMarket ,
(SELECT first 1 Q.Sec_Confidence FROM AnalystViews Q ,MeetingSchedule M INNER JOIN
MeetingSchedule ON Q.AnnounceId = M.AnnounceId WHERE (Q."ACTIVE" = 'T') AND (Q.Completed = 'Y') AND
M.CO_CODE = MeetingSchedule.CO_CODE ORDER BY Q.DOE) AS LastConMark ,
(SELECT first 1 "CLOSE" FROM DLYPRICEBSE ,MeetingSchedule ,AnalystViews
WHERE (DLYPRICEBSE.Co_Code = MeetingSchedule.Co_Code) AND (DLYPRICEBSE."DATE"<= AnalystViews.DOE)
ORDER BY DLYPRICEBSE."DATE" DESC) AS ClosingMarketPrice,
(SELECT first 1 "CLOSE" FROM DLYPRICEBSE ,MeetingSchedule WHERE
(DLYPRICEBSE.Co_Code = MeetingSchedule.Co_Code) ORDER BY DLYPRICEBSE."DATE" DESC) AS CurrentClosingMarketPrice,
AnalystViews.AnnounceID AS AnnId FROM MASTER RIGHT OUTER JOIN NewCompany RIGHT OUTER JOIN
MeetingSchedule ON NewCompany.Co_code = MeetingSchedule.Co_Code ON MASTER.CO_CO
Regards,
Magi
|
|
|
|
|
Look at the execution plan produced by SQL Server for your query. Identify those parts of the query that are consuming a disproportionately large amount of the query batch time and try and optimise them.
Paul Marfleet
|
|
|
|
|
Hi Magi
Which database engine is this for? Informix?
Try getting the individual sub-queries to work separately. For example:
(SELECT first 1 Q.Sec_Confidence
FROM AnalystViews Q, MeetingSchedule M
INNER JOIN MeetingSchedule
ON Q.AnnounceId = M.AnnounceId
WHERE Q."ACTIVE" = 'T'
AND Q.Completed = 'Y'
AND M.CO_CODE = MeetingSchedule.CO_CODE
ORDER BY Q.DOE) AS LastConMark looks like it is supposed to be a correlated sub-query (and should link back to the outside query). It is probably doing lots of work that you don't want it to do (and I guess is returning the wrong answer).
In this case, I would expect at least one of the links to the MeetingSchedule table to be removed. Possibly:
SELECT FIRST 1 Q.Sec_Confidence
FROM MeetingSchedule M
INNER JOIN AnalystViews Q
ON Q.AnnounceId = M.AnnounceId
AND Q."ACTIVE" = 'T'
AND Q.Completed = 'Y'
WHERE M.CO_CODE = MeetingSchedule.CO_CODE
ORDER BY Q.DOEWhich you could test by replacing "MeetingSchedule.CO_CODE" with an appropriate code.
Hope this helps.
Andy
|
|
|
|
|
Hi All.
I need Client tool to connect SQL-2005 (somting like 'SQL query Analyzer')
and some API tool to connect SQL-2005 from VC++ 6.0
|
|
|
|
|
david bagaturia wrote: and some API tool to connect SQL-2005 from VC++ 6.0
SQL Management Studio : downloadable from Microsoft.
david bagaturia wrote: I need Client tool to connect SQL-2005 (somting like 'SQL query Analyzer')
Standard SQL 2000 code will work as well as ODBC.
|
|
|
|
|
simply all i want to do is to make this expression works : DataSet.Tables["Accounting"].Columns["Total"].Expression = "Income - Spends";
this i put in the form load but when i try to complie vs 2005 gives me some crap error which i dunno what does it means (The column mapping from SourceColumn 'Total' failed because the DataColumn 'Total' is a computed column) i dunno what exactely it requires to do my simple math operation on the database and return me a decent value..and if this way is wrong what is right to do it..i hope someone get to me as fast as possible..thanx!
|
|
|
|
|
Please read the forum guidelines. Giving your messages titles like 'help' or 'urgent' gives us no idea what the problem is and will get you ignored.
__________________________
Don't drink and derive.
Alcohol and calculus don't mix.
|
|
|
|
|
Oh boy, another "urgent" to ignore 
OT:
I like that sig, but I've been able to work with PDEs well after a few beers 
"Try asking what you want to know, rather than asking a question whose answer you know." - Christian Graus
|
|
|
|
|
I'm urgently ignoring it.;P
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I try to be helpful, but after 12 hours and no response to my request for clarification I can see that it isn't really that urgent.
|
|
|
|
|
Colin Angus Mackay wrote: after 12 hours and no response to my request for clarification I can see that it isn't really that urgent.
The original post must not be really that urgent after all. Putting in "urgent" must have just been bait to get someone to reply right away.
"Try asking what you want to know, rather than asking a question whose answer you know." - Christian Graus
|
|
|
|
|
matched wrote: when i try to complie vs 2005 gives me some crap error which i dunno what does it means (The column mapping from SourceColumn 'Total' failed because the DataColumn 'Total' is a computed column)
That doesn't read like a compiler error to me. The compiler doesn't know anything more about DataSets, DataRows and DataColumns beyond the fact they are classes - so a high level error that indicates semantic knowledge of the classes it is using is virtually impossible to come from the compiler. I can't actually see how the compiler could pick up on this error as the fact that "Total" is computed can't really be known until run time.
So, where did this dataset come from? How was it created? What filled it and with what?
|
|
|
|
|
database columns supposed to be filled by the interface user \,and the error was in the calculated columns i want to enter a number qand another one and substract from each other to get me the last empty column...it's a simple thing i know but i was sick of it,and all those people who bothered to reply with silly things i will just ignore you ! so mature you are !! (give me one person who reads any instructions on forums,except you ofcourse)...
|
|
|
|
|
matched wrote: give me one person who reads any instructions on forums
There are enough forum guidelines around the world that you must have picked up on one or two of them... Curiously, the all say very similar things.
|
|
|
|
|
Hi
I have the following questions with regards to drag-drop type of databinding in windows forms.
Platform:
windows forms data bound form with the database as a source...
Bound column is defined as Identity in target SQL Server database
The databinding is created by draging-droping the datasource
Binding Navigator is automatically created
Senario:
When clicking the "BindingNavigatorAddNewItem" (the one with +) the bound text field to the identity column is incremented by 1.
Qustions:
Q1 - Is there a row added after to the underlying datatable after the click? Is the row added to the datatable only when you press the save button?
Q2 - Where is the code that increments this value?
Q3 - How to control the process of incrementing the value and showing it on the textbox? For example, I tried to create an event handler for this button but as soon as the code gets to this handler, the increment would have been completed already.
I have also tried to use the BindingSource.CancelEdit() but that did not work either.
Thanks for your help.
|
|
|
|
|
Hi,
I'm looking for some advice on a problem:
Mobile application implementing AUTD (http://msdn2.microsoft.com/en-us/library/aa446486.aspx[^]) calls a webservice. This webservice polls a SQL server (2005) every 30 seconds, for at most 3 minutes, lookikng for changes. Once the webservice has detected a change it returns true otherwise false. If true the mobile application makes another request to retrieve the changes, otherwise it issues a call to the webservice to check for changes.
The problem is that I would like a better way of checking SQL server for changes - some kind of event driven approach. I have tried using the Service Broker, based on http://www.codeproject.com/useritems/SQLNotificationRequest.asp?df=100&forumid=399750&exp=0&select=2062673[^]. However timing out the web method was impossible. I left the method running for an hour and it still responded to a change in a SQL table!!!!! The command timeout was set to 10 seconds.
Any help/suggestions would be greatly appreciated,
Mark
|
|
|
|
|
We are redoing our database. We have in an excel spreadsheet the mapping from the old to
the new: old table name, old column names, new table name, new column names. Is there a
tool out there or code that someone has put together that will create a new procedure from
the old replacing the old table names and old column names with the new table names and
new column names?
|
|
|
|
|
I suppose is a typical query but I dont know if exists a query to solve the next. Take for instance:
1.- Table items:
Code|Name
1000 Apple
1001 Orange
1002 Banana
2.- Table prices
Shop|Code|Price
1 1000 1.33
2 1000 1.44
1 1001 1.90
2 1001 2.10...
Can I show in a SQL query something LIKE:
Code Name PriceShop1 PriceShop2
1000 Apple 1.33 1.44
1001 Orange 1.90 2.10....
Thanks for any help because Im getting crazy
|
|
|
|
|
If you just have 2 shops then:
select i.Code, i.Name, p1.Price as PriceShop1, p2.Price as PriceShop2
from Items i
left outer join Prices as p1
on p1.Code = i.Code
and p1.Shop = 1
left outer join Prices as p2
on p2.Code = i.Code
and p2.Shop = 2
order by i.Code This links to the Prices table twice (one for shop 1 and once for shop 2). The "p1" and "p2" bits are "alias" names for these joins.
If you have more shops then you should try searching google for "crosstab queries".
Regards
Andy
|
|
|
|
|
If you are in SQL2005 you should look at the PIVOT command.
|
|
|
|
|
OK I'll take a look, thanks
|
|
|
|
|
sorry friend .i am getting exact ans, iam geting this one.
SELECT i.Item, p.shop
FROM Items i, prices p
WHERE i.code = p.code
GROUP BY i.Item, p.shop
regards venkat
|
|
|
|
|
How To export Data from a DataTable to Excel Sheet
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
