Results 1 to 4 of 4

Thread: using CASE

  1. #1
    Join Date
    May 2008
    Posts
    3

    Question Unanswered: using CASE

    Hi all,
    I'm a newbie to oracle and I'm trying to use the CASE statement but I keep getting "couldn't retrieve data" error. foll is my query

    select pk_id, (case pk_id when '8' then(select pm_family from bd.dkt where bd.dkt.pk_id='8') else 'N/A' END) as pm from bd.dkt

    Purpose of this query: I'm trying to check if there is a row with pk_id ='8' , if so get the pm_familyl value of it else 'N/A'

    Any thoughts?

    Thanks,
    karthik

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any thoughts?
    You should read & follow posting guidelines as stated in #1 STICKY post at the top of this forum.

    Based upon what you have posted & what you have NOT posted, nobody can reproduce what you tried to describe.

    It would have been much better if you had CUT & PASTED exactly what you did & how Oracle responded.
    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
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Error message you posted is not very descriptive; it would be better if you informed us of ORA error code (which uniquely identifies the error and there's no doubt what happened).

    Even better, do as Anacedent suggested - post the whole SQL*Plus session.

    Now, your query works in certain situations. If "pk_id" is a "primary key ID", then its value is unique and query would/should work:
    Code:
    SQL> select * from dkt;
    
    P PM_FAMILY
    - --------------------
    1 nemam pojma
    8 to je taj
    
    SQL> select pk_id,
      2  (case pk_id when '8' then
      3                (select pm_family from dkt where dkt.pk_id='8')
      4              else 'N/A' END) as pm
      5  from dkt;
    
    P PM
    - --------------------
    1 N/A
    8 to je taj
    
    SQL>
    However, if that's not the case, you're about to get another error: too many rows (ORA-01427) if there are more than a single value of "pk_id" column, such as
    Code:
    SQL> select * from dkt;
    
    P PM_FAMILY
    - --------------------
    1 nemam pojma
    8 to je taj
    8 to je drugi
    
    SQL> select pk_id,
      2  (case pk_id when '8' then
      3                (select pm_family from dkt where dkt.pk_id='8')
      4              else 'N/A' END) as pm
      5  from dkt;
    ERROR:
    ORA-01427: single-row subquery returns more than one row
    
    
    
    no rows selected
    
    SQL>
    Therefore, you'd perhaps want to switch to a simpler solution; here are two of them - one uses DECODE, the other one CASE:
    Code:
    SQL> select pk_id, decode(pk_id, '8', pm_family, 'N/A') pm
      2  from dkt;
    
    P PM
    - --------------------
    1 N/A
    8 to je taj
    8 to je drugi
    
    SQL>
    SQL> select pk_id,
      2    case when pk_id = '8' then pm_family
      3         else 'N/A'
      4    end pm
      5  from dkt;
    
    P PM
    - --------------------
    1 N/A
    8 to je taj
    8 to je drugi
    
    SQL>

  4. #4
    Join Date
    May 2008
    Posts
    3

    Thumbs up thanks

    thanks for the help guys, I got that resolved here as well. will try and post meaningfully the next time.

Posting Permissions

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