Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: Another WHERE CASE problem

    select * from DUAL z where
    (
    --First Condition
    (
    z.dummy = 'Y'
    AND rownum < 2
    )
    OR
    --Second Condition
    (
    case when z.dummy = 'X' then 'Z'
    )
    )

    How do I get this to work so that when the first condition is not true then the second condition will return 'Z' when dummy = 'X'. I know I scewed up the use of case here but I'm not clear from the definition how to use it.

    -sharief

    I figured out why this didn't work and I got the error:

    The following doesn't solve my real problem but it is now the right syntax for putting the case in the where clause. However it does not make dummy = 'Z' in the output it just makes that condition true. My intention was to change the output of Dummy base on some independent criteria in the where clause.


    select * from DUAL z where
    (
    --First Condition
    (
    z.dummy = 'Y'
    AND rownum < 2
    )
    OR
    --Second Condition
    (
    'Z' = case when z.dummy = 'X' then 'Z' end
    )
    )
    Last edited by shariefc; 02-04-04 at 18:03.

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Another WHERE CASE problem

    My question would be:

    What would happen in the first case if z.dummy = 'Y' ?

    It would be helpful to know so we can get some SQL. I think I have an idea of what you're after, but I'm a little confused.


    Im guessing you will want something like this

    Select decode(dummy, 'Y', 'Some Expression', 'X', 'Z')
    from table_name
    where rownum <2

    I like DECODE better than CASE.

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    Yeah your right, my example sort of suggest that DECODE would be a better solution. However I was trying to simplify my example to get the error that I was getting which is


    z.dummy = 'Y'
    *
    ORA-00905: missing keyword


    A proper example would be a 2 tables called employee_info and employee

    EMPLOYEE_INFO
    emp_id fname lname
    -------- ------- -------
    1001 joe shmoe
    2001 jane doe
    3001 john doe


    EMPLOYEE
    emp_id cd org home_ind
    -------- -- ---- --- -----------
    1001 11 E Y
    1001 88 W N
    2001 44 W Y
    3001 00 E Y

    Note: Some emps in the east have a East Id and West Id and emps in the West only need a West Id and some east emps have only a east Id.
    Therefore if they are east and west show cd for east and cd for west. If they have only west show west cd for both east and west. If they have east cd only then only show east and show a blank for west.

    Desired output
    -----------------

    joe shmoe 1001 11 88
    jane doe 2001 44 44
    john doe 3001 00 <empty>


    select a.fname, a.lname, a.emp_id, b.cd as 'EAST', c.cd as 'WEST
    from employee_info a, employee b, employee c
    where a.emp_id = b.emp_id(+)
    --This next clause will get an east cd for everyone
    --since everyone has only one row with home_ind =Y
    --and the cd for west emps will be used for their
    --east cd
    and b.home_ind = 'Y'

    and a.emp_id = c.emp_id(+)
    --Now we want a west cd for those who have one
    --and blank for those who don't but I seem to be
    --loosing the last row completely so I'm trying to
    --get it back with the case statement
    and
    (
    (c.org = 'W' and c.home_ind = 'N')
    OR
    (c.org = 'W' and c.home_ind = 'Y')
    OR
    --I'm not sure what to put in the case
    --but I want it to evaluate to true and return
    --a blank field or null so I don't loose the other info
    --for emp_id 3001
    (case when (c.org = 'E' and c.home_ind = 'Y') then null)
    )

    c.org = 'W' and c.home_ind = 'N'
    *
    ORA-00905: missing keyword


    These tables are made up for this example. I didn't run
    this particular query but one just like it

    -sharief

    The reason I got "Missing Keyword" is because the case statement is not complete. It should be

    ...
    (c.cd = case when (c.org = 'E' and c.home_ind = 'Y') then null)

    Which doesn't resolve my original problem. I needed the case to change the output of c.cd when c.org = 'E' and c.home_ind = 'Y'.

    -sharief
    Last edited by shariefc; 02-04-04 at 18:11.

  4. #4
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by shariefc
    Yeah your right, my example sort of suggest that DECODE would be a better solution. However I was trying to simplify my example to get the error that I was getting which is


    z.dummy = 'Y'
    *
    ORA-00905: missing keyword


    A proper example would be a 2 tables called employee_info and employee

    EMPLOYEE_INFO
    emp_id fname lname
    -------- ------- -------
    1001 joe shmoe
    2001 jane doe
    3001 john doe


    EMPLOYEE
    emp_id cd org home_ind
    -------- -- ---- --- -----------
    1001 11 E Y
    1001 88 W N
    2001 44 W Y
    3001 00 E Y

    Note: Some emps in the east have a East Id and West Id and emps in the West only need a West Id and some east emps have only a east Id.
    Therefore if they are east and west show cd for east and cd for west. If they have only west show west cd for both east and west. If they have east cd only then only show east and show a blank for west.

    Desired output
    -----------------

    joe shmoe 1001 11 88
    jane doe 2001 44 44
    john doe 3001 00 <empty>


    select a.fname, a.lname, a.emp_id, b.cd as 'EAST', c.cd as 'WEST
    from employee_info a, employee b, employee c
    where a.emp_id = b.emp_id(+)
    --This next clause will get an east cd for everyone
    --since everyone has only one row with home_ind =Y
    --and the cd for west emps will be used for their
    --east cd
    and b.home_ind = 'Y'

    and a.emp_id = c.emp_id(+)
    --Now we want a west cd for those who have one
    --and blank for those who don't but I seem to be
    --loosing the last row completely so I'm trying to
    --get it back with the case statement
    and
    (
    (c.org = 'W' and c.home_ind = 'N')
    OR
    (c.org = 'W' and c.home_ind = 'Y')
    OR
    --I'm not sure what to put in the case
    --but I want it to evaluate to true and return
    --a blank field or null so I don't loose the other info
    --for emp_id 3001
    (case when (c.org = 'E' and c.home_ind = 'Y') then null)
    )

    c.org = 'W' and c.home_ind = 'N'
    *
    ORA-00905: missing keyword


    These tables are made up for this example. I didn't run
    this particular query but one just like it

    -sharief
    What's the meaning of null as a condition in the where clause? It should be put in the select part of the statement.

  5. #5
    Join Date
    Feb 2004
    Posts
    7
    I'm not sure what to put for the <expr> after THEN but basically I need that case statement to return TRUE but put a blank in the WEST field for emp_id 3001.

    -sharief

  6. #6
    Join Date
    Feb 2004
    Posts
    7
    I didn't put in the select statment because the fields I'm evaluating are not part of the fields in the select.

    -sharief

  7. #7
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by shariefc
    I'm not sure what to put for the <expr> after THEN but basically I need that case statement to return TRUE but put a blank in the WEST field for emp_id 3001.

    -sharief
    select .. decode(c.org||c.home_ind,,'EY',c.cd,null) as west
    ...

  8. #8
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by lynden.zhang
    select .. decode(c.org||c.home_ind,,'EY',c.cd,null) as west
    ...
    select .. decode(c.org||c.home_ind,,'EY',null,c.cd) as west

  9. #9
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by lynden.zhang
    select .. decode(c.org||c.home_ind,,'EY',null,c.cd) as west
    typo again
    select .. decode(c.org||c.home_ind,'EY',null,c.cd) as west [/SIZE][/

  10. #10
    Join Date
    Feb 2004
    Posts
    7
    That's ok I realized what you meant. Trying it right now.

Posting Permissions

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