SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

select statement return null when it should not

Eslam_ElbyalyJun 1 2016 — edited Jun 16 2016

HI ,

Oracle db 10g rel2 , windows 7 ,

CREATE TABLE CURRENCIES
(
  ID           NUMBER,
  NAME         VARCHAR2(50 BYTE),
  DESCRIPTION  VARCHAR2(100 BYTE),
  ACTIVE       NUMBER(1)
);


ALTER TABLE CURRENCIES ADD (
  CONSTRAINT CURRENCIES_PK
 PRIMARY KEY
 (ID));

CREATE TABLE SUPPLIERS_AND_CUSTOMERS
(
  ID            NUMBER CONSTRAINT NNC_CUSTOMERSV1_CUSTOMER_NO NOT NULL,
  NAME          VARCHAR2(100 BYTE),
  WEBSITE       VARCHAR2(50 BYTE),
  NOTES         VARCHAR2(200 BYTE),
  FIRM          VARCHAR2(70 BYTE),
  TYPE          NUMBER,
  ACCOUNT_TYPE  NUMBER,
  EMAIL         VARCHAR2(90 BYTE)
);

COMMENT ON COLUMN SUPPLIERS_AND_CUSTOMERS.ACCOUNT_TYPE IS 'CUSTOMER , SUPPLIER , CUSTOMER_AND_SUPPLIER = 1 ,2 ,3 RESPECTIVELY .';

COMMENT ON COLUMN SUPPLIERS_AND_CUSTOMERS.TYPE IS '??? ?????? ?? ??????, ???? ????? , ???? ???? , ????';


ALTER TABLE SUPPLIERS_AND_CUSTOMERS ADD (
  CONSTRAINT SUPPLIER_PK
 PRIMARY KEY
 (ID));

CREATE TABLE SALES_ORDER
(
  ORDER_ID             NUMBER,
  CUSTOMER_ID          NUMBER,
  SALE_DATE            DATE,
  PAYMENT_METHOD       VARCHAR2(1 BYTE),
  STATUS               VARCHAR2(1 BYTE),
  DISCOUNT             NUMBER,
  NOTES                VARCHAR2(100 BYTE),
  INSTALLMENTS_NUMBER  NUMBER,
  DISCOUNT_UNIT        NUMBER,
  CREATED_BY           VARCHAR2(50 BYTE),
  MODIFIED_BY          VARCHAR2(50 BYTE),
  MODIFY_DATE          DATE,
  REP                  VARCHAR2(50 BYTE),
  SALE_CUR             NUMBER
);

COMMENT ON COLUMN SALES_ORDER.REP IS 'Representative';


ALTER TABLE SALES_ORDER ADD (
  CONSTRAINT SALES_ORDER_PK
 PRIMARY KEY
 (ORDER_ID));


ALTER TABLE SALES_ORDER ADD (
  CONSTRAINT SO_CURR_SALECUR_FK 
 FOREIGN KEY (SALE_CUR) 
 REFERENCES CURRENCIES (ID));

CREATE TABLE SALES_ORDER_DETAIL
(
  ORDER_ID        NUMBER,
  UNIT_PRICE      NUMBER(5,2),
  DISCOUNT        NUMBER(5,2),
  BONUS           NUMBER,
  EXPIRY_DATE     DATE,
  QTY             NUMBER,
  SALE_UNIT_CODE  NUMBER,
  SERIAL          NUMBER,
  SALE_TAX        NUMBER,
  DISCOUNT_UNIT   NUMBER,
  ITEM_ID         NUMBER,
  CREATED_BY      VARCHAR2(50 BYTE),
  MODIFIED_BY     VARCHAR2(50 BYTE),
  MODIFY_DATE     DATE,
  CREATE_DATE     DATE
);


ALTER TABLE SALES_ORDER_DETAIL ADD (
  CONSTRAINT ORDRID_ITMID_EXPDATE_CPK
 PRIMARY KEY
 (ORDER_ID, ITEM_ID, EXPIRY_DATE));


ALTER TABLE SALES_ORDER_DETAIL ADD (
  CONSTRAINT SOD_SO_ORDERID_FK 
 FOREIGN KEY (ORDER_ID) 
 REFERENCES SALES_ORDER (ORDER_ID)
    ON DELETE CASCADE);

CREATE TABLE SALES_ORDER_PAYMENTS
(
  PAYMENT_ID      NUMBER,
  ORDER_ID        NUMBER,
  PAYMENT_DATE    DATE,
  PAYMENT_AMOUNT  NUMBER,
  CREATED_BY      VARCHAR2(50 BYTE),
  MODIFIED_BY     VARCHAR2(50 BYTE),
  MODIFY_DATE     DATE,
  ADVANCE         NUMBER(1),
  NOTES           VARCHAR2(200 BYTE)
);


ALTER TABLE SALES_ORDER_PAYMENTS ADD (
  CONSTRAINT PO_PAYMENTS_PK
 PRIMARY KEY
 (PAYMENT_ID));


ALTER TABLE SALES_ORDER_PAYMENTS ADD (
  CONSTRAINT SO_SOP_ORDERID_FK 
 FOREIGN KEY (ORDER_ID) 
 REFERENCES SALES_ORDER (ORDER_ID));

CREATE TABLE SALES_RETURN
(
  ORDER_ID          NUMBER,
  CUSTOMER_ID       NUMBER,
  SALE_DATE         DATE,
  NOTES             VARCHAR2(100 BYTE),
  CREATED_BY        VARCHAR2(50 BYTE),
  MODIFIED_BY       VARCHAR2(50 BYTE),
  MODIFY_DATE       DATE,
  RETURN_DATE       DATE,
  RETURN_REASON_ID  NUMBER,
  SALES_RETURN_ID   NUMBER,
  STATUS            VARCHAR2(1 BYTE),
  REP               VARCHAR2(90 BYTE),
  CUR               NUMBER
);

COMMENT ON COLUMN SALES_RETURN.STATUS IS 'TEMP OR PERMANENT';


ALTER TABLE SALES_RETURN ADD (
  CONSTRAINT SALES_RETURN_PK
 PRIMARY KEY
 (SALES_RETURN_ID));


ALTER TABLE SALES_RETURN ADD (
  CONSTRAINT SR_SO_ORDERID_FK 
 FOREIGN KEY (ORDER_ID) 
 REFERENCES SALES_ORDER (ORDER_ID));

ALTER TABLE SALES_RETURN ADD (
  CONSTRAINT SR_CUSTOMERID_FK 
 FOREIGN KEY (CUSTOMER_ID) 
 REFERENCES SUPPLIERS_AND_CUSTOMERS (ID));

ALTER TABLE SALES_RETURN ADD (
  CONSTRAINT SR_CUR_ID_FK 
 FOREIGN KEY (CUR) 
 REFERENCES CURRENCIES (ID));

CREATE TABLE SALES_RETURN_DETAIL
(
  ORDER_ID          NUMBER,
  EXPIRY_DATE       DATE,
  QTY               NUMBER,
  RETURN_UNIT_CODE  NUMBER,
  SERIAL            NUMBER,
  ITEM_ID           NUMBER,
  CREATED_BY        VARCHAR2(50 BYTE),
  MODIFIED_BY       VARCHAR2(50 BYTE),
  MODIFY_DATE       DATE,
  CREATE_DATE       DATE,
  SALES_RETURN_ID   NUMBER,
  UNIT_PRICE        NUMBER
);


ALTER TABLE SALES_RETURN_DETAIL ADD (
  CONSTRAINT SRD_SR_ORDER_ITM_EXP
 PRIMARY KEY
 (SALES_RETURN_ID, ORDER_ID, ITEM_ID, EXPIRY_DATE));


ALTER TABLE SALES_RETURN_DETAIL ADD (
  CONSTRAINT SR_SRD_FK 
 FOREIGN KEY (SALES_RETURN_ID) 
 REFERENCES SALES_RETURN (SALES_RETURN_ID)
    ON DELETE CASCADE);

SET DEFINE OFF;

Insert into CURRENCIES

(ID, NAME, DESCRIPTION, ACTIVE)

Values

(2, 'L.E', 'جنيه مصرى', 0);

Insert into CURRENCIES

(ID, NAME, DESCRIPTION, ACTIVE)

Values

(3, '$', NULL, 0);

Insert into CURRENCIES

(ID, NAME, DESCRIPTION, ACTIVE)

Values

(4, 'S.R', NULL, 1);

COMMIT;

SET DEFINE OFF;

Insert into SUPPLIERS_AND_CUSTOMERS