SO 66808088

View All Public Scripts Login to Run Script

Script Name SO 66808088
Visibility Unlisted - anyone with the share link can access
Description SO 66808088
Contributor Justin Cave
Created Thursday March 25, 2021

Statement 1

create table organizations ( 
  org_name varchar2(20), 
  org_status varchar2(10), 
  product_id integer  
)

Table created.

Statement 2

create table products ( 
  product_id integer, 
  product_name varchar2(10) 
)

Table created.

Statement 3

insert into organizations values( 'Org 1', 'Active', 1 )

1 row(s) inserted.

Statement 4

insert into organizations values( 'Org 2', 'Legacy', 1 )

1 row(s) inserted.

Statement 5

insert into organizations values( 'Org 3', 'Legacy', 2 )

1 row(s) inserted.

Statement 6

insert into organizations values( 'Org 4', 'Active', 3 )

1 row(s) inserted.

Statement 7

insert into products values( 1, 'Product 1' )

1 row(s) inserted.

Statement 8

insert into products values( 2, 'Product 2' )

1 row(s) inserted.

Statement 9

insert into products values( 3, 'Product 3' )

1 row(s) inserted.

Statement 10

select o.org_id, o.org_status, p.product_id, p.product_name, 
       row_number() over (partition by p.product_id  
                              order by case when o.org_status = 'Active' 
                                            then 1 
                                            else 2 
                                        end desc) rn 
  from products p 
       join organizations o  
         on p.product_id = o.product_id

ORA-00904: "O"."ORG_ID": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 11
```
drop table organizations
```
```
Table dropped.
```
Statement 12
```
drop table products
```
```
Table dropped.
```

Statement 13

create table organizations ( 
  org_name varchar2(20), 
  org_status varchar2(10), 
  product_id integer  
)

Table created.

Statement 14

create table products ( 
  product_id integer, 
  product_name varchar2(10) 
)

Table created.

Statement 15

insert into organizations values( 'Org 1', 'Active', 1 )

1 row(s) inserted.

Statement 16

insert into organizations values( 'Org 2', 'Legacy', 1 )

1 row(s) inserted.

Statement 17

insert into organizations values( 'Org 3', 'Legacy', 2 )

1 row(s) inserted.

Statement 18

insert into organizations values( 'Org 4', 'Active', 3 )

1 row(s) inserted.

Statement 19

insert into products values( 1, 'Product 1' )

1 row(s) inserted.

Statement 20

insert into products values( 2, 'Product 2' )

1 row(s) inserted.

Statement 21

insert into products values( 3, 'Product 3' )

1 row(s) inserted.

Statement 22

select o.org_name, o.org_status, p.product_id, p.product_name, 
       row_number() over (partition by p.product_id  
                              order by case when o.org_status = 'Active' 
                                            then 1 
                                            else 2 
                                        end desc) rn 
  from products p 
       join organizations o  
         on p.product_id = o.product_id


ORG_NAME ORG_STATUS PRODUCT_ID PRODUCT_NAME RN
Org 2 Legacy 1 Product 1 1
Org 1 Active 1 Product 1 2
Org 3 Legacy 2 Product 2 1
Org 4 Active 3 Product 3 1

4 rows selected.

Statement 23
```
drop table organizations
```
```
Table dropped.
```
Statement 24
```
drop table products
```
```
Table dropped.
```

ORG_NAME	ORG_STATUS	PRODUCT_ID	PRODUCT_NAME	RN
Org 2	Legacy	1	Product 1	1
Org 1	Active	1	Product 1	2
Org 3	Legacy	2	Product 2	1
Org 4	Active	3	Product 3	1

Statement 25

create table organizations ( 
  org_name varchar2(20), 
  org_status varchar2(10), 
  product_id integer  
)

Table created.

Statement 26

create table products ( 
  product_id integer, 
  product_name varchar2(10) 
)

Table created.

Statement 27

insert into organizations values( 'Org 1', 'Active', 1 )

1 row(s) inserted.

Statement 28

insert into organizations values( 'Org 2', 'Legacy', 1 )

1 row(s) inserted.

Statement 29

insert into organizations values( 'Org 3', 'Legacy', 2 )

1 row(s) inserted.

Statement 30

insert into organizations values( 'Org 4', 'Active', 3 )

1 row(s) inserted.

Statement 31

insert into products values( 1, 'Product 1' )

1 row(s) inserted.

Statement 32

insert into products values( 2, 'Product 2' )

1 row(s) inserted.

Statement 33

insert into products values( 3, 'Product 3' )

1 row(s) inserted.

Statement 34

select o.org_name, o.org_status, p.product_id, p.product_name, 
       row_number() over (partition by p.product_id  
                              order by case when o.org_status = 'Active' 
                                            then 1 
                                            else 2 
                                        end asc) rn 
  from products p 
       join organizations o  
         on p.product_id = o.product_id


ORG_NAME ORG_STATUS PRODUCT_ID PRODUCT_NAME RN
Org 1 Active 1 Product 1 1
Org 2 Legacy 1 Product 1 2
Org 3 Legacy 2 Product 2 1
Org 4 Active 3 Product 3 1

4 rows selected.

ORG_NAME	ORG_STATUS	PRODUCT_ID	PRODUCT_NAME	RN
Org 1	Active	1	Product 1	1
Org 2	Legacy	1	Product 1	2
Org 3	Legacy	2	Product 2	1
Org 4	Active	3	Product 3	1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database