Results 1 to 8 of 8

Thread: Join Query

  1. #1
    Join Date
    Aug 2008
    Posts
    4

    Question Unanswered: Join Query

    Hi,
    i am trying to run an oracle query to join two tables.
    attached is Table t (the one with 3 columns) and the other attachment is a row of Table msv600.

    The columns i am interested in are msv.plant_no and t.search1.

    i am trying to create a join btw the two tables. If you notice the plant_no column has an extra caracter at the end of the string.

    how can i match the two fields and produce a result?
    i was trying the code below but it isnt working.

    can anyone help?

    SELECT DISTINCT e.equip_no, e.plant_no, e.equip_status
    FROM ellipse.msv600 e INNER JOIN t
    ON substr (e.plant_no, 1, 10) = t.search1;

    all suggestions welcome.

    cheers
    Attached Thumbnails Attached Thumbnails untitled1.JPG   untitled.JPG  

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It seems to be SEARCH1 that needs substringing to remove the last character, not PLANT_NO?
    Code:
    SELECT DISTINCT e.equip_no, e.plant_no, e.equip_status
    FROM ellipse.msv600 e INNER JOIN t
    ON e.plant_no = substr (t.search1, 1, 10);
    Incidentally the name SEARCH1 and the use of '%' suggests to me that the intended use might be:
    Code:
    SELECT DISTINCT e.equip_no, e.plant_no, e.equip_status
    FROM ellipse.msv600 e INNER JOIN t
    ON e.plant_no LIKE t.search1;

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    Try this query

    SELECT e.equip_no, e.plant_no, e.equip_status
    FROM ellipse.msv600 e ,t a
    where Left(plant_no,10) = a.search1
    group by e.equip_no, e.plant_no, e.equip_status;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    ... and you'll see:

    ORA-00904: "LEFT": invalid identifier

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Which database do you use, Laknar? Because, "LEFT" is an "invalid identifier" or "invalid column name" in Oracle (at least in 8i, 9i and 10g, databases I can access at the moment).

  6. #6
    Join Date
    Jul 2008
    Posts
    94
    SELECT e.equip_no, e.plant_no, e.equip_status
    FROM ellipse.msv600 e ,t a
    where Left(e.plant_no,10) = a.search1
    group by e.equip_no, e.plant_no, e.equip_status;

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yo! Laknar! There's no LEFT identifier in Oracle!

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Left(e.plant_no,10) --- Microsoft

    substr(e.plant_no,1,10) -- Oracle and Microsoft
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •