Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Select Statement

    Hi All,

    I have the following data that is displayed as shown:

    (1) GOOD VISA CHARGE CARD
    (20) GOOD WHITE PROZAC - 20 PILLS
    (1) USED BROWN BELT
    (1) USED BLACK KNIT HAT
    (1) USED BLACK WALLET
    (1) USED BLACK CELL PHONE IN CASE
    (1) USED BLACK SHOE STRING
    (1) USED BLUE CHAPSTICK
    (1) USED MULTICOLORED MD ID
    (1) USED MULTIC

    I need to remove this:
    (1) USED MULTIC

    I have tried different things and none works. How can accomplish this?

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by inka View Post
    I have tried different things
    And what exactly have you tried?

    and none works
    "none works" is not an Oracle error message I know.

    I need to remove this:
    What exactly do you mean with "remove"?
    Remove it from the result of your SELECT?
    Permanently remove it from the table?

    Please remeber some rules about posting good question (in order to get usable answers)

    • you need to show us the the definition of your table(s) (preferrably as CREATE TABLE statements)
    • show us some sample data for the table(s) (preferrably as INSERT statements) (with your sample it is not clear whether this is the table's data or whether it is the result of a JOIN between several tables)
    • the expected output based on the sample data ("I need to remove" is not enough)
    • Make sure you make the SQL readable by using [code] tags

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    I need to remove from the select statement. I have tried using substring and instring functions but didn't get the expected results. The data shown is the table's data. The select statement is within a stored procedure.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I don't know what you have.
    I don't know what you do.
    I don't know what you see.
    It is really, Really, REALLY difficult to fix a problem that can not be seen.
    use COPY & PASTE so we can see what you do & how Oracle responds.
    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.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by inka View Post
    I need to remove from the select statement. I have tried using substring and instring functions but didn't get the expected results. The data shown is the table's data. The select statement is within a stored procedure.
    Well, you forgot to post the most important thing: why should be this row "removed" from the result set? Would not it be better to "remove" (=delete) it from the table? Why should not e.g. "(1) USED BROWN BELT" (or any other) be "removed" as well?
    The best suggestion I have seen is posted in this your thread (without any feedback): http://www.dbforums.com/oracle/16540...rd-select.html

  6. #6
    Join Date
    Jul 2004
    Posts
    268
    This is what I have:

    CREATE OR REPLACE PROCEDURE B_99_EN_IT

    IS

    --
    -- Work variables
    --

    ls_env_description_1 VARCHAR2(1000);
    ls_env_sum varchar2(1000);


    BEGIN


    DECLARE

    CURSOR oic_env_description_cur IS

    SELECT opi.property_description, rc.description, opi.quantity,
    refc.description, opi.make
    FROM offender_ppty_containers opc, offender_ppty_items opi,
    reference_codes rc, reference_codes refc
    WHERE opc.offender_book_id = opi.offender_book_id and
    opc.property_container_id = opi.property_container_id and
    opi.color = rc.code(+) and
    opi.condition_code = refc.code(+) and
    rc.domain(+) = 'PPTY_COLOR' and
    refc.domain(+) = 'PPTY_CONDIT' and
    opc.container_code = 'ENV' and
    opc.active_flag = 'Y' and
    opc.offender_book_id = ln_offender_book_id;


    c1_property_description offender_ppty_items.property_description%TYPE;
    c1_description reference_codes.description%TYPE;
    c1_quantity offender_ppty_items.quantity%TYPE;
    c1_condition_code reference_codes.description%TYPE;
    c1_make offender_ppty_items.make%TYPE;





    BEGIN

    select sum(length((opi.quantity)||') '||upper(rc.description)||' '||upper(refc.description)||' '||
    upper(opi.property_description)||' '||opi.make))
    into ls_env_sum
    FROM offender_ppty_containers opc, offender_ppty_items opi,
    reference_codes rc, reference_codes refc
    WHERE opc.offender_book_id = opi.offender_book_id and
    opc.property_container_id = opi.property_container_id and
    opi.color = rc.code(+) and
    opi.condition_code = refc.code(+) and
    rc.domain(+) = 'PPTY_COLOR' and
    refc.domain(+) = 'PPTY_CONDIT' and
    opc.container_code = 'ENV' and
    opc.active_flag = 'Y' and opc.offender_book_id = 12345;


    OPEN oic_env_description_cur;
    loop
    fetch oic_env_description_cur into c1_property_description, c1_description,c1_quantity,c1_condition_code,c1_ma ke;
    exit when oic_env_description_cur%notfound;

    if ls_env_sum < 255 then
    ls_env_description_1 := ls_env_description_1||'('||c1_quantity||') '||upper(c1_condition_code)||' '||upper(c1_description)||' '||
    c1_make||' '||upper(c1_property_description)||chr(11);
    end if;

    if ls_env_sum > 255 then
    ls_env_description_1 := substr(ls_env_description_1||'('||c1_quantity||') '||upper(c1_condition_code)||' '||upper(c1_description)||' '||c1_make||' '||
    upper(c1_property_description),1,255);

    ls_env_description_1 := substr(ls_env_description_1, 1, instr(ls_env_description_1||'('||c1_quantity||') '||upper(c1_condition_code)||' '||upper(c1_description)||' '||c1_make||' '||
    upper(c1_property_description), ') ',-1,1));


    end if;




    end loop;
    close oic_env_description_cur;


    END;
    END;


    This is what I see:

    (1) GOOD VISA CHARGE CARD
    (20) GOOD WHITE PROZAC - 20 PILLS
    (1) USED BROWN BELT
    (1) USED BLACK KNIT HAT
    (1) USED BLACK WALLET
    (1) USED BLACK CELL PHONE IN CASE
    (1) USED BLACK SHOE STRING
    (1) USED BLUE CHAPSTICK
    (1) USED MULTICOLORED MD ID
    (1) USED MULTIC

  7. #7
    Join Date
    Dec 2009
    Location
    Bangalore
    Posts
    2
    If you think from a business logic, the issue is with your data. Remove the data which contains "(1) USED MULTIC" from your database table/s

  8. #8
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    This should do it for you, You can add further selection criteria to the cursor(oic_env_description_length_cur) so that more or less rows or selected.

    Code:
    create or replace procedure b_99_en_it is
    
      v_env_description_1           varchar2( 1000 );
      v_offender_book_id            offender_ppty_containers.offender_book_id%TYPE;
      v_env_sum                     pls_integer  :=  0;
      g_error_text                  varchar2( 1000 );
    
    
      -- Cursor to retrieve length of columns by book_id
      cursor oic_env_description_length_cur is
        select opc.offender_book_id,
               sum( length( ( opi.quantity) || ') ' || upper( rc.description )           || ' ' ||
               upper( refc.description)     || ' '  || upper( opi.property_description ) || ' ' || opi.make ) )
         from  offender_ppty_containers         opc
                       join offender_ppty_items opi  on opc.offender_book_id       =  opi.offender_book_id
                                                    and opc.property_container_id  =  opi.property_container_id
            left outer join reference_codes     rc   on opi.color                  =  rc.code
            left outer join reference_codes     refc on opi.condition_code         =  refc.code
        where  opc.container_code    =  'ENV'
         and   opc.active_flag       =  'Y'
         and   rc.domain             =  'PPTY_COLOR'
         and   refc.domain           =  'PPTY_CONDIT'
        group by opc.offender_book_id;
    
    
      -- Cursor to retrieve columns for book_id
      cursor oic_env_description_cur( v_offender_book_id in varchar2 )  is
        select opi.property_description, rc.description, opi.quantity, refc.description, opi.make
         from  offender_ppty_containers       opc
                     join offender_ppty_items opi  on opc.offender_book_id       =  opi.offender_book_id
                                                  and opc.property_container_id  =  opi.property_container_id
          left outer join reference_codes     rc   on opi.color                  =  rc.code
          left outer join reference_codes     refc on opi.condition_code         =  refc.code
        where  opc.offender_book_id  =  v_offender_book_id;
    
      c1_property_description    offender_ppty_items.property_description%TYPE;
      c1_description             reference_codes.description%TYPE;
      c1_quantity                offender_ppty_items.quantity%TYPE;
      c1_condition_code          reference_codes.description%TYPE;
      c1_make                    offender_ppty_items.make%TYPE;
    
    
    begin
    
      open oic_env_description_length_cur;
      loop
        -- Determine book_id and length of columns with this cursor
        fetch oic_env_description_length_cur into v_offender_book_id, v_env_sum;
        exit when oic_env_description_length_cur%notfound;
    
        open oic_env_description_cur( v_offender_book_id );
        loop
          -- Retrieve columns for book with this cursor
          fetch oic_env_description_cur into c1_property_description, c1_description, c1_quantity, c1_condition_code, c1_make;
          exit when oic_env_description_cur%notfound;
    
          v_env_description_1  :=  '('                             ||
                                   c1_quantity                     ||
                                   ') '                            ||
                                   upper( c1_condition_code )      ||
                                   ' '                             ||
                                   upper( c1_description )         ||
                                   ' '                             ||
                                   c1_make                         ||
                                   ' '                             ||
                                   upper( c1_property_description );
    
          -- Length is less than 255
          if v_env_sum     <  255 then
            v_env_description_1  :=  v_env_description_1 || chr( 11 );
    
          -- Length is greater than 255
          elsif v_env_sum  >  255 then
            v_env_description_1  :=  substr( v_env_description_1, 1, 255 );
    
          -- Length is equal to 255
          else
            -- If you want to do something if the sum is equal to 255 then replace null with code
            -- otherwise you don't even need an 'else' section.
            null;
          end if;
    
          -- Now do something with the field( v_env_description_1 ) you created
          -- Like update another table, output it with dbms_output.put_line, etc.
    
    
        end loop;
        close oic_env_description_cur;
      end loop;
      close oic_env_description_length_cur;
    
      exception
        when others then
          g_error_text  :=  dbms_utility.format_error_backtrace;
          dbms_output.put_line( to_char( sqlcode ) || ': ' || sqlerrm );
          dbms_output.put_line( g_error_text );
    
    end b_99_en_it;


    hth

  9. #9
    Join Date
    Dec 2010
    Posts
    5

    kisheas from

    Quote Originally Posted by inka View Post
    Hi All,

    I have the following data that is displayed as shown:

    (1) GOOD VISA CHARGE CARD
    (20) GOOD WHITE PROZAC - 20 PILLS
    (1) USED BROWN BELT
    (1) USED BLACK KNIT HAT
    (1) USED BLACK WALLET
    (1) USED BLACK CELL PHONE IN CASE
    (1) USED BLACK SHOE STRING
    (1) USED BLUE CHAPSTICK
    (1) USED MULTICOLORED MD ID
    (1) USED MULTIC

    I need to remove this:
    (1) USED MULTIC

    I have tried different things and none works. How can accomplish this?

    Thanks
    why don you use delete statement for that?

Posting Permissions

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