Home » SQL & PL/SQL » SQL & PL/SQL » a column may not be outer-joined to a subquery
| a column may not be outer-joined to a subquery [message #644205] |
Fri, 30 October 2015 04:54  |
 |
javon13
Messages: 17
Registered: October 2015
|
Junior Member |
|
|
I have some special select which works until I do insert..select.
When I run insert..select it gives me:
SQL Error: ORA-01799: "a column may not be outer-joined to a subquery"
The truth is, I'm short with ideas how to repair it and why it works as plain select but not as insert..select.
It selects data from table A, inner join with table B. On top of that it left join a table with values for translation of values from table B
Here:
--SQL--
insert into TARGET_TABLE (
ID,
NAM1,
NAM2,
ADR1,
DPT,
RETURN_CODE1,
RETURN_CODE2
)
select
TAB_A.ID ID,
TAB_A.NAM1 NAM1,
TAB_A.NAM2 NAM2,
TAB_A.ADR1 ADR1,
TAB_B.DPT DPT,
VALTB.RETURN_CODE1 RETURN_CODE1,
VALTB.RETURN_CODE2 RETURN_CODE2
FROM TAB_A
INNER JOIN TAB_B
ON TAB_A.ID = TAB_B.NAME
LEFT JOIN ( --THIS IS BEGINNING OF PROBLEMATIC PART
SELECT
NVL(VALTB1.RETURN_CODE1, (SELECT RETURN_CODE1 FROM VAL_TABLE WHERE PAR2='*' AND PAR3='*' AND PAR1='*')) RETURN_CODE1,
NVL(VALTB1.RETURN_CODE2, (SELECT RETURN_CODE2 FROM VAL_TABLE WHERE PAR2='*' AND PAR3='*' AND PAR1='*')) RETURN_CODE2,
VALTB1.PAR1, VALTB1.PAR2, VALTB1.PAR3
from VAL_TABLE VALTB1
) VALTB
on (case when TAB_B.PAR1 in (select VALTB2.PAR1 from VAL_TABLE VALTB2)
then TAB_B.PAR1
else '*'
end
= VALTB.PAR1)
and (case when TAB_B.PAR2 in (select VALTB2.PAR2
from VAL_TABLE VALTB2
where VALTB2.PAR1 = (case when TAB_B.PAR1 in (select VALTB2.PAR1
from VAL_TABLE VALTB2)
then TAB_B.PAR1
else '*'
end))
then TAB_B.PAR2
else '*'
end
= VALTB.PAR2)
and (case when TAB_B.PAR3 in (select VALTB2.PAR3
from VAL_TABLE VALTB2
where VALTB2.PAR1 = (case when TAB_B.PAR1 in (select VALTB2.PAR1
from VAL_TABLE VALTB2)
then TAB_B.PAR1
else '*'
end)
and VALTB2.PAR2 = (case when TAB_B.PAR2 in (select VALTB2.PAR2
from VAL_TABLE VALTB2
where VALTB2.PAR1 = (case when TAB_B.PAR1 in (select VALTB2.PAR1
from VAL_TABLE VALTB2)
then TAB_B.PAR1
else '*'
end))
then TAB_B.PAR2
else '*'
end ))
then TAB_B.PAR3
else '*'
end
= VALTB.PAR3);
--SQL END--
Thanks a lot in advance for your hints. If you'd like to test it, I attached a file with complete environment: tables, data, select. Select is properly formatted in the file.
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read
-
Attachment: orafag.sql
(Size: 4.94KB, Downloaded 1448 times)
[Updated on: Fri, 30 October 2015 07:40] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: a column may not be outer-joined to a subquery [message #644220 is a reply to message #644219] |
Fri, 30 October 2015 11:28  |
|
javon13
Messages: 17
Registered: October 2015
|
Junior Member |
|
|
All right, I didn't want to make a mess in posts, but you're right.
Here it is:
create table TAB_A (
ID INTEGER NOT NULL,
NAM1 VARCHAR2(20) NOT NULL,
NAM2 VARCHAR2(40) NOT NULL,
ADR1 VARCHAR2(50) NOT NULL,
CONSTRAINT PK_TAB_A PRIMARY KEY (ID)
);
create table TAB_B (
NAME INTEGER NOT NULL,
DPT VARCHAR2(30) NOT NULL,
PAR1 VARCHAR2(10) NOT NULL,
PAR2 VARCHAR2(10) NOT NULL,
PAR3 VARCHAR2(10) NOT NULL,
CONSTRAINT PK_TAB_B PRIMARY KEY (NAME)
);
create table VAL_TABLE (
PAR1 VARCHAR2(10) NOT NULL,
PAR2 VARCHAR2(10) NOT NULL,
PAR3 VARCHAR2(10) NOT NULL,
RETURN_CODE1 INTEGER NOT NULL,
RETURN_CODE2 INTEGER NOT NULL,
SEQ INTEGER NOT NULL
);
CREATE UNIQUE INDEX UNQ_VAT_TABLE_PAR ON VAL_TABLE (PAR1, PAR2, PAR3);
create table TARGET_TABLE (
ID INTEGER NOT NULL,
NAM1 VARCHAR2(20) NOT NULL,
NAM2 VARCHAR2(40) NOT NULL,
ADR1 VARCHAR2(50) NOT NULL,
DPT VARCHAR2(30) NOT NULL,
RETURN_CODE1 INTEGER NOT NULL,
RETURN_CODE2 INTEGER NOT NULL,
CONSTRAINT PK_TARGET_TABLE PRIMARY KEY (ID)
);
INSERT ALL
INTO TAB_A (ID, NAM1, NAM2, ADR1) VALUES (1, 'Alpha', 'Smith', 'Ct')
INTO TAB_A (ID, NAM1, NAM2, ADR1) VALUES (2, 'Beta', 'Johnson', 'Nv')
INTO TAB_A (ID, NAM1, NAM2, ADR1) VALUES (3, 'Gamma', 'Newman', 'Ca')
INTO TAB_A (ID, NAM1, NAM2, ADR1) VALUES (4, 'Delta', 'Doe', 'Co')
INTO TAB_B (NAME, DPT, PAR1, PAR2, PAR3) VALUES (1, 'D100', 'S', 'D', 'BDL')
INTO TAB_B (NAME, DPT, PAR1, PAR2, PAR3) VALUES (2, 'D200', 'T', 'G', 'LAS')
INTO TAB_B (NAME, DPT, PAR1, PAR2, PAR3) VALUES (3, 'D300', 'R', 'F', 'LAX')
INTO TAB_B (NAME, DPT, PAR1, PAR2, PAR3) VALUES (4, 'D400', 'S', 'E', 'DEN')
INTO VAL_TABLE (PAR1, PAR2, PAR3, RETURN_CODE1, RETURN_CODE2, SEQ) VALUES ('S', '*', 'BDL', 1, 10, 1)
INTO VAL_TABLE (PAR1, PAR2, PAR3, RETURN_CODE1, RETURN_CODE2, SEQ) VALUES ('T', 'G', 'LAS', 2, 20, 1)
INTO VAL_TABLE (PAR1, PAR2, PAR3, RETURN_CODE1, RETURN_CODE2, SEQ) VALUES ('T', '*', 'LAX', 3, 30, 1)
INTO VAL_TABLE (PAR1, PAR2, PAR3, RETURN_CODE1, RETURN_CODE2, SEQ) VALUES ('*', 'D', 'DEN', 4, 40, 1)
INTO VAL_TABLE (PAR1, PAR2, PAR3, RETURN_CODE1, RETURN_CODE2, SEQ) VALUES ('*', 'E', '*', 5, 50, 1)
INTO VAL_TABLE (PAR1, PAR2, PAR3, RETURN_CODE1, RETURN_CODE2, SEQ) VALUES ('R', 'F', '*', 6, 60, 1)
INTO VAL_TABLE (PAR1, PAR2, PAR3, RETURN_CODE1, RETURN_CODE2, SEQ) VALUES ('*', '*', '*', 7, 70, 99)
SELECT 1 FROM DUAL;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jul 05 10:11:53 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
