Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Column Selection from Table in a Function (10g, HP Unix)
Multiple Column Selection from Table in a Function [message #380112] Fri, 09 January 2009 03:33 Go to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
Dear Guys,
I have a table T1 with the following columns:

phone_number
country_code
country_name
city_name

Now I have a function get_country_code to which I pass a phone number and in return I get the "country_code" information.
I write an UPDATE statement on the table T1 using the function in the following manner.

UPDATE t1
   SET country_code = get_country_code (phone_number).

This function has a query that FETCHES country_code information from a table that also has the country_name and city_name infomration as well.

Now what I want to do is to update the country_name and city_name information as well so that i don't have to write three seperate functions( get_country_code, get_country_name, get_city_name) because all this information get be selected only on one criteria.

Also the logic to calculate the information is such that i have to write that in the function.

Is there any way that I can avoid two extra functions and update country_name and city_name as well ?

[Updated on: Fri, 09 January 2009 03:37]

Report message to a moderator

Re: Multiple Column Selection from Table in a Function [message #380120 is a reply to message #380112] Fri, 09 January 2009 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create function, create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Multiple Column Selection from Table in a Function [message #380132 is a reply to message #380112] Fri, 09 January 2009 04:21 Go to previous messageGo to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
create table t1( phone_number varchar2(15), country_code varchar2(10), 
country_name varchar2(100), city_name varchar2(100));
 
create table country_information( 
country_code varchar2(10), country_name varchar2(100), city_name varchar2(100));

Insert into T1
   (PHONE_NUMBER)
 Values
   ('92423434343');
Insert into T1
   (PHONE_NUMBER)
 Values
   ('92513403074');
COMMIT;

Insert into COUNTRY_INFORMATION
   (COUNTRY_CODE, COUNTRY_NAME, CITY_NAME)
 Values
   ('9242', 'Pakistan', 'Lahore');
Insert into COUNTRY_INFORMATION
   (COUNTRY_CODE, COUNTRY_NAME, CITY_NAME)
 Values
   ('9251', 'Pakistan', 'Islamabad');
COMMIT;

/* Formatted on 2009/01/09 15:04 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION get_country_code (p_num VARCHAR2)
   RETURN VARCHAR2
IS
   v_country_code   VARCHAR2 (10);
BEGIN
   SELECT country_code
     INTO v_country_code
     FROM (SELECT   country_code
               FROM country_information,
                    (SELECT     SUBSTR (p_num, 1, LEVEL) codestring,
                                LEVEL lvl
                           FROM DUAL
                     CONNECT BY LEVEL <= LENGTH (p_num))
              WHERE country_code = codestring
           ORDER BY lvl DESC)
    WHERE ROWNUM = 1;

   RETURN v_country_code;
END;
/


Using the above information i write the UPDATE statement as given below.

UPDATE t1
   SET country_code = get_country_code (phone_number)


Now I have to write an UPDATE statement that will update country_name and city_name as well as given below.

UPDATE t1
   SET country_code = get_country_code (phone_number),
       country_name = get_country_name(phone_number),
       city_name = get_city_name(phone_number)


The functions get_country_name and get_city_name are based on the same logic as the function get_country_code except that we select different columns in each function ( country_name, city_name ).

I want to avoid these two extra functions because they are based on the same logic and still update all the columns using single function that returns all the infomration( country_code, country_name, city_name).

I want an UPDATE like
UPDATE t1
SET (country_code, country_name, city_name) = (....)


[Updated on: Fri, 09 January 2009 04:27]

Report message to a moderator

Re: Multiple Column Selection from Table in a Function [message #380138 is a reply to message #380132] Fri, 09 January 2009 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace type mytype as object (
  2  country_code varchar2(10), country_name varchar2(100), city_name varchar2(100))
  3  /

Type created.

SQL> create or replace type myarray as table of mytype
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION get_code (p_num VARCHAR2)
  2     RETURN myarray
  3  IS
  4     v_code myarray := myarray();
  5  BEGIN
  6     v_code.extend;
  7     SELECT mytype(country_code, country_name, city_name)
  8       INTO v_code(1)
  9       FROM (SELECT   country_code, country_name, city_name
 10                 FROM country_information,
 11                      (SELECT     SUBSTR (p_num, 1, LEVEL) codestring,
 12                                  LEVEL lvl
 13                             FROM DUAL
 14                       CONNECT BY LEVEL <= LENGTH (p_num))
 15                WHERE country_code = codestring
 16             ORDER BY lvl DESC)
 17      WHERE ROWNUM = 1;
 18  
 19     RETURN v_code;
 20  END;
 21  /

Function created.

SQL> update t1 set (country_code, country_name, city_name) =
  2                (select * from table(get_code(phone_number)))
  3  /

2 rows updated.

SQL> select * from t1;
PHONE_NUMBER    COUNTRY_CO COUNTRY_NAME         CITY_NAME
--------------- ---------- -------------------- --------------------
92423434343     9242       Pakistan             Lahore
92513403074     9251       Pakistan             Islamabad

2 rows selected.

Regards
Michel
Re: Multiple Column Selection from Table in a Function [message #380140 is a reply to message #380132] Fri, 09 January 2009 04:45 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You already wrote the GET functions, how about some SET functions to handle the UPDATE transparently? For example, create a SET_COUNTRY_BASED_ON_PHONENUM function and pass the phone_number to it.
Re: Multiple Column Selection from Table in a Function [message #380158 is a reply to message #380140] Fri, 09 January 2009 06:02 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Can try with this

SQL> COLUMN COUNTRY_CODE  	FORMAT A4
SQL> COLUMN COUNTRY_NAME  	FORMAT A12
SQL> COLUMN CITY_NAME     	FORMAT A12
SQL> COLUMN PHONE_NUNMBER   FORMAT A12
SQL> SELECT * FROM t11

PHONE_NUMBER    COUN COUNTRY_NAME CITY_NAME   
--------------- ---- ------------ ------------
92423434343                                   
92513403074                                   

2 rows selected.

SQL> UPDATE t11 a
   SET (country_code, country_name, city_name) =
          (SELECT country_code, country_name, city_name
             FROM country_information b
            WHERE b.country_code = REGEXP_SUBSTR (phone_number, country_code))
2 rows updated.

SQL> SELECT * FROM t11

PHONE_NUMBER    COUN COUNTRY_NAME CITY_NAME   
--------------- ---- ------------ ------------
92423434343     9242 Pakistan     Lahore      
92513403074     9251 Pakistan     Islamabad   

2 rows selected.


Thanks
Trivendra

[Updated on: Fri, 09 January 2009 06:03]

Report message to a moderator

Re: Multiple Column Selection from Table in a Function [message #380160 is a reply to message #380158] Fri, 09 January 2009 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't carefully read the function.
"b.country_code = REGEXP_SUBSTR (phone_number, country_code)" may return several rows.

Regards
Michel
Re: Multiple Column Selection from Table in a Function [message #380166 is a reply to message #380160] Fri, 09 January 2009 06:31 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks Michel,

As soon as I posted, I recognized as country_code can be match in any pattern of the phone_number.

I have done some changes( As I am not good in REGEXP).

may be this is correct

Inserted one more test case

 Insert into T11
   (PHONE_NUMBER)
 Values
   ('91113449251');

SQL> COLUMN COUNTRY_CODE  FORMAT A4
SQL> COLUMN COUNTRY_NAME  FORMAT A12
SQL> COLUMN CITY_NAME     FORMAT A12
SQL> COLUMN PHONE_NUNMBER    FORMAT A12
SQL> SELECT *
  FROM t11

PHONE_NUMBER    COUN COUNTRY_NAME CITY_NAME   
--------------- ---- ------------ ------------
92423434343                                   
92513403074                                   
91113449251                                   

3 rows selected.
SQL> UPDATE t11 a
   SET (country_code, country_name, city_name) =
          (SELECT country_code, country_name, city_name
             FROM country_information b
            WHERE b.country_code =
                     DECODE (INSTR (phone_number, country_code),
                             1, REGEXP_SUBSTR (phone_number, country_code)
                            ))
3 rows updated.
SQL> SELECT *
  FROM t11

PHONE_NUMBER    COUN COUNTRY_NAME CITY_NAME   
--------------- ---- ------------ ------------
92423434343     9242 Pakistan     Lahore      
92513403074     9251 Pakistan     Islamabad   
91113449251                                   

3 rows selected. 


I hope Michel you will provide better example for this.

Thanks and Regards
Trivendra

[Updated on: Fri, 09 January 2009 06:34]

Report message to a moderator

Re: Multiple Column Selection from Table in a Function [message #380169 is a reply to message #380166] Fri, 09 January 2009 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I said is that you can have several entries in country_information that match the criteria for instance, in addition to 9251, 925 and 92513. Then your subquery will return several rows and the update will fail.

Regards
Michel

Re: Multiple Column Selection from Table in a Function [message #380170 is a reply to message #380169] Fri, 09 January 2009 06:48 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Got It.

Thanks
Trivendra
Previous Topic: to_number problem
Next Topic: Calculate EMA
Goto Forum:
  


Current Time: Tue Dec 06 13:53:04 CST 2016

Total time taken to generate the page: 0.08988 seconds