Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: Decode and escape characters help..

    Hi everybody,
    I have a little issue with a decode statement, I'm using Oracle 10, this is the query:

    SELECT JOBEXE.OPS_FK
    FROM VS_AM_REQUEST_ITEM JOBEXE
    WHERE JOBEXE.JOB_KIND IN ( DECODE ( &value , 'REJ' , 'REPA' , 'PRJ', ' ''PREP''||,||''MAIN'' ' )) ;

    Basically if value is PRJ then JOB_KIND can be equals to PREP or MAIN , so I tried to nest a decode into an in, but no rows are selected...
    Thanks in advance for the help!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Basically if value is PRJ
    value of what, exactly?

    >then JOB_KIND can be equals to PREP or MAIN
    OK. so use "OR" condition

    I don't understand what you have or what you expect/desire?
    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
    Jul 2012
    Posts
    4
    Yes sorry I wasn't very clear...

    I have a parameter passed through java into the variable &value (I haven't control on that) so the where condition on job_kind in the query should be dinamic. Like

    Code:
    if (&value = 'REJ' )
        SELECT JOBEXE.OPS_FK
        FROM VS_AM_REQUEST_ITEM JOBEXE
       WHERE JOBEXE.JOB_KIND = 'REPA' ;
     else if (&value = 'PRJ' )
        SELECT JOBEXE.OPS_FK
        FROM VS_AM_REQUEST_ITEM JOBEXE
        WHERE JOBEXE.JOB_KIND IN ('PREP','MAIN') ;
    So I was trying to write it all in one query using decode..

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one option:
    Code:
    SQL> select ename, job
      2  from emp
      3  where  job = decode('&&val', 'C' , 'CLERK')
      4     or (job = decode('&&val', 'SM', 'SALESMAN')
      5         or
      6         job = decode('&&val', 'SM', 'MANAGER')
      7        )
      8  order by job, ename;
    Enter value for val: C
    
    ENAME      JOB
    ---------- ---------
    ADAMS      CLERK
    JAMES      CLERK
    MILLER     CLERK
    SMITH      CLERK
    
    SQL> undefine val
    SQL> /
    Enter value for val: SM
    
    ENAME      JOB
    ---------- ---------
    BLAKE      MANAGER
    CLARK      MANAGER
    JONES      MANAGER
    ALLEN      SALESMAN
    MARTIN     SALESMAN
    TURNER     SALESMAN
    WARD       SALESMAN
    
    7 rows selected.
    
    SQL>

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question

    Quote Originally Posted by kenatt82 View Post
    WHERE JOBEXE.JOB_KIND IN ( DECODE ( &value , 'REJ' , 'REPA' , 'PRJ', ' ''PREP''||,||''MAIN'' ' )) ;
    Thats a common misconception of how the in clause works.

    An IN clause will evaluate a list of items.
    Code:
    COL IN ('VALUE1', 'VALUE2')
    is different from
    Code:
    COL IN ('VALUE1, VALUE2')
    Can you see the difference? I am still thinking of how I could make this more clear.

    Anyway, try this statement instead.
    Code:
    SELECT JOBEXE.OPS_FK
    FROM VS_AM_REQUEST_ITEM JOBEXE
    WHERE JOBEXE.JOB_KIND = DECODE( &value , 'REJ' , 'REPA')
      or JOBEXE.JOB_KIND = DECODE( &value , 'PRJ', 'PREP')
      or JOBEXE.JOB_KIND = DECODE( &value , 'PRJ', 'MAIN');

  6. #6
    Join Date
    Jul 2012
    Posts
    4
    Yes I see my mistake..thanks a lot guys!

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by kenatt82 View Post
    ...

    ... the where condition on job_kind in the query should be dinamic. Like

    Code:
    if (&value = 'REJ' )
        SELECT JOBEXE.OPS_FK
        FROM VS_AM_REQUEST_ITEM JOBEXE
       WHERE JOBEXE.JOB_KIND = 'REPA' ;
     else if (&value = 'PRJ' )
        SELECT JOBEXE.OPS_FK
        FROM VS_AM_REQUEST_ITEM JOBEXE
        WHERE JOBEXE.JOB_KIND IN ('PREP','MAIN') ;
    So I was trying to write it all in one query using decode..
    Try,
    Code:
    SELECT JOBEXE.OPS_FK
     FROM  VS_AM_REQUEST_ITEM JOBEXE
     WHERE
           &value = 'REJ'
       AND JOBEXE.JOB_KIND = 'REPA'
       OR
           &value = 'PRJ'
       AND JOBEXE.JOB_KIND IN ('PREP' , 'MAIN')
    ;
    By the way,
    If (&value NOT IN ('REJ' , 'PRJ') )
    then what do you want to do?
    Or, is it guaranteed &value to be 'REJ' or 'PRJ'?

  8. #8
    Join Date
    Jul 2012
    Posts
    4
    Thanks, there was no need for decode in the end
    If (&value NOT IN ('REJ' , 'PRJ') ) result should be 'no rows selected' so this code should be fine..

Posting Permissions

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