Home » SQL & PL/SQL » SQL & PL/SQL » Value multiplication help (Oracle 10g Pl/SQL)
Value multiplication help Wed, 19 February 2014 06:13
 pmreddy.mahi@gmail.com Messages: 12Registered: 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

Re: Value multiplication help [message #608399 is a reply to message #608395] Wed, 19 February 2014 06:47
 Solomon Yakobson Messages: 2622Registered: January 2010 Location: Connecticut, USA 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
 Solomon Yakobson Messages: 2622Registered: January 2010 Location: Connecticut, USA 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
 pmreddy.mahi@gmail.com Messages: 12Registered: 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
 _jum Messages: 515Registered: February 2008 Senior Member
Yepp, you found 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
 pmreddy.mahi@gmail.com Messages: 12Registered: 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
 pmreddy.mahi@gmail.com Messages: 12Registered: 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
 ThomasG Messages: 3202Registered: 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
 pmreddy.mahi@gmail.com Messages: 12Registered: 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: Wed Aug 16 14:46:52 CDT 2017

Total time taken to generate the page: 0.09769 seconds