select question 2

[FALCONSEYE]

I have the following fields in tblA

itemID fieldName fromVal toVal
110 end date 08-JUN-11 10-JUN-11
123 cost 8 9
123 quantity 1 6
157 quantity 4 1

If the field name contains cost or quantity for the same itemIDs, 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.

http://www.fuzzysiberians.com

 

[SantaMufasa]

FalconEye,

Following is one (of many) solutions to your need. I added a row to your data to cover the case where there is an "ItemID" with a "FieldName" of "Cost", but no accompanying "Quantity". In such case I made the resulting "FromVal" and "ToVal" a value of "0" since there is no quantity. If, instead, you want the results in such case to be the actual "FromVal" and "ToVal", just change the NVL value from "0" to "1":

SQL> select * from tbla;

    ITEMID FIELDNAME                      FROMVAL    TOVAL
---------- ------------------------------ ---------- ----------
       110 end date                       08-JUN-11  10-JUN-11
       123 cost                           8          9
       123 quantity                       1          6
       157 quantity                       4          1
       201 cost                           6          10

col FromVal format a10
col ToVal format a10
col x Heading "Non-Extended Values" format a50
select itemid
      ,fieldname
      ,decode(fieldname
             ,'cost',to_char((FromVal * nvl((SELECT FromVal from tbla i
                                              where i.ItemID(+) = o.ItemID
                                                and i.FieldName = 'quantity'),0)
                            ))
             ,fromVal
             ) fromVal
      ,decode(fieldname
             ,'cost',to_char((ToVal * nvl((SELECT ToVal from tbla i
                                            where i.ItemID(+) = o.ItemID
                                              and i.FieldName = 'quantity'),0)
                            ))
             ,toVal
             ) toVal
      ,decode(fieldname
             ,'cost','(Original "From" Cost: '||FromVal||'. Original "To" Cost: '||ToVal||'.)'
             ) x
  from tbla o
/

ITEMID FIELDNAME                      FROMVAL    TOVAL      Non-Extended Values
------ ------------------------------ ---------- ---------- --------------------------------------------------
   110 end date                       08-JUN-11  10-JUN-11
   123 cost                           8          54         (Original "From" Cost: 8. Original "To" Cost: 9.)
   123 quantity                       1          6
   157 quantity                       4          1
   201 cost                           0          0          (Original "From" Cost: 6. Original "To" Cost: 10.)

Let us know if this approximates what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[FALCONSEYE]

Wow! Thanks Musafa!

ColdFusion Ninja for hire.

 

[FALCONSEYE]

SantaMufasa sorry, typed your name wrong!



ColdFusion Ninja for hire.

 

[carp]

Here's another one:

SELECT tblA.*, vb.msg
FROM tblA
     LEFT OUTER JOIN (SELECT v.itemID, 
                             'cost is changed from '||v.from1||'*'||v.from2||'='||v.from1*v.from2||' to '||v.to1||'*'||v.to2||'='||v.to1*v.to2||' for itemID '||v.itemid AS msg
                        FROM (SELECT a.itemID, a.fieldname AS operand1, b.fieldname AS operand2,
                                     a.from_value AS from1, a.toval AS to1,
                                     b.from_value AS from2, b.toval AS to2
                                FROM tbla a
                                     INNER JOIN tbla b
                                        ON a.itemID = b.itemid
                                           AND a.fieldname = 'cost'
                                           AND b.fieldname = 'quantity'
                                           AND a.fieldname != b.fieldname) v) vb
                          ON tblA.itemID = vb.itemID;

with the results

ITEMID   FIELDNAME   FROM_VALUE   TOVAL   MSG
123   quantity   1   6   cost is changed from 8*1=8 to 9*6=54 for itemID 123
123   cost      8   9   cost is changed from 8*1=8 to 9*6=54 for itemID 123
157   quantity   4   1   
110   end date   08-JUN-11   10-JUN-11

OK, the alignment is jacked up, but you get the idea.

 

[FALCONSEYE]

SantaMufasa: I have been running some tests by changing quantity / parts and the query won't pick up the updated value.

for instance: i updated : 123 cost to 60000

my tempTable shows:
itemId fieldName fromVal toVal
110 cost 60000 500

the query returns:
110 cost 0 0 (Original "From" Cost: 60000. Original "To" Cost: 500.)

something is wrong here.


ColdFusion Ninja for hire.

 

[FALCONSEYE]

carp works great! thanks

ColdFusion Ninja for hire.

 

[SantaMufasa]

FalconsEye,

The reason that your re-query (post-update) shows "0"s is because your QUANTITY is also "0"... 0 times 60000 = 0, and 0 times 500 = 0. As I mentioned above, if you want 60000 and 500 to display in your query instead, change my code to read as follows (using "1" instead of "0" as the replacement for NULL values that result from their being no QUANTITY values):

select itemid
      ,fieldname
      ,decode(fieldname
             ,'cost',to_char((FromVal * nvl((SELECT FromVal from tbla i
                                              where i.ItemID(+) = o.ItemID
                                                and i.FieldName = 'quantity'),1) -- New NULL-replacement
                            ))
             ,fromVal
             ) fromVal
      ,decode(fieldname
             ,'cost',to_char((ToVal * nvl((SELECT ToVal from tbla i
                                            where i.ItemID(+) = o.ItemID
                                              and i.FieldName = 'quantity'),1) -- New NULL-replacement
                            ))
             ,toVal
             ) toVal
      ,decode(fieldname
             ,'cost','(Original "From" Cost: '||FromVal||'. Original "To" Cost: '||ToVal||'.)'
             ) x
  from tbla o
/

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[FALCONSEYE]

This was working fine until this happened: MuFaSa, I get:
ORA-01427: single-row subquery returns more than one row

Carp, your solution now repeats the same result for 33 times.

Task: Painting
Item:194: Paint Entire House has been deleted
Paint Entire House cost: ($825.00)

this is what the current table looks like:

id	itemID	fieldName	fromVal	toVal
5377	0	start date	30-Jun-11	22-Jul-11
5377	0	end date	25-Jul-11	5-Aug-11
5377	18	itemID added		18
5377	68	itemID added		68
5377	90	cost	195	350
5377	90	vendor name	compA	compB
5377	90	vendor address	1 place	PO BOX 333333
5377	90	vendor city	sacramento	RIVERDALE
5377	90	vendor zip	12345	98765
4108	90	itemID removed		90
5377	90	vendor_id	76075	125823
5377	92	itemID added		92
5377	102	itemID added		102
5377	104	itemID added		104
5377	127	itemID added		127
4108	141	itemID removed		141
5377	143	quantity	5	7
5377	146	itemID added		146
5377	149	itemID added		149
5377	152	itemID added		152
5377	153	itemID added		153
5377	155	itemID added		155
5377	163	itemID added		163
5377	166	itemID added		166
5377	167	itemID added		167
5377	169	itemID added		169
5377	170	itemID added		170
4108	194	itemID removed		194
5377	194	vendor zip	12345	98765
5377	194	vendor city	sacramento	RIVERDALE
5377	194	vendor address	1 place	PO BOX 333333
5377	194	vendor name	compA	compB
5377	194	cost	825	1200

In this case for painting, I'd expect to see compA (addresss, city, zip, etc) deleted for itemID 194 with the cost of $825 and compB (addresss, city, zip, etc) added with the cost of $1200.

any help?

ColdFusion Ninja for hire.

 

[Dagon]

You must have two quantity values for an item somewhere. You can find them by running a query like:

select itemId, count(*)
from tblA
where FieldName = 'quantity'
group by itemId
having count(*)>1

For Oracle-related work, contact me through Linked-In.

 

[FALCONSEYE]

Yes, I do have multiple quantities but for different itemIDs.

ColdFusion Ninja for hire.

 

[Dagon]

The only place that error can come from in Santa's query is:

SELECT ??? from tbla i
where i.ItemID(+) = o.ItemID
and i.FieldName = 'quantity'

This means you must have at least one row (and it only needs one) where there are two rows with FieldName 'quantity' for the same ItemId.

For Oracle-related work, contact me through Linked-In.