#1
  1. Psycho Canadian
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Jan 2001
    Location
    Canada
    Posts
    4,846
    Rep Power
    635

    like case but for the whole query


    Ok I've been checking out a lot of different options and haven't found what I need. Maybe I'm just not looking at this correctly.

    SELECT field1 FROM table WHERE field2 = 'something' AND field1 = 145
    but if 145 doesn't exist I want it to run a view and have the results from the view be in place of 145.

    I know it's not very clear ask questions I'll try to explain more.
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    I may not be understanding correctly, but it sounds like your problem could be solved with a UNION between your first case of when field1 = 145 and your second case, when field1 ISNULL or doesn't = 145. Can you post the table setup and more about what you want from the query?


    -b
    PostgreSQL, it's what's for dinner...
  4. #3
  5. Psycho Canadian
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Jan 2001
    Location
    Canada
    Posts
    4,846
    Rep Power
    635
    Ok let me try to explain better.
    One table
    CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name CHARACTER VARYING NOT NULL,
    aid INTEGER NOT NULL
    );

    I want to
    SELECT * FROM mytable WHERE aid = 145
    now if that quey fails I want it to run
    SELECT * FROM mytable WHERE aid = 43

    I can do this programticly but I assume it can be done within the query also. OR isn't good because it returns both and I need only one record returned.

    I tried UNION but it didn't do what I need. Hope this is clearer thanks for the help.
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    I can't think of a way to do this with simple SQL. Why not write a function in pl/pgsql to do this? Then it'd be as simple as:

    select myFunction(145,43);
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Code:
    SELECT * 
    FROM mytable 
    WHERE aid = 145 
    union all
    select * 
    from mytable
    where not exists 
    (select * 
    from mytable 
     where aid = 145)
    and aid = 43
  10. #6
  11. Psycho Canadian
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Jan 2001
    Location
    Canada
    Posts
    4,846
    Rep Power
    635
    Thank you thank you very much swampboogie works perfectly. Thanks to all of you for your help.

IMN logo majestic logo threadwatch logo seochat tools logo