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]

...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?

Surely we can do that, but the problem with that modification is...how will the RETURN value ever differ from the incoming-argument value? (Answer: it won't.) Therefore, I propose that if you want non-numeric values to stay unchanged, that you use Oracle's NVL ("NULL Value") function to give you the results you want:

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)||'"'
                    [b]ELSE NVL(numchk(GL_ACC),GL_ACC)[/b]
               end as MEASURE
         from .......................)
 WHERE measure is NOT NULL;

The above ELSE... clause says that if neither of the previous WHERE clauses returns a numeric value that fits the ranges you specify, then:


"try to convert GL_ACC one last time to a valid number; if the result of numchk is NULL, then make MEASURE become simply the original (unconverted) value of GL_ACC."

Then finally, if GL_ACC's original value happened to be NULL to start with, then the "WHERE measure is NOT NULL" suppress that row. (If you want to display rows WHERE GL_ACC's orginal value IS NULL, then just remove the final WHERE clause.)

Let us know how this fits your situation.


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

 

[Olivia123]

Hey,
You really helped me out last time, thank you so much.
I was wondering if you can help me out this time.....

I have this Function that I'll be using for my ranges to be read automaticaly...

CREATE OR REPLACE FUNCTION chckAccount
(ACCOUNT NUMBER,
CALLCENTER NUMBER,
P_USR_NAME VARCHAR2)

RETURN VARCHAR2 AS V_ACCT NUMBER(10);
V_ACCT1 NUMBER(10);
V_CC NUMBER(10);
V_CC1 NUMBER(10);
C_CDEC VARCHAR2(5);

BEGIN

SELECT glrd_acct, glrd_acct1, glrd_cc, glrd_cc1, glrd_desc
INTO V_ACCT, V_ACCT1, V_CC, V_CC1, C_CDEC

FROM <<TABLE NAME>>

WHERE glrd_user1 = P_USR_NAME;

IF ((ACCOUNT>V_ACCT AND ACCOUNT<V_ACCT1) AND
(CALLCENTER>V_CC AND CALLCENTER<V_CC1))

THEN

RETURN ('"'||trim(ACCOUNT)||'-'||C_CDEC||'"' );

END IF;

RETURN NULL;

END;


This Function works, but, when I try to use it in my SQL Statement, it doesn't work.....

SELECT KL_ACC,
KL_SA,
KL_CC,
KL_ENT,
SUM(KL_AMT)
FROM <<TABLE_1>>

I need to use this function with KL_ACC.....So, then when I run it, it reads the function and adds the description (glrd_desc) to KL_ACC..Right away..


Thank you

 

[SantaMufasa]

This Function works, but, when I try to use it in my SQL Statement, it doesn't work

Can you post and example of its working?...and an example of what (bad) happens when you use it in your SQL statement?

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

 

[Olivia123]

This Function works when I run it, it has no errors...

Then I try to use it in my query, and it wont work

SELECT chckAccount(KL_ACC),
KL_SA,
KL_CC,
KL_ENT,
SUM(KL_AMT)
FROM <<TABLE_1>>.....

Basically KL_ACC and KL_CC are my transactional tables and I need it to read the this Function to add a description to each account.....
This is what my ranges table looks like, and from this table it picks up a desc according to the glrd_acc between glrd_acct1 and glrd_cc between glrd_cc1

glrd_acct/glrd_acct1/glrd_cc/glrd_cc1/glrd_user1/code_desc
6401 / 6405/ 0/ 900/ 121.02/ SS
8000 / 8999/ 700/ 760/ 150/ CC
8000 / 8999/ 801/ 802/ 150/ cc

Thank you again for helping me out

 

[SantaMufasa]

This Function works when I run it, it has no errors...

Olivia, I presume that when you say, "...when I run it..." that you mean when you execute the "CREATE OR REPLACE FUNCTION..." statement, is that right? When that statement runs "successfully" (i.e., responds with "Function created", that simply means that you obeyed all of the "syntax" rules for creating a function. It does not guarantee that you will not have either run-time errors or logic errors.


So, when you say,

Then I try to use it in my query, and it wont work

...I infer from this that you receive either a run-time error (i.e., execution abnormally terminates with a run-time error message, or the function runs without an abnormal termination, but the results are not what you want (which is a logic error).


Therefore, I need you to copy and paste into a reply here, from your computer screen, your invoking SQL statement that used the function, along with the "bad thing" (either the run-time error or the logic error) that happens when you invoke the function. (If the problem is a logic error, please refer to the result that you paste here, explaining what it is about the results that you don't want or expect.)

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

 

[Olivia123]

Thanks again

This my table_1, where I have my ranges...

glrd_acct/glrd_acct1/glrd_cc/glrd_cc1/glrd_user1/code_desc
6401 / 6405/ 0/ 900/ 121.02/ SS
8000 / 8999/ 700/ 760/ 150/ CC
8000 / 8999/ 801/ 802/ 150/ cc


This is my transactional table_2 where I have my accounts

KL_ACC/ KL_SA / KL_CC / KL_ENT / KL_AMT
8761 / 003 / 730 / 1000 / 890.76

I need to add to my KL_ACC a CODE_DESC from table_1 (ex 8761-CC), I can't have the ranges hard coded......

I use this Function to read the ranges and to pick up the desc....

CREATE OR REPLACE FUNCTION chckAccount (ACCOUNT NUMBER,
CALLCENTER NUMBER,
P_USR_NAME VARCHAR2)


RETURN VARCHAR2 AS V_ACCT NUMBER(10);
V_ACCT1 NUMBER(10);
V_CC NUMBER(10);
V_CC1 NUMBER(10);
C_CDEC VARCHAR2(5);

BEGIN

SELECT glrd_acct, glrd_acct1, glrd_cc, glrd_cc1, glrd_desc
INTO V_ACCT, V_ACCT1, V_CC, V_CC1, C_CDEC

FROM D_ACCT_RNGS_ENT1000

WHERE glrd_user1 = P_USR_NAME;

IF ((ACCOUNT>V_ACCT AND ACCOUNT<V_ACCT1) AND
(CALLCENTER>V_CC AND CALLCENTER<V_CC1))

THEN

RETURN ('"'||trim(ACCOUNT)||'-'||C_CDEC||'"' );

END IF;

RETURN NULL;

END;

I'm having a hard time to write a SQL Query so that when I ran my transactional tables which is KL_ACC and KL_CC for it to read the function go to table_1 pick up the CODE_DESC according to the account and callceneter, and add it to my account in this format "KL_ACC-CC"

SELECT KL_ACC,<<w/desc>>
KL_SA,
KL_CC,
KL_ENT,
SUM(KL_AMT)

This is what i need to select in my output file...

Thank you

 

[Olivia123]

I've changed my Function can u please let me know what am I doing wrong, because still when I ran my Select statement and use the Function in it , it won't recognize the Function name.....
Here is my function

CREATE OR REPLACE FUNCTION chckAccount (a.table_2.KL_ACC NUMBER,
a.table_1.KL_CC NUMBER,
P_USR_NAME VARCHAR2)

RETURN VARCHAR2ASV_ACCT NUMBER(10);
V_ACCT1 NUMBER(10);
V_CC NUMBER(10);
V_CC1 NUMBER(10);
C_CDEC VARCHAR2(5);

BEGIN

SELECT b.glrd_acct,b.glrd_acct1,b.glrd_cc,b.glrd_cc1,b.code_desc
INTO V_ACCT,V_ACCT1,V_CC,V_CC1,C_CDEC

FROM TABLE_2 a,
TABLE_1 b

WHERE glrd_user1 = P_USR_NAME;

IF ((KL_ACC>V_ACCT AND KL_ACC<V_ACCT1) AND
(KL_CC>V_CC AND KL_CC<V_CC1))

THEN
RETURN ('"'||trim(ACCOUNT)||'-'||C_CDEC||'"' );
END IF;
RETURN NULL;
END;

And this is my SQL, which is very simple

SELECT chckAccount (KL_ACC,
KL_CC),
KL_SA,
KL_ENT,
SUM(KL_AMT)

FROM Table_2

GROUP BY .........

It won't recognize the chckAccount, the error mes that I get, that it is an invalid identifier.

Thank you in advance.

 

[SantaMufasa]

Olivia,

The reason that you have not had a response from me (and others) is that you have not posted the vital information that responds to the requests-for-information, above.

We can offer quality responses if you please post a literal screen copy-and-paste (not a manual re-typing and not excerpts of code only) of the actual "CREATE or REPLACE FUNCTION..." execution (along with the actual screen results confirming "Function created.") and also a literal screen copy-and-paste of your attempt to call the function from your SQL statement, (again, along with the error messages/results).

If you cannot do the above, I'm afraid that at least I (and possibly others) cannot be helpful.

I look forward to your screen-copy-and-pastes. I really would like to be helpful to you.

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

 

[Beilstwh]

I can see one error immediately. You are invoking your chckaccount function in your select with only two arguments when your function definition's has three arguments.

Bill
Oracle DBA/Developer
New York State, USA

 

[Olivia123]

Hello,

This is an exact copy and paste of my Function

[CREATE OR REPLACE FUNCTION chckAccount( ACCOUNT NUMBER,
CALLCENTER NUMBER,
P_USR_NAME VARCHAR2)

RETURN VARCHAR2ASV_ACCT NUMBER(10);
V_ACCT1 NUMBER(10);
V_CC NUMBER(10);
V_CC1 NUMBER(10);
C_CDEC VARCHAR2(5);

BEGIN

SELECT b.glrd_acct, b.glrd_acct1, b.glrd_cc, b.glrd_cc1, b.code_desc
INTO V_ACCT, V_ACCT1, V_CC, V_CC1, C_CDEC

FROM RANGES_ENT

WHERE glrd_user1 = P_USR_NAME;

IF ((ACCOUNT BETWEEN (V_ACCT AND V_ACCT1)) AND ((CALLCENTER BETWEEN (V_CC AND V_CC1)))

THEN

RETURN ('"'||trim(ACCOUNT)||'-'||C_CDEC||'"' );

END IF;

RETURN NULL;

END; ]

This is the error that I get

[ORA - 00900: invalid SQL statement]

I believe that my main problem is that my Function is written wrong.....I'm just hoping you can take a look at it, and maybe let me know me what I might be doing wrong...

 

[Beilstwh]

TRY THE FOLLOWING

CREATE OR REPLACE FUNCTION chckAccount( ACCOUNT NUMBER,
CALLCENTER NUMBER,
P_USR_NAME VARCHAR2)
RETURN VARCHAR2 IS
C_DESC VARCHAR2(5);
BEGIN
SELECT b.code_desc
INTO C_DESC
INTO CNT
FROM RANGES_ENT B
WHERE ACCOUNT BETWEEN b.glrd_acct AND b.glrd_acct1
AND CALLCENTER BETWEEN b.glrd_cc AND b.glrd_cc1;

RETURN ('"'||trim(ACCOUNT)||'-'||C_CDEC||'"' );
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/

Bill
Oracle DBA/Developer
New York State, USA