logo
down
shadow

Oracle SQL join the same table


Oracle SQL join the same table

By : user2952938
Date : November 19 2020, 03:59 PM
it helps some times It looks like you want to pivot your result set, rather than join it to itself. Assuming you're on Oracle 11g or higher you can do this natively:
code :
select * from (
  select point, decode(alarm,0,'new','rec') NewRec, countAlarms, load_id
  from MyTable
)
pivot (
  sum(countAlarms) as alarms
  for (load_id) in (111113 as a, 111112 as b, 111111 as c)
)
order by 1, 2;
POINT  NEWREC   A_ALARMS   B_ALARMS   C_ALARMS
------ ------ ---------- ---------- ----------
point1 new          1200       1300       1300 
point1 rec          6000       6300       6300 
point2 new          1220                  1120 
point2 rec          3000                  3100 
point3 new          3220       1220       1220 
point3 rec          1000       1100       1100 
select * from (
  select point, decode(alarm,0,'new','rec') NewRec, countAlarms,
    dense_rank() over (partition by null order by load_id desc) as rnk
  from MyTable
)
pivot (
  sum(countAlarms) as alarms
  for (rnk) in (1 as a, 2 as b, 3 as c)
)
order by 1, 2;


Share : facebook icon twitter icon
Self join in SQL Oracle; Why does the right outer join not retain all the values of the table

Self join in SQL Oracle; Why does the right outer join not retain all the values of the table


By : steamyrayvaughn
Date : March 29 2020, 07:55 AM
To fix the issue you can do Right and Left joins perform the same function. What's different in your examples is the tables that you are SELECTing from.
These two queries:
code :
select j2.name,j2.department
from join_test j1 left join join_test j2
on(j1.department=j2.department and j1.name<>j2.name)
select j1.name,j1.department
from join_test j1 right join join_test j2
on(j1.department=j2.department and j1.name<>j2.name)
NAME    DEPARTMENT
stelios sa
andros  sa
stav    ba
mary    ba
(null)  (null)
(null)  (null)
NAME        DEPARTMENT   NAME       DEPT
stelios     sa           andros     sa
andros      sa           stelios    sa
stav        ba           mary       ba
mary        ba           stav       ba
(null)      (null)       antonia    la
(null)      (null)       lambros    ka
How to conditionally join a table function in Oracle SQL, which has a primary table row column as an argument, without e

How to conditionally join a table function in Oracle SQL, which has a primary table row column as an argument, without e


By : user3422163
Date : March 29 2020, 07:55 AM
it helps some times I have the following issue: Every row in my base table have a flag column ('flg' in the listing below) and an function argument column ('arg'). If in a row #N the flag is 'Y', then function has to be called; after, it shall return, let's say, a column (actually a bunch of them, but i'll simplify as much as i can). And finally, the row #N should transform into a sub-table - full join of a row #N and the column, returned by the function. If the flag is 'N' then a result for the row #N shall be as the row itself plus NULL in that "function return column". , For flag = "N" the function is not called if you conditionally join
code :
set serveroutput on
with base_table as
(
    select 'Shall invoke' col0, 'Y' flg, '2' arg from dual
    union
    select 'Shall not invoke' col0, 'N' flg, '0' arg from dual
)
select * from base_table t0
left join table( test_package.testFunc(t0.arg) ) t1 on (t0.flg = 'Y');
Package created.
Package body created.

COL0             FLG ARG COLUMN_VALUE
---------------- --- --- ------------
Shall invoke     Y   2              1
Shall invoke     Y   2              2
Shall not invoke N   0               

3 rows selected.
Function call, arg = 2
Query to join two tables by mapping third table without returning all records from third table in Oracle

Query to join two tables by mapping third table without returning all records from third table in Oracle


By : user3767285
Date : March 29 2020, 07:55 AM
help you fix your problem You need rows, where student's ID is present in at least one of tables MATHS, ENGLISH. These queries gives output you wanted:
code :
select id, s.name, m.Marks1, e.Marks2 
  from maths m 
    full join english e using (id)
    join student s using (id);
select s.id, s.name, m.Marks1, e.Marks2 
  from student s
    left join english e on e.id=s.id
    left join maths m on m.id = s.id
  where e.id is not null or m.id is not null
Counting customers from different countries, country table will not join to customer table (Oracle)

Counting customers from different countries, country table will not join to customer table (Oracle)


By : mukesh mishra
Date : March 29 2020, 07:55 AM
This might help you You would need the following query (presuming there is a column named customer_id) :
code :
SELECT country.country, count(customer_details.customer_id) AS customer_count
FROM country
INNER JOIN customer_details
ON customer_details.country_id = country.country_id
GROUP by country.country
ORDER by country.country;
Convert Oracle (Cross Join?) to Netezza when using comma separated table list instead of JOIN keywords

Convert Oracle (Cross Join?) to Netezza when using comma separated table list instead of JOIN keywords


By : marscat
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further Had to use explicit columns in Netezza when doing a CROSS JOIN. Using SELECT * throws the error as indicated in my question EDIT. Also had to escape the '%' character by escaping nothing. Thank you a_horse_with_no_name. Cheers! "Where everybody knows your name." ;-)
code :
select A.CODE, B.CODE, LOWER(A.DIM), LOWER(B.DIM)
FROM TABLE_A A
cross join TABLE_A B
WHERE A.PATH LIKE '\A\A\A%' ESCAPE '' AND A.X = 'Y'
AND B.PATH LIKE '\B\B\B%' ESCAPE '' AND B.X = 'Y'
shadow
Privacy Policy - Terms - Contact Us © ourworld-yourmove.org