If your query returns a single value (which it seems to since your example doesn't like that record to anything in the table you're merging in to or the data you are using to perform the merge) why wouldn't you just select that in to a variable outside the MERGE statement?
SELECT
ts.VALUE
INTO
l_variable
FROM term_scope ts
JOIN campus_term ct ...
Thread: Merge with subquery causes ORA-38101 Invalid column in the INSERT VALUES C  |
|||||||||||||
|
Started 1 month, 1 week ago by Abc123Xyz
The following is a somewhat shortened version of an SQL statement that is causing the error.
MERGE INTO applicant_matches m_table USING
(
SELECT DISTINCT
s.student_id,
a.term_scope_id,
a.first_name,
a.last_name,
a.ssn_visa,
a.birth_date,
...
) m_list ON
(m_list.student_id = m_table.student_id ...
|
|
||||||||||||
|
|||||||||||||
Other posts in this thread:
Tubby replied 1 month, 1 week ago
Size: 3,674 bytes
Customize: 
user142857 replied 1 month, 1 week ago
If you change the subquery so that the last condition is specified in the JOIN rather than in the WHERE, does it work?
SELECT 'A'
FROM term_scope ts
JOIN campus_term ct
ON ts.campus_term_id=ct.campus_term_id
AND ts.term_scope_id = 5
Size: 593 bytes
Customize:
Abc123Xyz replied 1 month, 1 week ago
Thanks. I tried to simplify to figure out what was going on because I was wondering if it wouldn't let you do a subquery with a value you were merging with, so I just wanted to rule that out. Originally it looked more like this:
MERGE INTO applicant_matches m_table USING
(
SELECT DISTINCT
s.student_id,
a.term_scope_id,
a....
Size: 2,347 bytes
Customize:
user142857 replied 1 month, 1 week ago
just doing some local tests here, it doesn't like the condition when specified as part of the WHERE clause. It works if the condition is made part of the JOIN.
It does work if you use the old Oracle join syntax:
SELECT 'A'
FROM term_scope ts,
campus_term ct
WHERE ts.campus_term_id=ct.campus_term_id
AND ts.term_scope_id = m_list.term_...
Size: 850 bytes
Customize:
Abc123Xyz replied 1 month, 1 week ago
user142857,
that works with the constant, but then when I try to change it back to
(SELECT
'P'
FROM watch_term_scope ts
JOIN watch_campus_term ct ON ts.campus_term_id=ct.campus_term_id AND ts.term_scope_id = m_list.term_scope_id
)
I get:
SQL Error: ORA-00904: "M_LIST"."TERM_SCOPE_ID": invalid identifier
In the...
Size: 832 bytes
Customize:
user142857 replied 1 month, 1 week ago
try it using the old join syntax
select 'x'
from t1, t2
where t1.col = t2.col
and t2.other_col = using_set.other_col
Size: 397 bytes
Customize:
Abc123Xyz replied 1 month, 1 week ago
well, that seems to work.
Size: 264 bytes
Customize:
user142857 replied 1 month, 1 week ago
Seems like a good candidate for a bug report.
Size: 284 bytes
Customize:
user12194376 replied 1 month, 1 week ago
Emicsoft WMV Converter for Mac is devoted to converting video formats like MPEG, MOV, AVI, MP4, FLV, 3GP and other video formats to WMV Mac, apart from multiple input formats supported, you are allowed to convert WMV to other formats on Mac OS X. Not only video files conversion supported, the WMV Converter Mac also supports extracting audio track from WMV ...
Size: 2,888 bytes
Customize:
|
Top contributing authorsRelated threads on "www.oracle.com":Related threads on other sites: |
||||||||||||
Thread profile page for "Merge with subquery causes ORA-38101 Invalid column in the INSERT VALUES C" on http://www.oracle.com.
This report page is a snippet summary view from a single thread "Merge with subquery causes ORA-38101 Invalid column in the INSERT VALUES C", located on the Message Board at http://www.oracle.com.
This thread profile page shows the thread statistics for: Total Authors, Total Thread Posts, and Thread Activity
Home |
About Us |
Submit Your Site |
Update Your Site |
Get Search For Your Site |
Partners |
Contact |
Search Plugin |
Blog |
 |
Last Searches | Top Searches |
Designated brands and trademarks are property of their respective owners.
Use of this website constitutes acceptance of BoardReader's User Agreement and Privacy Policy.