Help with a Function 1

[Olivia123]

Hello,
I need help to write a SQL Statement/ Function that would read this table below...

glrd_acct*glrd_acct1*glrd_cc*glrd_cc1*glrd_user1*code_desc
6401 * 6405 * 1 * 900* 121.01 * SS
8000 * 8999 * 700 * 755 * 150 * AA
9100 * 9120 * A030 * A030 * 389.DUBU * EE

Basically, I need if Account is between glrd_acct and glrd_acct1 and has a CallCenter between glrd_cc and glrd_cc1.....THEN '"'||trim(Account)||'-code_desc"'

Thank you

 

[LKBrwnDBA]

Did you try:

Select '"'||trim(Account)||'-'||code_desc||'"' 
  From ThisTable
 Where Account between glrd_acct and glrd_acct1
   And CallCenter between glrd_cc and glrd_cc1;

-- OR in a function:

Create Or Replace Function D_Acct
       (Account Varchar2, CallCenter Varchar2)
Return Varchar2 Is
V_Acct Varchar2(30):='';
Begin
  Select '"'||trim(Account)||'-'||code_desc||'"'  
    Into V_Acct
    From ThisTable
   Where Account Between glrd_acct And glrd_acct1
     And CallCenter Between glrd_cc And glrd_cc1;
  Return V_Acct;
End;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[BJCooperIT]

Something like this? It assumes there will only be one matching hit:

CREATE OR REPLACE FUNCTION f_olivia
(
    in_acct         IN VARCHAR2,
    in_callcenter   IN VARCHAR2
) 
RETURN VARCHAR2 
IS
    v_result    VARCHAR2(50) := NULL;
BEGIN
    SELECT  '"'||trim(in_acct)||'-'||code_desc||'"'
    INTO    v_result
    FROM    olivias_table
    WHERE   trim(in_acct)   between glrd_acct and glrd_acct1 
    AND     in_callcenter   between glrd_cc   and glrd_cc1;
    RETURN(v_result);
EXCEPTION
    WHEN no_data_found THEN RETURN(v_result);
END f_olivia;

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[Olivia123]

Hello,
Thanks to everyone that replied, I really appriciate it...
But I will use this Function for my ranges to be read automaticaly...I just have one more problem

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, I can't really put it together..

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

Please let me know what would be the best way to write it...

Thank you

 

[carp]

OK - where do expect the values for account, callcenter, and p_usr_name to come from? If they are the arguments you need to provide, then you need to figure out how to provide them. Once you have that worked out, then you can do something like

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

 

[Olivia123]

Hey,

Thank you so much for helping, I still don't really understand what you mean by (If they are the arguments you need to provide, then you need to figure out how to provide them)...

Where it says ACCOUNT in the Function, I need it to understand that it is the same as KL_ACC, or CALLCENTER same as KL_CC...and KL_ACC and KL_CC are coming from a different table...

Please let me know what am I doing wrong, or if there is anything I should change in the function....

Thank you

 

[carp]

Ah! That helps some. Since your arguments are coming from different tables, you will need to include them in your query. Something like this:

 SELECT   chckAccount(t1.KL_ACC, 
                      t2.kl_cc, 
                      t3.kl_user_name) check_account, 
       t1.KL_SA,
       t2.KL_CC,
       t2.KL_ENT,
       SUM(t1.KL_AMT)
  FROM my_table1 t1, 
       my_table2 t2, 
       my_table3 t3
 WHERE <<conditions to select the desired 
         rows from the tables and define
         how they relate to one another>>