Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Cool Unanswered: multi select in create view

    hi there

    I have this case :

    create view view1(field1, field2, field3, field4)
    as select f1, f2 ,
    (case when f1 = 'Y' then
    (select x1 from t1 ...)
    else
    (select y1 from t2 ...)
    end) f3,
    (case when f1 = 'Y' then
    (select x2 from t1 ...)
    else
    (select y2 from t2 ...)
    end) f4
    from table1;

    its working
    but when I tried to select 2 field at atime, Error appear!

    create view view1(field1, field2, field3, field4)
    as select f1, f2 ,
    (case when f1 = 'Y' then
    (select x1, y1 from t1 ...)
    else
    (select x2,y2 from t2 ...)
    end) f3, f4
    from table1;

    any Idea ?!?!?!?!

    thanx

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The CASE expression can only be used to determine one column value at a time. Your SQL could therefore be changed to:
    Code:
    create view view1(field1, field2, field3, field4)
    as select f1, f2 ,
    (case when f1 = 'Y' then
    (select x1 from t1 ...)
    else
    (select x2 from t2 ...)
    end) f3
    (case when f1 = 'Y' then
    (select y1 from t1 ...)
    else
    (select y2 from t2 ...)
    end) f4
    from table1;
    You may find it more convenient to use a UNION:
    Code:
    create view view1(field1, field2, field3, field4)
    as
    select f1, f2, x1, y1
    from table1, t1
    where f1 = 'Y'
    UNION ALL
    select f1, f2, x2, y2
    from table1, t2
    where NVL(f1,'x') != 'Y'

  3. #3
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137
    but I tried to use union before, its make the view slow
    I don't prefere to use union within view

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Don't use UNION then - it was only a suggestion!

    But just to be sure: did you mean it was slow when you tried it in this query? Or do you mean you tried it once in a different query, and it was slow there? I wouldn't like you to rule out ever using UNION because of one bad experience!

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    I vote for the second reason. My favorite is when people ask for help, then refuse your advice when "I can't do it that way" or "I dont want to do a,b,c or d - help me"
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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