Help - Search - Members - Calendar
Full Version: Query NOT as per Client logic,,plz help
Oracle DBA Forums > Oracle > Oracle Forum
rhnshk
this is the Excel representation of the DATA like how the users of my client is maintaining Buy/Sell txns.

CODE
DT         COD         PRICE        PURQTY       PURCOST        BALQTY       BALCOST      AVGPRICE       SOLDQTY       SOLDAMT     NETSELAMT      COSTSALE      GAINLOSS         COMM%
---------- --- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
03/01/2008 BUY         2.221     75000.000    166593.158     75000.000    166593.158    2.22124211          .000          .000          .000          .000          .000       666.373
10/01/2008 SEL         2.226          .000          .000     72000.000    159929.432    2.22124211      3000.000      6677.000      6650.292      6663.726       -13.434        26.708
15/03/2008 BUY         2.306     35000.000     80717.835    107000.000    240647.267    2.24903988          .000          .000          .000          .000          .000       322.871
01/04/2008 SEL         2.880          .000          .000    102000.000    229402.067    2.24903988      5000.000     14400.000     14342.400     11245.199      3097.201        57.600
20/04/2008 BUY         2.169     50000.000    108432.000    152000.000    337834.067    2.22259255          .000          .000          .000          .000          .000       433.728


the main thing tat i am unable to arrive as per my client logic is the COSTSALE & AVGPRICE. His formula for these columns are;
BALQTY formula is simple BALQTY = BALQTY + PURQTY - SOLDQTY

COSTSALE = AVGPRICE * SOLDQTY &
the BALCOST is a running total (BALCOST = BALCOST + PURCOST - COSTSALE)
AVGPRICE = BALCOST / BALQTY

Based on this logic i designed Tables and Form for the above requiremnt.
inside the form, for validation trigger i need an SQL to arrive at the AVGPRICE tat will calculate GAINLOSS.
the table stores CODE, PRICE, QTY, GROSSAMOUNT, COMMAMOUNT, NETAMOUNT, depending upon the Txn CODE & m not storing COSTSALE in the table. i have not defined in the tab-stuc. so i tried to put an SQL for this;

CODE
select
SUM(DECODE(CODE,'BUY',NETAMOUNT,-NETAMOUNT)) /
SUM(DECODE(CODE,'BUY',QTY,-QTY))  AS AVGPRICE
from TXN
where txndate < TO_date('25/02/2008','dd/mm/yyyy')

     AVGPRICE
-------------
   2.17612188



this is what m getting right now, but its NOT calculating GAIN/LOSS correctly, bcoz m unable to incorporate COSTSALE in my query as per my client logic.
and
i dont know how to take care of the COSTSALE. kindly help me come out of this...

ty v much in advnce.
burleson
Hi,

>> inside the form, for validation trigger i need an SQL to arrive at the AVGPRICE tat will calculate GAINLOSS.

Aha, this is a great question! You have two choices:

1 - Create "functions" for the columns and calculate their values, and store these derivations in the table.

2 - Don't create the columns (they waste space and are fully derived from other columns anyway), and compute their value whenever they are requested.

What's the best approach? It depends.

- Will they waste an appreciatbe amount if disk?
- Is there easurable overhead in computing them at request time?

BTW, in 11g, they have"virtual columns", perfect for what you are doing:

http://www.dba-oracle.com/oracle11g/oracle...sed_columns.htm

************************************************************
>> inside the form, for validation trigger i need an SQL to arrive at the AVGPRICE tat will calculate GAINLOSS.

If you are not 11g, I would consider using an INSERT trigger instead, filling-in the values at insert time.

************************************************************
>> not storing COSTSALE in the table

So, where does it come from? Is it compoted from the values of other columns?

Once you figure-out where costsale comes from, it's easy
HAL9000
I see that you asked the exact same question on OTn.

http://forums.oracle.com/forums/thread.jsp...73&tstart=0

Why did you ask all over again here? Were you unhappy with all of the answers that you were given there?

They looked very good to me, some nice people even attempted to solve it for you.

Tell me, do think think that it's disrespectful to ask many people the exact same question, wasting their time?

YOur "issue" strikes me not as a legitimate question.

Instead, it appears that you may be an untrained beginner who needs someone to do their work for them, but is unwilling to pay them for their time.

Yep, here it is, right here. You are not interested in learning anything, and you don't have any question.

You just want someone to solve it for you:


so i wud rather like to sent to you thru email..i need you are email id..
can you suggest me an SQL,


In the future, I would stay on OTN. They are mostly willing to help people keep their jobs, and they don't care if you understand it.
aussie_dba
He's just another spammer. He posted it on OraFAQ too:

http://www.orafaq.com/forum/t/122485/0/

No questions, he just wants somebody to do his job for him.

And they wonder why outsourcing is a failure. I'll bet that his employer thinks that he knows what he is doing, and he relies on the kindness of others to save him.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.