to_number 1

[Stevennn]

Im doing Select statment with CASE but i got a problem i need to convert gl_acc and gl_cc to NUMBER right now they are varchar2. So the BETWEEN should be read as number not as charctor for that range!!!

And pull only those records where MEASURE not equals to NULL....


Thank-you for your HELP!

SELECT
xxx1,
ccc,

CASE WHEN GL_ACC BETWEEN 4001 AND 4500 AND

GL_CC = 'NONE'

THEN '"NS-'|| GL_ACC ||'"'

WHEN ((gl_acc BETWEEN 6101 AND 6390) OR

(gl_acc BETWEEN 6411 AND 6705)) AND

gl_cc = 'NONE'

THEN '"PV-'|| GL_ACC ||'"'
end as MEASURE

from.......................

 

[SantaMufasa]

Russski,

I believe that the most straightforward method to achieve your results is to use my user-defined function, "NUMCHK":

create or replace
function numchk (n varchar2) return number is
        number_hold     number;
begin
        number_hold     := to_number(trim(n));
        return number_hold;
exception
        when others then
                return null;
end;
/

Function created.

Then your original code can read:

SELECT *
  FROM (SELECT xxx1
              ,ccc
              ,CASE WHEN numchk(GL_ACC)
                         BETWEEN 4001 AND 4500
                         AND GL_CC = 'NONE'
                         THEN '"NS-'||trim(GL_ACC)||'"'
                    WHEN ((numchk(gl_acc)
                         BETWEEN 6101 AND 6390)
                         OR (numchk(gl_acc)
                             BETWEEN 6411 AND 6705))
                         AND gl_cc = 'NONE'
                         THEN '"PV-'||trim(GL_ACC)||'"'
               end as MEASURE
         from .......................)
 WHERE measure is NOT NULL;

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Olivia123]

I have tried to do what you said, and I'm still getting this error message
"Trigger, procedure or function created with PL/SQL compilation error(s)."
This is what I have .....

create or replace
function NUMCHK (n varchar2) return number is
number_hold number;
begin
number_hold := to_number(trim);
return number_hold;
exception
when others then
return null;
end;

SELECT *
FROM (SELECT ID_AC,
GL_CC,
CASE WHEN NUMCHK(ID_AC)
BETWEEN 4001 AND 4500
AND ID_CC = 'NONE'
THEN '"NS-'||trim(ID_AC)||'"'
WHEN ((NUMCHK(ID_AC)
BETWEEN 6101 AND 6390)
OR (NUMCHK(ID_AC)
BETWEEN 6411 AND 6705))
AND ID_CC = 'NONE'
THEN '"PV-'||trim(ID_AC)||'"'
end as MEASURE
from ................. )
WHERE measure is NOT NULL;

 

[SantaMufasa]

Olivia,

If you are using SQL*Plus to compile the above function, you must terminate the function with a "/" on a line of its own (following the "end;" statement). Did you do that?

If you ran all of the above code together, you will certainly encounter at least this error:

Warning: Function created with compilation errors.

Then, to see the actual errors that Oracle thinks you have in that situation, you user the SQL*Plus "show errors" command:

SQL> show errors

Errors for FUNCTION NUMCHK:

LINE/COL ERROR
-------- ---------------------------------------------------------------
11/1     PLS-00103: Encountered the symbol "SELECT"
25/15    PLS-00103: Encountered the symbol "." when expecting one of the
         following:
         ( <an identifier> <a double-quoted delimited-identifier>
         table the
************************************************************************

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Olivia123]

I'm using TOAD and QTODBC and when I put (/) around my Function I get an error "Invalid SQL Statement" and when I use SQL> show errors command, it doesn't show me my errors,
but it still gives me this error "Trigger, procedure or function created with PL/SQL compilation error(s).

 

[SantaMufasa]

Olivia,

First, Olivia, in both of your postings, above, the code for the "numchk" function and the code for your SQL query seem to appear together. I just want to confirm that you are running the "CREATE FUNCTION..." code separately from the SELECT statement...You should not attempt to run the SELECT statement until your "CREATE FUNCTION..." code results in a "Function created." response.

Second, TOAD and QTODBC don't recognise "/"; that is a SQL*Plus symbol.

Third, if the SQL*Plus command, "show errors", produces no results (following an attempt to compile in SQL*Plus), then you can use the following script to display errors for a user session. (BTW, I artificially widened the display, below, to avoid unsightly/confusing line wrap on wider lines.):

col line_pos format a8
col text Heading "Error Description" format a90
accept proc prompt "Enter the name of the Procedure/Function you just tried to compile: "
select line||'/'||position line_pos, text
from user_errors
where name = upper('&proc')
/

I called the above script "showerrors.sql" (designed for use in SQL*Plus). You cannot copy and paste the above code since it contains "ACCEPT...PROMPT" syntax, which must run from a script. Following is a sample invocation of "showerrors.sql" from a SQL*Plus prompt:

SQL> @showerrors
Enter the name of the Procedure/Function you just tried to compile: xy

LINE_POS Error Description
-------- ------------------------------------------------------------------------------------
2/7      PLS-00201: identifier 'VW' must be declared
2/7      PL/SQL: Item ignored
6/19     PLS-00320: the declaration of the type of this expression is incomplete or malformed
6/21     PL/SQL: ORA-00904: : invalid identifier
6/5      PL/SQL: SQL Statement ignored
7/26     PLS-00320: the declaration of the type of this expression is incomplete or malformed
7/5      PL/SQL: Statement ignored
**********************************************************************************************

You could tailor the above script for use outside of SQL*Plus (i.e., TOAD, et cetera), as well.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Olivia123]

Hey,
I'm sorry, I got a liitle confused when you said "in both of your postings, above, the code for the "numchk" function and the code for your SQL query seem to appear together", how else would I write it?
I declare the function first and then I use it in the select. Please let me know. Thank you.
This is what I have so far....the Function runs, but, as soon as I try to write the SELECT statement after, it shows me the error that I mentioned before "Trigger, procedure or function created with PL/SQL compilation error(s)."

function NUMCHK (n varchar2) return number is
number_hold number;
begin
number_hold := to_number(trim);
return number_hold;
exception
when others then
return null;
end;

SELECT *
FROM (SELECT ID_AC,
GL_CC,
CASE WHEN NUMCHK(ID_AC)
BETWEEN 4001 AND 4500
AND ID_CC = 'NONE'
THEN '"NS-'||trim(ID_AC)||'"'
WHEN ((NUMCHK(ID_AC)
BETWEEN 6101 AND 6390)
OR (NUMCHK(ID_AC)
BETWEEN 6411 AND 6705))
AND ID_CC = 'NONE'
THEN '"PV-'||trim(ID_AC)||'"'
end as MEASURE
from ................. )
WHERE measure is NOT NULL;

Thank you

 

[SantaMufasa]

I declare the function first...

...and when you did that, was there a confirmation that Oracle created the function successfully?

Please run the following two queries, which will tell us if the function is behaving correctly:

SQL> select numchk('abc') from dual;

NUMCHK('ABC')
-------------
              <--- Notice that NULL appears here

SQL> select numchk('   -5.67    ') from dual;

NUMCHK('-5.67')
---------------
          -5.67

The queries, above, should produce the same results that you see, above.

Then, please run your main, original query (that is receiving the errors). Copy and paste both the query and the error message directly from the screen, here on this thread.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Olivia123]

Hey, thanks so much for helping. The function and the SQL Statement worked fine.....
The only problem I have now is when I run this query, in the MEASURE column I get ID_AC that I don't have in my ranges. For ex. "NS-1001"
Can you plese tell me why this might be happening?
SELECT *
FROM (SELECT ID_AC,
GL_CC,
CASE WHEN NUMCHK(ID_AC)
BETWEEN 4001 AND 4500
AND ID_CC = 'NONE'
THEN '"NS-'||trim(ID_AC)||'"'
WHEN ((NUMCHK(ID_AC)
BETWEEN 6101 AND 6390)
OR (NUMCHK(ID_AC)
BETWEEN 6411 AND 6705))
AND ID_CC = 'NONE'
THEN '"PV-'||trim(ID_AC)||'"'
end as MEASURE
from ................. )
WHERE measure is NOT NULL;

 

[Olivia123]

I need to mention that my ID_CC is identified as CHAR as well, and when there are ranges I use NUMCHK function....... The problem is, when I use NUMCHK to convert ID_CC it counts all of the accounts that have an ID_CC with letter in the front of the number (ex:A100)
A018 9301 "9301-NOE"
A018 9302 "9302-NOE"
A018 9303 "9303-NOE"
NONE 1003 "1003-PV"
NONE 1025 "1025-PV"
NONE 1155 "1155-PV"
NONE 1157 "1157-PV"
NONE 1341 "1341-PV"
NONE 1343 "1343-PV"
NONE 1346 "1346-PV"
NONE 1400 "1400-PV"
NONE 1401 "1401-PV"
NONE 1410 "1410-PV"
NONE 1415 "1415-PV"

I don’t have any ranges for ID_AC with the range starting in 1000’s but ID_CC does have a character for ID_AC in that range. How would I write if there is a letter in front of "numbers" not to convert that to_numbers

 

[SantaMufasa]

Olivia,

That is very puzzling, indeed. All I can suggest to isolate that issue is to break out your code into smallest possible components and re-test incremental code segments, such as:

SELECT ID_AC, GL_CC
  FROM ....
 WHERE NUMCHK(ID_AC) BETWEEN 4001 AND 4500
   AND ID_CC = 'NONE';

...to see if the "1001" value shows up.

Then, if the "1001" value does not show up, start adding code back into that minimal code segment until the results do not match your expectations.

I'm eager to hear about your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Olivia123]

The function that you helped me with converts character value to number. (ex: 100, 300, 555, a356, w245)
Is there a way to alter this function in order to convert ONLY those fields that start with a number not a character AND those fields that start with a character leave it alone?

When it will be converting a356 it will not count “a” but count starting from 3.


Thank you again for helping...

 

[SantaMufasa]

Certainly...We can write a function to do just about anything you want.

Before building a function for you, however, we need to nail down the specifications of a new function.

In the previous function, if the VARCHAR expression contained a value that would not convert directly to a valid numeric value, the function returned NULL. So, the values that you listed in your most recent reply would return these values:
100 -> 100, 300 -> 300, 555 -> 555, a356 -> NULL, w245 -> NULL.

In a revised function, what would you want these incoming values to return from the function:
[tt]
100.5 ->
A206 ->
ABC306 ->
xy123xy ->
ab12cd7 ->
x12. 4 ->
x-23.3w ->
[/tt]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Olivia123]

Again, can’t thank you enough for helping me out!


So the values that are a356 or w245 I would like not to be NULL and not be converted to NUMBER but to leave it alone as is (which is character).

Thank-you

 

[SantaMufasa]

Sorry, Olivia, I'm not clear yet...If you want to not convert "a356" and "w245" to NUMBER, what qualifies them for printing out at all (since the don't fit in the numeric ranges that you specified)? If you don't convert the above type of values, then why would you want to convert any of the values?

I'm not trying to be contrary, I'm just trying to understand fully so that I can meet your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Olivia123]

Hey,

Let's say if I have a range in the table between 1000 and 9000 that must be prefixed with PV. And sometimes in that same table I have fields that are w546,a789. That function will convert W and A as a number and read it in that range between 1000 and 9000, and so it'll add PV to it....I can't have that. When there is a field that has a letter in front of it, I don't want it to be converted to numbers. So, it'll read the ranges correctly.

Thank you

 

[SantaMufasa]

Thanks, Olivia, for the clarifications.

Unless I am mistaken, it seems to me that you can achieve what you want (without our re-writing the function), simply by proper structuring of your CASE statement. Specifically, in pseudo-code:

if the first character of GL_ACC = 'W' or 'A', then result X
else if GL_ACC in range A then result Y
else if GL_ACC in range B then result Z
else do whatever you want when none of the above conditions

If the above logic assumptions are correct, then here might be your code to achieve that:

SELECT *
  FROM (SELECT xxx1
              ,ccc
              ,CASE [b]WHEN upper(substr(GL_ACC,1,1)) in ('A','W')
                         THEN GL_ACC[/b]
                    WHEN numchk(GL_ACC)
                         BETWEEN 4001 AND 4500
                         AND GL_CC = 'NONE'
                         THEN '"NS-'||trim(GL_ACC)||'"'
                    WHEN ((numchk(gl_acc)
                         BETWEEN 6101 AND 6390)
                         OR (numchk(gl_acc)
                             BETWEEN 6411 AND 6705))
                         AND gl_cc = 'NONE'
                         THEN '"PV-'||trim(GL_ACC)||'"'
               end as MEASURE
         from .......................)
 WHERE measure is NOT NULL;

Let us know how the above differs from what you want to do.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Olivia123]

Thank you so much this worked!!
"CASE WHEN upper(substr(GL_ACC,1,1)) in ('A','W')"

I just have one last question
In the end of my statement I only pull the fields that include the ranges by using this statement

end as MEASURE
from ............)
WHERE MEASURE is NOT NULL

How do I state in the end, to disclude the fields beginning with 'A' or 'W' as well.

Thank you so much again.

 

[SantaMufasa]

...WHERE measure is NOT NULL
     AND upper(substr(GL_ACC,1,1)) not in ('A','W');

Let us know if this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Olivia123]

Hey,



I think I just confused you more than it really is.



function NUMCHK (n varchar2) return number is
number_hold number;
begin
number_hold := to_number(trim);
return number_hold;
exception
when others then
return null;
end;



This FUNCTION converts character to number and returns NULL if it's not number (ex: 1000=1000, a343 = 0). I would like to convert only those fields that contain NUMBERS OnLy and those fields that might and will contain ANY CHARACTER to leave it "as is" which is Character. And not return as NULL.



Example:



100 - à100;

222 - à 222;

445---> 445;

w987 àw987, right now it does this w987 à987

A212 àA212;

a455 àa455; right now it does this a455 à455

NONE- àNONE



Latest what happens is all the ranges with NUMBERS work and they come out as MEASURE and all the ranges contain character come out as the value of the field that has character.

Ex: Right way.

"ENT-1000" "SA-1100""CC-0874" "8998" -18784.12 "Jan" "FY-2007"

"8998-SGA"

Ex NOW:

"ENT-3000" "SA-3100" "CC-A989" "6120" -2788378.21 "Jan" "FY-2007" A989 (because the field gl_cc has character that makes A989 be in measure field)



To finalize it I'm not sure if it possible, can we just alter the function where it will be stated to convert only those fields that has NUMBERS and those fields that has CHARACTER to leave it be. ( And not return as NULL)





I hope this will clarify!!!!



Sorry for all of the confusion! L