Oracle left outer join query

select a,last_note_user,c,d,iso_src from X,Y,Z left outer join W ON (W.last_note_user=Z.userid AND W.user_ten=Y.iso_src)

when I am executing the above query it is giving me the error "Y.iso_src" is a invalid identifier.However,Y.iso_src is a valid identifier and it exist in the system table. Is something wrong with the above left outer join?? Please help me out!!!


You cannot mix JOIN syntaxes in Oracle this way.

A JOIN condition can only refer tables previously joined using ANSI JOIN syntax.

Use this:

SELECT a, last_note_user, c, d, iso_src
ON W.last_note_user = Z.userid
AND W.user_ten = Y.iso_src

It's probably a good habit to not mix ANSI and non-ANSI join syntax as the other responses indicate. However, it is technically possible, by isolating the non-ANSI joins into a subquery:

create table X as (select dummy a, dummy c, dummy d from dual);

create table Y as (select dummy iso_src from dual);

create table Z as (select dummy userid from dual);

create table W as (select dummy last_note_user, dummy user_ten from dual);

select a,last_note_user,c,d,iso_src
from (select a, c, d, iso_src, userid FROM X,Y,Z) xyz
left outer join W
ON (W.last_note_user=xyz.userid AND W.user_ten=xyz.iso_src);

- -------------- - - -------

1 rows selected

Category:sql Time:2009-07-22 Views:1
Tags: sql oracle

Related post

  • Join query or subquery 2009-03-18

    Are there rules of thumb for developers when to use join instead of subquery or are they the same. --------------Solutions------------- Depends on RDBMS. You should compare execution plans for both queries. In my experience with Oracle 10 and 11, exe

  • Symfony Propel: Join Query Across different Database 2009-08-28

    I have two tables that should be joined together by a foreign key relationship, unfortunately they are separate into two different database. My question is that is it possible for me to create a Criteria thingy that allows me to create cross database

  • Joining tables in Oracle (multiple outer joins) 2011-08-25

    I am trying to convert an Informix query to Oracle: The Informix query looks like this: SELECT r.aa,,,, ud.dd, g.attr FROM tab1 u, tab2 ud, OUTER (tab3 a, tab4 n, tab5 nd, tab6 r, OUTER (tab7 g, tab8 atr)) WHERE r.xx = n.xx AND n.nas =

  • How can I create a MySQL JOIN query for only selecting the rows in one table where a certain number of references to that row exist in another table? 2008-10-06

    I have two tables in my database, called ratings and movies. Ratings: | id | movie_id | rating | Movies: | id | title | A typical movie record might be like this: | 4 | Cloverfield (2008) | and there may be several rating records for Cloverfield, lik

  • How to create a dataview In Sharepoint with data from a join query? 2009-01-22

    I have 3 Lists in Sharepoint. I want to create a dataview that is a join of 3 tables. Table1 is joined with Table2 on FieldA Table 2 is joined to Table3 on FieldB Table1 has duplicate values in FieldA so I need to only return one value to join with T

  • a JOIN query edited so that a field that has unique values 2009-05-06

    I have a JOIN query that pulls the last 6 "topic" records (the records with the highest topic_ids that are have a topic_status of 0, i.e. it's not spam): SELECT topic_title, topic_slug, meta_value, topic_poster FROM `folio_topics` as Topics INNER JOI

  • Django .."join" query? 2009-07-31

    guys, how or where is the "join" query in Django? i think that Django dont have "join"..but how ill make join? Thanks --------------Solutions------------- If you're using models, the select_related method will return the object for any foreign keys y

  • Which MySQL JOIN query is more efficient? 2009-11-12

    Given the following table structure: CREATE TABLE user ( uid INT(11) auto_increment, name VARCHAR(200), PRIMARY KEY(uid) ); CREATE TABLE user_profile( uid INT(11), address VARCHAR(200), PRIMARY KEY(uid), INDEX(address) ); Which join query is more eff

  • C# Display Join Query in DataGridView at designTime using TableAdapter, etc 2009-12-04

    I have a DataGridView I also have some tableAdapters (groupTableAdapter, userTableAdapter) generated from sqlserver database. I have created a JOIN query in userTableAdapter that shows users with their correspoding groupname. Of course, I've got the

  • mysql join query using like? 2009-12-18

    I have been trying to get this working for quite a while now but it just doesn't seem to work, maybe it is is not even possible, what i am wanting to do is to perform a mysql join query using like, such as this example i found... SELECT * FROM Table1

  • MS-Access: What could cause one form with a join query to load right and another not? 2010-03-11

    Late breaking news! If I manually create the new record with SQL and then open the form in edit mode using the code below, it works. I would still like to know what the problem was before, but at least I can get on with my application now. Form1 Form

  • How to deal with the Hibernate hql multi-join query result in an Object-Oriented Way? 2010-03-12

    How to deal with the Hibernate hql multi-join query result in an Object-Oriented Way? As I see it returns a list of Objects. yes, it is tricky and only you who write the query know what should the query return (what objects). But are there ways to si

  • (Oracle Performance) Will a query based on a view limit the view using the where clause? 2010-03-22

    In Oracle (10g), when I use a View (not Materialized View), does Oracle take into account the where clause when it executes the view? Let's say I have: MY_VIEW = SELECT * FROM PERSON P, ORDERS O WHERE P.P_ID = O.P_ID And I then execute the following:

  • Basic join query understanding 2010-05-10

    I know this very silly, but can anybody help me in understanding what does this join query is doing in elabortive description? SELECT j1.* FROM jos_audittrail j1 LEFT OUTER JOIN jos_audittrail j2 ON (j1.trackid = j2.trackid AND j1.field = j2.field AN

  • Want to avoid the particular rows from select join query- See description 2010-05-13

    I have a Select Left Join Query whis displays me the rows for the latest changedone(its a time) column name ("field" should not be equal) column name ("trackid" should not be equal), and column name "Operation should be "UPDATE" ", below is the query

  • Join query in MySQL cursor 2010-05-29

    We can declare a cursor like this DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; Can we use a join query instead of a simple query? --------------Solutions------------- Yes, you can use all the various clauses in the SELECT statement except the

  • A join query for three tables!! for the given condtion below 2010-06-25

    I have three tables Orders table and customers table and orderstatus table, both order and customer table have customerid as common field,and order and orderstatus have order_status_id as common field customer table have firstname, lastname, phone an

  • Mysql join query for multiple "tags" (many-to-many relationship) that matches ALL tags? 2010-07-16

    I am trying to query for Objects that match ALL of a given set of Tags. Basically I want users to be able to add on more and more Tags to filter or "narrow down" their search results, kind of like does. My table structure is a table of Obj

  • MySQL implications of 'like' vs 'join' query 2010-08-15

    I've got two tables - Videos and Playlists Typically I would create the two respective tables and a multi-value table for the 1-Many relationship, such that : Videos = [pk, videoId, title, etc..] Playlists = [pk, title, etc..] Playlists_Videos = [Vid

Copyright (C), All Rights Reserved.

processed in 0.406 (s). 13 q(s)