I need to compare prices coming from 3 sources (27, 2, 55) from owners column. i am not sure if i should use select sub query or left join ?
Desired Result
2
-
1Please, check this articles: Why not upload images of code/errors when asking a question? How to create a Minimal, Reproducible Example and How much research effort is expected of Stack Overflow users?.– astentxCommented Jun 27, 2021 at 16:35
-
1Does this answer your question? Oracle SQL pivot query– astentxCommented Jun 27, 2021 at 16:36
Add a comment
|
1 Answer
Looks like a pivot.
Sample data in lines #1 - 10; query you might need begins at line #12.
SQL> with test (id_value, as_of, timezone, price, owner) as
2 -- sample data
3 (select 'EEM.A', date '2021-06-25', 'J1530', 55.04, 55 from dual union all
4 select 'EEM.A', date '2021-06-25', 'J1530', 55.04, 27 from dual union all
5 select 'EEM.A', date '2021-06-25', 'J1530', 55.04, 2 from dual union all
6 --
7 select 'AMX.N', date '2021-06-25', 'J1530', 15.4, 55 from dual union all
8 select 'AMX.N', date '2021-06-25', 'J1530', 15.4, 27 from dual union all
9 select 'AMX.N', date '2021-06-25', 'J1530', 15.4, 2 from dual
10 )
11 -- query begins here
12 select *
13 from test
14 pivot (max(price)
15 for owner in (55, 27, 2)
16 );
ID_VA AS_OF TIMEZ 55 27 2
----- ---------- ----- ---------- ---------- ----------
AMX.N 06/25/2021 J1530 15,4 15,4 15,4
EEM.A 06/25/2021 J1530 55,04 55,04 55,04
SQL>
answered Jun 27, 2021 at 16:12
-
i just tried this on golden 6 software, do i need to start off from the first line - with test ?– NainaCommented Jun 27, 2021 at 16:23
-
Never hear of "golden 6 software", but - no, you'd start off from line #12 (as I wrote, but you either didn't read or didn't understand what I was saying). Commented Jun 27, 2021 at 16:25
-
select * from quotes_mxsequities pivot (max(price) for owner in (55, 27, 2) ) where timezone = 'J1530' and asof = '25Jun2021';– NainaCommented Jun 27, 2021 at 16:30
-
-
As you can see, I am - with sample data you posted. What I can tell - based on what you posted as a comment - is that ASOF looks strange. If it is supposed to be a DATE value then try with
and asof = date '2021-06-25'Commented Jun 27, 2021 at 19:56