CODEWITHSUNDEEP
oracle-database
Kung Fu Ninja
Kung Fu Ninja

Reputation: 3752

select statement

my table looks like this:

enter image description here

If the field name contains cost or quantity for the same lineItemIds, I have to display the result as:

cost is changed from 8*1=8 (fromVal*fromVal) to 9*6=54 (toVal*toVal) for itemID 123.

any help will be appreciated.

Upvotes: 0

Views: 64

Answers (2)

manji
manji

Reputation: 47978

SELECT tc.LINE_ITEM_ID              ITEM_ID,
       tc.FROMVAL              COST_FROMVAL,
       tq.FROMVAL               QTY_FROMVAL,
       (tc.FROMVAL*tq.FROMVAL) PROD_FROMVAL,
       tc.TOVAL                  COST_TOVAL,
       tq.TOVAL                   QTY_TOVAL,
       (tc.TOVAL*tq.TOVAL)       PROD_TOVAL,
  FROM
(SELECT LINE_ITEM_ID,
        FROMVAL,
        TOVAL,
   FROM table
  WHERE FIELDNAME = 'cost') tc
JOIN (SELECT LINE_ITEM_ID,
             FROMVAL,
             TOVAL,
        FROM table
       WHERE FIELDNAME = 'quantity') tq
  ON tc.LINE_ITEM_ID = tq.LINE_ITEM_ID

Upvotes: 2

mservidio
mservidio

Reputation: 13057

I would look into using product aggregate functions. You'll have to compile them yourself though, Oracle doesn't include them as system functions. http://radino.eu/2010/11/17/product-aggregate-function/

If it's just for this one case where cost or quantity are used, then you could also just use subqueries, or temporary transaction based tables.

I'd provide you with a query example, but unfortunately don't have an Oracle instance accessible presently.

Upvotes: 0

Related Questions