Home » SQL & PL/SQL » SQL & PL/SQL » Value multiplication help (Oracle 10g Pl/SQL)
Value multiplication help [message #608395] Wed, 19 February 2014 06:13 Go to next message
pmreddy.mahi@gmail.com
Messages: 12
Registered: December 2011
Junior Member
Hi All,


WITH t AS
(
SELECT '01-jan-2014' COL1, 'B' COL2, .001 COL3, .0006 COL4 FROM DUAL UNION ALL
SELECT '02-jan-2014' COL1, 'B' COL2, .002 COL3, .0007 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .003 COL3, .0008 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .002 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'C' COL2, .004 COL3, null COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'B' COL2, .005 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .005 COL3, .0009 COL4 FROM DUAL
)
SELECT
*
FROM
t;

and the result of above query is

01-jan-2014 B 0.001 0.0006
02-jan-2014 B 0.002 0.0007
03-jan-2014 B 0.003 0.0008
03-jan-2014 B 0.002 0.0009
03-jan-2014 C 0.004
04-jan-2014 B 0.005 0.0009
04-jan-2014 C 0.002

My requirement is
if we have same dates with different COL2 values we need to multiply COL3 of C value with COL4 of B value.

01-jan-2014 B 0.001 0.0006
02-jan-2014 B 0.002 0.0007
03-jan-2014 B 0.003 0.0000032
03-jan-2014 B 0.002 0.0000036
03-jan-2014 C 0.004
04-jan-2014 B 0.005 0.0000018
04-jan-2014 C 0.002

Thanks in Advance!
Re: Value multiplication help [message #608399 is a reply to message #608395] Wed, 19 February 2014 06:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2070
Registered: January 2010
Senior Member
And what if there are bultiple B's and/or multiple C's per same date?

SY.
Re: Value multiplication help [message #608401 is a reply to message #608399] Wed, 19 February 2014 07:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2070
Registered: January 2010
Senior Member
I will assume to use max value in case of multiple C's per date. Also, your source data doesn't match posted results. Based on "and the result of above query is", last part of the union should be:

SELECT '04-jan-2014' COL1, 'C' COL2, .002 COL3, null COL4 FROM DUAL


Then:

WITH t AS
(
SELECT '01-jan-2014' COL1, 'B' COL2, .001 COL3, .0006 COL4 FROM DUAL UNION ALL
SELECT '02-jan-2014' COL1, 'B' COL2, .002 COL3, .0007 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .003 COL3, .0008 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'B' COL2, .002 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '03-jan-2014' COL1, 'C' COL2, .004 COL3, null COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'B' COL2, .005 COL3, .0009 COL4 FROM DUAL UNION ALL
SELECT '04-jan-2014' COL1, 'C' COL2, .002 COL3, null COL4 FROM DUAL
)
SELECT  col1,
        col2,
        col3,
        case
          when max(case col2 when 'C' then col3 end) over(partition by col1) is null then col4
          when col2 = 'B' then col4 * max(case col2 when 'C' then col3 end) over(partition by col1)
          else col4
        end col4
  FROM  t
/

COL1        C       COL3       COL4
----------- - ---------- ----------
01-jan-2014 B       .001      .0006
02-jan-2014 B       .002      .0007
03-jan-2014 B       .003   .0000032
03-jan-2014 B       .002   .0000036
03-jan-2014 C       .004
04-jan-2014 B       .005   .0000018
04-jan-2014 C       .002

7 rows selected.


SY.

[Updated on: Wed, 19 February 2014 07:02]

Report message to a moderator

Re: Value multiplication help [message #608428 is a reply to message #608401] Wed, 19 February 2014 10:13 Go to previous messageGo to next message
pmreddy.mahi@gmail.com
Messages: 12
Registered: December 2011
Junior Member
I found the solution like this but it is not working in procedure. Any help!

SELECT col1,
col2,
col3,
CASE WHEN col2 <> LEAD(col2) OVER(PARTITION BY col1 ORDER BY col2) THEN col4*LEAD(col3) OVER(PARTITION BY col1 ORDER BY col2) ELSE col4 END AS col4
FROM t;
Re: Value multiplication help [message #608430 is a reply to message #608428] Wed, 19 February 2014 10:19 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Yepp, you found Cool it.

[Updated on: Wed, 19 February 2014 10:19]

Report message to a moderator

Re: Value multiplication help [message #608431 is a reply to message #608430] Wed, 19 February 2014 10:20 Go to previous messageGo to next message
pmreddy.mahi@gmail.com
Messages: 12
Registered: December 2011
Junior Member
In pl/sql block it is not working!
Re: Value multiplication help [message #608432 is a reply to message #608431] Wed, 19 February 2014 10:38 Go to previous messageGo to next message
pmreddy.mahi@gmail.com
Messages: 12
Registered: December 2011
Junior Member
Is it possible to write with out using analytical functions like LEAD.
Re: Value multiplication help [message #608433 is a reply to message #608431] Wed, 19 February 2014 10:43 Go to previous messageGo to next message
ThomasG
Messages: 3115
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
pmreddy.mahi@gmail.com wrote on Wed, 19 February 2014 17:20
In pl/sql block it is not working!


Post the actual SQL*Plus session with the actual error you get, we are not psychics, you know?


Re: Value multiplication help [message #608434 is a reply to message #608433] Wed, 19 February 2014 10:46 Go to previous message
pmreddy.mahi@gmail.com
Messages: 12
Registered: December 2011
Junior Member
I am not getting the error. but I am getting the raw values of COL4.
Previous Topic: Creating a table and referencing that table in same package.
Next Topic: SELECT INTO causes ORA-00905 Error
Goto Forum:
  


Current Time: Fri Nov 28 18:27:20 CST 2014

Total time taken to generate the page: 0.14735 seconds