|
Did you try using Cursor, I think that can solve your problem.
Hope this helps.
All the best.
|
|
|
|
|
No, But How can i use cursor for this requirement...
If you can think then I Can.
|
|
|
|
|
You can use select exp(sum(ln(sal))) from emp which of course will throw an exception for negative numbers and zero.
But that you can easily fix with a case expression.
The best solution is of course to create a function to do the job.
|
|
|
|
|
Can u please explain with the form of SQL Query.
If you can think then I Can.
|
|
|
|
|
eg_Anubhava wrote: Can u please explain with the form of SQL Query.
Well I did.
I'll do it again, with extra explanation, and I'll write slowly.
You have a table EMP that has a column SAL that you want the product from instead of the sum.
Then the query can look like this:
SELECT EXP(SUM(LN(SAL)))
FROM EMP This query has the drawback that the LN() function will throw an exception for negative numbers and zero.
Tip: CASE WHEN and SIGN() will be useful.
This can be mathematically written like: A*B = eln(A)+ln(B)
|
|
|
|
|
It is generating an Error while executing this Query.
Error Message is : Undefined Function 'LN' in Expression.
If you can think then I Can.
|
|
|
|
|
ln() is supported on Oracle since version 8i. If you're using an older version than that, you're having bigger problems than the missing ln() function.
But if you by any chance aren't using Oracle despite your numerous references to PL/SQL, then both SqlServer and MSAccess uses log() instead of ln().
|
|
|
|
|
eg_Anubhava wrote: Can u please explain with the form of SQL Query.
which proves that you did not look at the link I posted above. It is shown there!
|
|
|
|
|
And obviously I didn't either. 
|
|
|
|
|
If you don't want to use any of the built in Oracle commands, then you could achieve this by using a cursor to iterate over the user's salary and keep a running total that way.
|
|
|
|
|
declare @temp table (ename varchar(10), sal int)
insert into @temp values ('Anu', 10)
insert into @temp values ('Minu', 20)
insert into @temp values ('Tusar', 20)
insert into @temp values ('Piter', 10)
select exp(sum(log(sal))) from @temp
|
|
|
|
|
It returns the wrong answer in some cases change the values and then try.
the difference of values are in some points.
If you can think then I Can.
|
|
|
|
|
Try
SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG(ABS(NULLIF(sal,0))))),0)*SIGN(MIN( ABS(sal)))*(COUNT(NULLIF(SIGN(sal),1))%2*-2+1),0) AS INTEGER)
FROM @temp
|
|
|
|
|
Briallent Wow
Thanks
If you can think then I Can.
|
|
|
|
|
Hi,
Even though you mentioned that you don't want to use PL/SQL you could be interested in this: Custom aggregates in Oracle[^]
Best regards,
mika
|
|
|
|
|
Dear Friends,
<List>
<Entry>
<id>1</uid>
<lastName>abc</lastName>
<idList>
<id>
<id>1</uid>
<idType>pqr</idType>
<idNumber>1001</idNumber>
<idCountry>INDIA</idCountry>
</id>
<id>
<id>2</uid>
<idType>xyz</idType>
<idNumber>1002</idNumber>
<idCountry>USA</idCountry>
</id>
</idList>
</Entry>
Above is the xml i have in my database i want to read this information and return output as given below
id LastName id idType idNumber idCountry
1 abc 1 pqr 1001 INDIA
1 abc 2 xyz 2001 USA
i am using OPENXML in my stored procedure the code is as given below
DECLARE @HANDLE INT
DECLARE @XMLDOC XML
SET @XMLDOC = (select ofacList from tbl_OFAC_SDN_List)
EXEC SP_XML_PREPAREDOCUMENT @HANDLE OUTPUT,@XMLDOC
SELECT * FROM OPENXML(@HANDLE, '/List/Entry', 2)
WITH ([id] int, lastName nvarchar(100))
EXEC sp_xml_removedocument @HANDLE
till this all working fine but i don't know how to relate the id information with the main data and how to get the output
as well as if i want to add this information into two table (tblmainTable and idDetailsTable) how i will manage it's relation.
tblmainTable Record
id LastName
1 abc
idDetailsTable Records
uid id idType idNumber idCountry
1 1 pqr 1001 INDIA
1 2 xyz 2001 USA
please help me to come out of this.
thanks and regard's in advance
Sasmi
modified on Tuesday, March 15, 2011 3:35 AM
|
|
|
|
|
Hi all,
I was wondering if it is possible to get up-to-second data from a database without querying for it.
I want a lock-mechanism that prevents people from editing specific rows on a windows form that have been edited by other users on other computers. The only way they are connected is by database.
An 'easy' solution would be to have a DB table that holds a boolean wether the current row is being edited and then have the application 'poll' for changes every few seconds. But there will be a lot of polling for a whole lot of nothing...
So is there any other smart way to do this?
Thanks.
It's an OO world.
|
|
|
|
|
Hi,
There are really many ways to do this, for example:
- If you want to check that no-one has made modifications after reading the data include optimistic locking. In this case include a column into the table which is modified every time the row is changed and include the value from this column in UPDATE/DELETE statements.
- If you want to prevent changes from another session after you have read the data until you release it, then take advantage of the pessimistic locks, i.e. lock the row the user is editing for example with a simple update. However this is typically not the best solution since transactions should never include user conversations.
But in any case do not include edit-state information in the table. In order to do that you would have to commit the change into to database and you would be in trouble in no time since you would have 'locked' rows left in the db.
You didn't mention the DB vendor, but for example in SQL Server there's a special datatype rowversion (timestamp in older versions) for optimistic locking.
|
|
|
|
|
Thanks for the reply.
That's already some useful information that I will certainly look into. But I do not see how it fixes the following problem yet.
The problem is that user A and user B both load the same data (into a WinForms app). Now while the data for both users is loaded user A starts to edit. At this time user B should automatically see that the row is being edited without the need of refreshing/reloading the screen (not manually anyway).
I am using SQL2008.
It's an OO world.
|
|
|
|
|
|
Wow, certainly going to check that out this weekend (maybe sooner)! Thanks 
It's an OO world.
|
|
|
|
|
You should have added "not urgent at all" to your subject line.
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.
|
|
|
|
|
That was hilarious
|
|
|
|
|
I never said it was urgent... 
But the quest for knowledge always has a top priority!
It's an OO world.
|
|
|
|
|
No problem 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
