Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    13

    Unanswered: Change results in column to display different

    I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.

    Here goes..

    SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
    FROM SAMPLE SAMPLE, SDIDATAITEM SDI
    WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
    (SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
    ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Change results in column to display different

    If your sure that NR is the only other option besides a numeric you can use a simple decode

    Decode(Trim(SDI.DISPLAYVALUE),'NR',Null,SDI.DISPLA YVALUE)

    this is read like an IF statement

    If the value of the column is NR return a null, else return the column

    HIH

  3. #3
    Join Date
    Dec 2003
    Posts
    13
    I am not sure if there are other values. The table is very large, has has all kinds of numeric data) Is there something else I should use just in case there is other values?

  4. #4
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by Ashar
    I am not sure if there are other values. The table is very large, has has all kinds of numeric data) Is there something else I should use just in case there is other values?
    write a function is_numeric, taking the string returning Y or N and
    put that in the decode.

    function is_numeric(p_in varchar2) return varchar2 is
    v_num number;
    begin
    v_num := p_in;
    return 'Y';
    exception
    when others then
    return 'N';
    end;

    Then use
    decode(is_numeric(column_name),'Y',column_name,nul l)

  5. #5
    Join Date
    Dec 2003
    Posts
    13
    I am sorry, I should have started off that I am a beginner in Oracle PL\SQL. Is functions sort of like stored procs in SQL server. How do I create one? Is this syntax good, can I just copy it over to sql plus and run it?

  6. #6
    Join Date
    Dec 2003
    Posts
    13
    I don't think this last bit will work for me, I am actually calling this code from vbscript. So I don't want to have to call functions. I think, we this be done with a case statement or an if then statement?

  7. #7
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Function

    A function is a Stored Procedure that returns a value. This allows you to use them in SQL statements. The Function is stored in User_Source table and is compiled in the database. So you can use it from whatever language you want as long as you have execute privs to the function.

    So first you would create the function in the database under the correct schema.

    Code:
    Create Or Replace Function Is_Numeric(p_in varchar2) 
    return varchar2 
    is
       v_num number;   -- local variable of type Number
    begin
    
       v_num := p_in;  -- store input parameter in local Number variable
       return 'Y';          -- return Y for yes it is a number
    
    exception              -- error handling routine
       when others then  -- if any errors are encountered 
          return 'N';          -- return N.
    end;
    If the input parameter is not Numeric it will raise an exception, thus returning N.

    so now that you deployed your function grant it to the user / roles that will be executing the sql

    Grant Execute on Is_Numeric to USER/ROLE;

    Now when that user executes the select statement use

    decode(is_numeric(column_name),'Y',column_name,nul l)

    which is basically an IF statement.

    If the function is_numeric returns 'Y' then display column_name
    else display null

    HIH

  8. #8
    Join Date
    Dec 2003
    Posts
    13
    Thank you so much, I will work on this, to see if it helps.

  9. #9
    Join Date
    Dec 2003
    Posts
    13
    Will this code work in Oracle 8.1.7?

    When I went to create the function, I got an error Ora-00900.

    Here is the code I am posting. I just did a copy and paste.

    Create Or Replace Function Is_Numeric(p_in varchar2)
    return varchar2
    is
    v_num number; -- local variable of type Number
    begin

    v_num := p_in; -- store input parameter in local Number variable
    return 'Y'; -- return Y for yes it is a number

    exception -- error handling routine
    when others then -- if any errors are encountered
    return 'N'; -- return N.
    end;

  10. #10
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    It appears your Procedural Option is not installed.

    Can you ask your DBA to install it. If you are the DBA search google or www.dbaclick.com on the topic.

Posting Permissions

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