Session 01 - DP89

View All Public Scripts Login to Run Script

Script Name Session 01 - DP89
Visibility Unlisted - anyone with the share link can access
Description -- I want to find the sku_no values from this list that do not have any ven_type='P' records
Area Oracle 12c
Contributor dhruv842002@yahoo.com
Created Wednesday January 20, 2021

Statement 1

CREATE TABLE vendor_item( 
    site_no NUMBER(10) NOT NULL, 
    sku_no VARCHAR2(50) NOT NULL, 
    vendor_id VARCHAR2(50) NOT NULL, 
    ven_type VARCHAR2(1) NOT NULL 
)

Table created.

Statement 2

CREATE TABLE vendor_item( 
    site_no NUMBER(10) NOT NULL, 
    sku_no VARCHAR2(50) NOT NULL, 
    vendor_id VARCHAR2(50) NOT NULL, 
    ven_type VARCHAR2(1) NOT NULL 
)

ORA-00955: name is already used by an existing object

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

Statement 3
```
select * from vendor_item
```
```
no data found
```
Statement 4
```
select * from vendor_item
```
```
no data found
```

Statement 5

insert into vendor_item  
values  
(5121, 11172275, 'OWAJ', 'P')

1 row(s) inserted.

Statement 6

select * from vendor_item


SITE_NO SKU_NO VENDOR_ID VEN_TYPE
5121 11172275 OWAJ P

SITE_NO	SKU_NO	VENDOR_ID	VEN_TYPE
5121	11172275	OWAJ	P

Statement 7

insert into vendor_item  
values  
(5121, 11519912, 'OWAJ', 'P')

1 row(s) inserted.

Statement 8

insert into vendor_item  
values  
(5121, 11445716, 'OWAJ', 'P')

1 row(s) inserted.

Statement 9

insert into vendor_item  
values  
(5121, 11172275, '41000010J', 'S')

1 row(s) inserted.

Statement 10

insert into vendor_item  
values  
(5121, 11519912, '41000010J', 'S')

1 row(s) inserted.

Statement 11

insert into vendor_item  
values  
(5121, 11445716, '41000020J', 'S')

1 row(s) inserted.

Statement 12
```
commit
```
```
Statement processed.
```

Statement 13

select * from vendor_item


SITE_NO SKU_NO VENDOR_ID VEN_TYPE
5121 11519912 41000010J S
5121 11445716 41000020J S
5121 11172275 41000010J S
5121 11519912 OWAJ P
5121 11172275 OWAJ P
5121 11445716 OWAJ P

6 rows selected.

SITE_NO	SKU_NO	VENDOR_ID	VEN_TYPE
5121	11519912	41000010J	S
5121	11445716	41000020J	S
5121	11172275	41000010J	S
5121	11519912	OWAJ	P
5121	11172275	OWAJ	P
5121	11445716	OWAJ	P

Statement 14

insert into vendor_item  
values  
(5121, 11171614, '41033300J', 'P')

1 row(s) inserted.

Statement 15

insert into vendor_item  
values  
(5121, 11171614, 'OWAJ', 'S')

1 row(s) inserted.

Statement 16

insert into vendor_item  
values  
(5121, 11171618, '41033300J', 'P')

1 row(s) inserted.

Statement 17

insert into vendor_item  
values  
(5121, 11171618, 'OWAJ', 'S')

1 row(s) inserted.

Statement 18
```
commit
```
```
Statement processed.
```

Statement 19

insert into vendor_item  
values  
(5121, 11170263, '41000020J', 'S')

1 row(s) inserted.

Statement 20

insert into vendor_item  
values  
(5121, 11170263, '41000010J', 'S')

1 row(s) inserted.

Statement 21

insert into vendor_item  
values  
(5121, 11170263, 'OWAJ', 'S')

1 row(s) inserted.

Statement 22

insert into vendor_item  
values  
(5121, 10197056, '41000050J', 'S')

1 row(s) inserted.

Statement 23

insert into vendor_item  
values  
(5121, 10197056, 'OWAJ', 'S')

1 row(s) inserted.

Statement 24

insert into vendor_item  
values  
(5121, 11520369, '41002350J', 'S')

1 row(s) inserted.

Statement 25
```
41002350J
```
```
Invalid statement
```

Statement 26

insert into vendor_item  
values  
(5121, 11448384, '41004350J', 'S')

1 row(s) inserted.

Statement 27
```
commit
```
```
Statement processed.
```

Statement 28

select * from vendor_item


SITE_NO SKU_NO VENDOR_ID VEN_TYPE
5121 11519912 41000010J S
5121 10197056 OWAJ S
5121 11445716 41000020J S
5121 11170263 OWAJ S
5121 10197056 41000050J S
5121 11172275 41000010J S
5121 11171614 41033300J P
5121 11448384 41004350J S
5121 11519912 OWAJ P
5121 11171614 OWAJ S
5121 11172275 OWAJ P
5121 11445716 OWAJ P
5121 11171618 41033300J P
5121 11171618 OWAJ S
5121 11170263 41000020J S
5121 11170263 41000010J S
5121 11520369 41002350J S

17 rows selected.

SITE_NO	SKU_NO	VENDOR_ID	VEN_TYPE
5121	11519912	41000010J	S
5121	10197056	OWAJ	S
5121	11445716	41000020J	S
5121	11170263	OWAJ	S
5121	10197056	41000050J	S
5121	11172275	41000010J	S
5121	11171614	41033300J	P
5121	11448384	41004350J	S
5121	11519912	OWAJ	P
5121	11171614	OWAJ	S
5121	11172275	OWAJ	P
5121	11445716	OWAJ	P
5121	11171618	41033300J	P
5121	11171618	OWAJ	S
5121	11170263	41000020J	S
5121	11170263	41000010J	S
5121	11520369	41002350J	S

Additional Information

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