Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: It is stupid, but it works. How should it be done.

    I think I must be brain dead to have come up with this solution. Does any one know a "correct" way to do this?

    I am joining my lookups table to a data table. I want to show all the lookups that are not represented in the data table. I was getting nothing when the data table was empty. Here is my solution:

    SELECT lookup_code, lookup_desc
    FROM mdm_lookups, mdm_master_recipes
    WHERE UPPER(lookup_type) = 'PRODUCT'
    AND (lookup_code <> master_recipe_id
    AND UPPER(lookup_subtype) = UPPER(equipment_code)
    OR 0 = (select count(0) from mdm_master_recipes))
    order by lookup_desc

    There must be a more elegant way. I am all ears.
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Sorry. I gues you can ignore the "it works part". I did not run a full test. Now I just have the problem with no solution.

    I am still all ears.
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    select lookup_id from lookup_table
    MINUS
    select lookup_id from other_table;

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Man. That is why you have all those stars next to your name.

    Thanks. Have a good weekend.
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: It is stupid, but it works. How should it be done.

    Assuming that lookup_subtype in the lookup table joins to the equipment_code in the recipes table

    something like this should work


    Code:
    SELECT lookup_code, lookup_desc
    FROM mdm_lookups
    WHERE UPPER(lookup_type) = 'PRODUCT'
      AND NOT EXISTS 
         ( SELECT 1
           FROM mdm_master_recipes
           WHERE UPPER(lookup_subtype) = UPPER(equipment_code))

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Unfortunately, those stars merely represent persistence, rather than accuracy!

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Yes. That is the form that I once used.
    NOTE: Please disregard the label "Senior Member".

  8. #8
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Timing is everything.

    The "Yes. That is the form ..." was for Carloa.

    On the subject of stars. It must be more than persistence. I have seen people with more posts who have less stars. I thought it might be a frequency thing but "the Grand Poobah" and I have close to the same frequency but his star power is greater. As long as it is not some trade secret I would like someone to explain the star thing to me.
    NOTE: Please disregard the label "Senior Member".

  9. #9
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    If its not the # of posts, then it mumaybe its the # of posts compared to the time on the forum???

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't know. I got the impression that there were simple thresholds based on the number of posts. Maybe someone else can tell us the truth?

  11. #11
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Another version using an outer join - maybe faster or slower than the MINUS solution, it depends on the data statistics, memory available, etc - but i like the rowid is null trick ;-)

    SQL> create table lookup (x) as select rownum from all_objects where rownum <=5;

    Table created.

    SQL> create table master (x int);

    Table created.

    SQL> select l.*
    2 from master m, lookup l
    3 where m.x(+) = l.x
    4 and m.rowid is null;

    X
    ----------
    1
    2
    3
    4
    5

    SQL> insert into master (x) values (1);

    1 row created.

    SQL> select l.*
    2 from master m, lookup l
    3 where m.x(+) = l.x
    4 and m.rowid is null;

    X
    ----------
    2
    3
    4
    5

    About the stars - maybe a reply counts more than a post ?

    hth
    al

  12. #12
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thresholds: Yes, 1 star 20 - 49, 2 stars = 50-99 , 3 stars 100 - ? (I haven't attained 4 yet) I am guessing 250. Perhaps 5 stars is 500+. That would be the pattern and from a quick review of posts those levels seem to fit. I thought I once saw somebody with 1000+ who only had 4 stars. Maybe is was delusional at that point. Perhaps there is something else involved. The controllers of this universe are silent when it comes to asking them questions directly (at least my questions. :-) ).
    NOTE: Please disregard the label "Senior Member".

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Maybe we can work it out empirically. Carloa, you currently have 99 posts and 2 stars. How about replying to this and see if your rating becomes 3 stars?

  14. #14
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    Originally posted by andrewst
    Maybe we can work it out empirically. Carloa, you currently have 99 posts and 2 stars. How about replying to this and see if your rating becomes 3 stars?
    My 100th post (the one before this one) did, in fact, earn me my third star!
    Problem solved?.....Or just beginning?

  15. #15
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I was confident of the star progression up to 3 as I have tracked myself. Anybody near 250 or 500?
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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