|
Glad to hear you got it resolved!
|
|
|
|
|
hi everybody,
I have a big problem with update statement, let me mention the situation by exact script and results!
first create trans table=>
create table trans(
id bigint identity(1,1) not null,
price decimal(18,0) null,
relatedid bigint null)
Then fill it by this script
Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(500,NULL)
Insert into trans (price,relatedid)values(1,NULL)
Insert into trans (price,relatedid)values(1,3)
Now run this query!
Update trans
set price=(case when relatedid is null then (select avg(price) from trans where id<=t.id)
else (select top(1) price from trans where id=t.relatedid) end)
from trans t
After execution of query I expect this result:
id-----price-----relatedid
1 100 NULL
2 300 NULL
3 200 NULL
4 200 3 <======== realy expected!!!!
But this is the result!!!
id-----price-----relatedid
1 100 NULL
2 300 NULL
3 200 NULL <======= #3 row
4 1 3 <===== wrong value, i mean this is the value before update
row with id 3 has been updated before but update doesnt know this!!!!!
WHAT I NEED i need to get expected result from this execution!!!! plzzzzzzzzzzz
modified on Wednesday, April 13, 2011 7:53 AM
|
|
|
|
|
My output looks like this:
id price relatedid
1 100 NULL
2 100 NULL
3 233 NULL
4 175 NULL
5 500 3
SQL Server 2008 R2
|
|
|
|
|
row #5 expected to be updated to 233, not 500
|
|
|
|
|
Not the way it is written.
select top(1) price from trans where id=t.relatedid gives the value in the database at the time of the query not after the update.
You would need to calculate the maximum average in this query.
|
|
|
|
|
djj55 wrote: You would need to calculate the maximum average in this query.
how i could do this?
|
|
|
|
|
For starters, I don't think you have the data in your table what yuo included in the script. In the script you add 5 rows but in the result you have 4 rows, not possible. Seems that you don't have the first row in the database so that makes the different output after the update.
If you truncate the test table and try again with 5 rows, are the results expected.
|
|
|
|
|
I am running a MDX query on a Cube. When I parse or run that query It gives me following error:
A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)
No connection could be made because the target machine actively refused it 172.16.1.86:2383 (System)
When I click OK button , I see the connection to the server is lost.
Then I login to Analysis Service Server manchine, I come to know that the Analysis Service has been stoped.
Then I start the service again and tried to parse the query , It behaved in the same way.
But If I run any other MDX query , It works fine ....
PLease help me in this regard,
Irfan
|
|
|
|
|
So are you saying that when you start the analysis services, it stops when you run a query against it. If that's the case, try to find related error messages from Windows Event Log.
|
|
|
|
|
Do all MDX queries cause SSAS to crash? Or just the one you are trying to run?
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
I have a sql statement that counts the units(cable boxes) that were refurbed(had damaged parts replaced) and total units(cable boxes that just went through refurb and had nothing replaced) and its supposed to do this count regularly, (every time a unit is processed the count). Can someone please help me? Thank you.
Justin
Heres my sequal code so far:
SELECT COUNT(*)
FROM cxadminn.repair_part
WHERE repair_type = 'REFURB' and created_date >?
|
|
|
|
|
If you want to get the current date including time you use SYSDATE. For example:
SELECT SYSDATE FROM dual;
But I don't know why would you put SYSDATE in place of the question mark unless you have rows in the table where created date is in the future?
|
|
|
|
|
Exchange the ? with a "to_date" function.
For example: to_date('2009/07/09', 'yyyy/mm/dd') would return a date value of July 9, 2009.
There's more on the formatting here[^]
|
|
|
|
|
SELECT current_date
FROM dual; or
SELECT SYSDATE FROM dual;
|
|
|
|
|
Hi All,
I have one table containing few columns.
LruName, ChName and Type.
I want to fetch a query from the above table
order by LruName, ChName, Type
by using the above query I am getting output like this
-------------------------------------------------------------------------------------------------------
LruName ChName Type
ADU1 CH1 Tx
ADU2 CH2 Tx
AHRS1 CH3 Tx
AHRS2 CH4 Tx
DVS CH24 Rx
DVS CH28 Rx
DVS CH5 Tx
DVS CH6 Tx
FADEC1 CH1 Tx
FADEC1 CH2 Tx
FADEC2 CH3 Tx
FADEC2 CH4 Tx
NLDW CH8 Tx
OAS CH7 Tx
----------------------------------------------------------------------------------------------------------
But I want output like this
----------------------------------------------------------------------------------------------------------
LruName ChName Type
ADU1 CH1 Tx
ADU2 CH2 Tx
AHRS1 CH3 Tx
AHRS2 CH4 Tx
DVS CH5 Tx
DVS CH6 Tx
DVS CH24 Rx
DVS CH28 Rx
FADEC1 CH1 Tx
FADEC1 CH2 Tx
FADEC2 CH3 Tx
FADEC2 CH4 Tx
NLDW CH8 Tx
OAS CH7 Tx
ie I want LruName, Chname and Type to be order wise
again in Type Tx ( rows ) should come first then Rx.
how to write a query by getting the above results.
|
|
|
|
|
if the Type field is limited to the values in your example, then a descending order would do it, hence:
order by LruName, ChName, Type DESC
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Hi,
thank u for the reply.
But i want Tx rows should come first then Rx rows
|
|
|
|
|
as Tx > Rx and you want Tx first, descending should do it.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
It is not working
I gave like this
ORDER BY LruName, ChName, Type desc
|
|
|
|
|
in your example the Type isn't really relevant to the sort as all (LruName,ChName) combinations are unique already without the Type field.
So maybe you want
order by LruName, Type DESC, ChName
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
yaah... it is working
I got what I want
thank u very much.
|
|
|
|
|
One of the problems you are going to have is with the ChName column. It appears as though you want CH5 to appear before CH24. In your order by clause you may have to have it
order by .... ,length(ChName),ChName, ...
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]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Hi,
Luc Pattyn
solved my problem
thank you for the reply
|
|
|
|
|
That's a good solution for a common problem.
A five from me even if it wasn't what the OP wanted.
|
|
|
|
|
Thanks Jörgen.
Jörgen Andersson wrote: even if it wasn't what the OP wanted.
Oddly, in the original post he indicated what output he was after and so I'm pretty sure he is going to have to do something similar to what I offered.
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]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
