Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    8

    Unanswered: Please help with this Query

    Tables

    table1
    table2

    Condition: if table1.column1 doesnot start with '11' map table2.column2

    Subset conditions: Select only data where table1.column2 = 018, table1.column3 between ' ' and ' ' order by table1.column2


    Subset conditions : select only data where table2.column2 = 018 table2.column3 between ''and '' order by table2.column2

    Conditions are there for join.

    query

    select (case when table1.column1 not like '11%' then table2.column2 end) from table1 outer join table2 on ( table1.column4 = table2.column4 and table1.column2 = table2.column2 and table1.column3 = table2.column3) where table1.column2 = 018 and table1.column3 between ''and '' order by table1.column2



    please let me know if i made any error as it is saying ORA 00904 table2.column2 has invalid identifier

    I have column names correct and the datatypes are same in both tables

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by aruku View Post
    I have column names correct
    Apparently not, because you are getting the error message.

    Show the complete DDL (CREATE TABLE ...) for both tables
    Show your real SQL query

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT   (CASE
                WHEN table1.column1 NOT LIKE '11%'
                THEN table2.column2
              END)
    FROM     table1 OUTER
             JOIN table2
               ON (table1.column4 = table2.column4
                   AND table1.column2 = table2.column2
                   AND table1.column3 = table2.column3)
    WHERE    table1.column2 = 018
             AND table1.column3 BETWEEN '' AND ''
    ORDER BY table1.column2
    In/on LINE #3 TABLE2 has not yet been defined, used, or referenced.
    SELECT can only reference objects that exist in FROM clause
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2003
    Posts
    76
    also, shouldnt the case statement be given some column name?

  5. #5
    Join Date
    Dec 2009
    Posts
    8
    we can write case statement without column name too

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anacedent View Post
    Code:
    FROM     table1 OUTER
             JOIN table2
               ON ...
    is oracle different from other database systems in that the LEFT|RIGHT|FULL keyword is optional???

    i always thought that LEFT|RIGHT|FULL was mandatory, and OUTER was optional...
    Code:
    FROM t1 LEFT|RIGHT|FULL [OUTER] JOIN t2 ON ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i always thought that LEFT|RIGHT|FULL was mandatory, and OUTER was optional.
    This is confirmed by manual

    SELECT

    I just previously formatted what OP had provided.
    FWIW - the formatter did not complain so I had "assumed" it was valid SQL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anacedent View Post
    I just previously formatted what OP had provided.
    no worries, i've done the same more than once

    you use a formatter? i format my SQL manually, with a text editor

    happy holidays
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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