Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    4

    Cool Unanswered: minus Query issue

    can somebody tell what wrong in this query? I am getting an error ora-00933 SQL command not properly ended.


    select distinct upods_stg.stg_ndb_adr_t.adr_id
    from upods_stg.stg_ndb_adr_t

    (select upods_stg.stg_ndb_adr_t.adr_ln_1_txt
    from upods_stg.stg_ndb_adr_t)
    minus
    (select upods_stg.stg_ndb_adr_t.adr_ln_1_txt
    from upods_stg.prov_ndb_adr_t)

    Thanks
    Sri

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >can somebody tell what wrong in this query?
    it is invalid syntax.

    what exactly are the characters between the two pair of parenthesis?
    Are they supposed to be part of the 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.

  3. #3
    Join Date
    Jun 2012
    Posts
    4
    Yes,
    they are supposed to be in the FROM clause.
    I am trying to write a minus query, where the source adr_ln_1_txt is same as target column adr_ln_1_txt.
    I am using distinct on adr_id, so that I can get the matching rows.

    Thanks,
    Sri

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >they are supposed to be in the FROM clause.
    I was always taught that items in the FROM clause are separated by commas
    FROM TABLE1 T1, TABLE2 T2
    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.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Sri2792 View Post
    ...
    I am trying to write a minus query, where the source adr_ln_1_txt is same as target column adr_ln_1_txt.
    ...
    So, I thought that INTERSECT migh be matched to your requirements, raher tan MINUS.

    Anoher isuues was you should specify relation(s) with other tables and/or other result of subqueries
    for table(s) in FROM clause,
    like already pointed out by other persons in this thread.


    Anyway, I though that it might be not necessary to use set operations for your requirements, like
    Code:
    SELECT s.adr_id
     FROM  upods_stg.stg_ndb_adr_t s/*ource*/
     WHERE EXISTS
          (SELECT 0
            FROM  upods_stg.prov_ndb_adr_t t/*arget*/
            WHERE t.adr_ln_1_txt = s.adr_ln_1_txt
          )


    But, if you want to use set operations, this might be an hint for you (though, it might be not exact answer for you requiremen)....
    Code:
    SELECT adr_id
     FROM  upods_stg.stg_ndb_adr_t
     WHERE adr_ln_1_txt
           IN
          (SELECT adr_ln_1_txt
            FROM  upods_stg.stg_ndb_adr_t
           INTERSECT
           SELECT adr_ln_1_txt
            FROM  upods_stg.prov_ndb_adr_t
          )
    -
    Last edited by tonkuma; 06-24-12 at 06:10. Reason: Replace second example.

Posting Permissions

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