Hi
I did a report using 5 tables and when the functional analyst Was testing show to Another analyst (It's not me) and Another analyst Said that it did not return data because I was using LEFT join and there were empty tables, and that I should use a full join And in fact when he put data in one of the tables returned to work
I did a test below, where I create 3 tables, one of them always contains records the second has also, but the third does not contain and brought records correctly even if there is no data in the third
DROP TABLE MYTABLE01 CASCADE CONSTRAINTS;
DROP TABLE MYTABLE02 CASCADE CONSTRAINTS;
DROP TABLE MYTABLE03 CASCADE CONSTRAINTS;
CREATE TABLE MYTABLE01
( ID NUMBER,
COLUMN01 VARCHAR2(10 BYTE));
CREATE UNIQUE INDEX MYTABLE01_PK ON MYTABLE01 (ID);
ALTER TABLE MYTABLE01 ADD (
CONSTRAINT MYTABLE01_PK
PRIMARY KEY
(ID)
USING INDEX MYTABLE01_PK
ENABLE VALIDATE);
CREATE TABLE MYTABLE02(
ID NUMBER,
TAB01_FK NUMBER NOT NULL,
COLUMN02 VARCHAR2(10 BYTE)
);
CREATE UNIQUE INDEX MYTABLE02_PK ON MYTABLE02 (ID);
ALTER TABLE MYTABLE02 ADD (
CONSTRAINT MYTABLE02_PK
PRIMARY KEY
(ID)
USING INDEX MYTABLE02_PK
ENABLE VALIDATE);
ALTER TABLE MYTABLE02 ADD (
FOREIGN KEY (TAB01_FK)
REFERENCES MYTABLE01 (ID)
ENABLE VALIDATE);
CREATE TABLE MYTABLE03 ( ID NUMBER,
TAB01_FK NUMBER,
COLUMN03 VARCHAR2(10 BYTE));
CREATE UNIQUE INDEX MYTABLE03_PK ON MYTABLE03 (ID);
ALTER TABLE MYTABLE03 ADD (
CONSTRAINT MYTABLE03_PK
PRIMARY KEY
(ID)
USING INDEX MYTABLE03_PK
ENABLE VALIDATE);
ALTER TABLE MYTABLE03 ADD (
FOREIGN KEY (TAB01_FK)
REFERENCES MYTABLE01 (ID)
ENABLE VALIDATE);
See the data entry and query
INSERT INTO MYTABLE01 VALUES (1,'TEST01');
INSERT INTO MYTABLE01 VALUES (2,'TEST02');
INSERT INTO MYTABLE01 VALUES (3,'TEST03');
INSERT INTO MYTABLE02 VALUES (1,1,'XXXXX');
INSERT INTO MYTABLE02 VALUES (2,1,'YYYYY');
INSERT INTO MYTABLE02 VALUES (3,2,'AAAA');
SELECT *
FROM MYTABLE01 T1,
MYTABLE02 T2,
MYTABLE03 T3
WHERE T1.ID = T2.TAB01_FK(+)
AND T1.ID = T3.TAB01_FK(+)
I would like to know what may be happening that does not return data when one of the tables is empty on the functional analyst's test?
using
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Message was edited by: muttleychess translate correctly was in portuguese