Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    36

    Unanswered: Comparing functions problem

    SELECT trim(B.ORIGINAL_LIC_NUM) FROM CR_LIC_TEMP B, CR_TBL A
    WHERE '%'||trim(B.ORIGINAL_LIC_NUM)||'%' in ( '%' || trim(A.ACTIVITY1)||' '||trim(A.ACTIVITY2)||' '||trim(A.ACTIVITY3)||' '||trim(A.ACTIVITY4)||' '||trim(A.ACTIVITY5)|| '%')
    OR
    '%' ||trim(B.CLEAN_LIC_NUM) ||'%' in ( '%' || trim(A.ACTIVITY1)||' '||trim(A.ACTIVITY2)||' '||trim(A.ACTIVITY3)||' '||trim(A.ACTIVITY4)||' '||trim(A.ACTIVITY5)|| '%')



    Dear Experts,

    Greetings..!!

    I have problem,

    Above is my query, where im trying to select ORIGINAL_LIC_NUM from CR_LIC_TEMP table.

    In the above case, ACTIVITY1 to ACTIVITY5 is an actual string column from CR_TBL table, where ORIGINAL_LIC_NUM present along with other desciption, (please check the data).

    Im writing the above query to select the ORIGINAL_LIC_NUM, which is present in either one among those 5 activities.

    I have tried to replace IN with LIKE and '='.

    But no luck.

    Some body please advise


    Thank you in advance
    Attached Thumbnails Attached Thumbnails Activity_Smapl.jpg  

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    IN operator does not support wildcards, it allows only list of fixed values, as described e.g. here: http://psoug.org/definition/IN.htm
    In your case, it contains one string value, so it is equivalent to equality (=) operator.

    Maybe, before using any Oracle feature, you should learn about it in SQL Language Reference, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

    If you are looking for appearance of CLEAN_LIC_NUM or ORIGINAL_LIC_NUM in any of ACTIVITY column (regardless its start/stop, so that 'RAIN' is included in 'I NEED ORACLE TRAINING') with taking care of case (so that 'rain' is not included in the same string), it could look like:
    Code:
    WHERE trim(A.ACTIVITY1)||' '||trim(A.ACTIVITY2)||' '||trim(A.ACTIVITY3)||' '
          ||trim(A.ACTIVITY4)||' '||trim(A.ACTIVITY5) LIKE '%'||trim(B.ORIGINAL_LIC_NUM)||'%'
       OR trim(A.ACTIVITY1)||' '||trim(A.ACTIVITY2)||' '||trim(A.ACTIVITY3)||' '
          ||trim(A.ACTIVITY4)||' '||trim(A.ACTIVITY5) LIKE '%'||trim(B.CLEAN_LIC_NUM)||'%'

  3. #3
    Join Date
    Dec 2007
    Posts
    253
    Your exact requirements are a little unclear (mostly due to an incomplete set of sample data), however I'm guessing that you want something like:
    Code:
    with t1 as (select 'abc' val1, 1 id from dual union all
                select 'mno' val1, 1 id from dual union all
                select 'pqr' val1, 1 id from dual)
                ,
          t2 as (select '123890' act1, ';labc#l;l' act2, '7656' act3 from dual union all      
                 select '123890' act1, '87876' act2, 'mnokkkkkk' act3 from dual union all
                 select '123890' act1, '123890' act2, '123890' act3 from dual union all
                 select '123890' act1, '123890' act2, '123890' act3 from dual)
    select *
    from t1, t2
    where instr(t2.act1||t2.act2||t2.act3,t1.val1)>0
    If this does not suit your requirements, then post a working test case (use WE single byte characters rather than the multi byte character set that you attached)

Posting Permissions

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