Conc Function

In Oracle 9i, I have written function as follows


CREATE OR REPLACE function get_email (emailid_in varchar2) return varchar2 is
    hold_emailid varchar2(4000);
begin
    for x in (select emailid
                from memberemail
               where emailid = emailid_in
               order by emailid) loop
        hold_emailid := hold_emailid||', '||x.emailid;
    end loop;
    return ltrim(hold_emailid,', ');
end;
/

Now when I call this function SELECT get_email (emailid) FROM MEMBEREMAIL
WHERE MEMBER_ID=123

I get output as
abc@abc.com,abc@abc.com
yogesh_shtty@yahoo.com

in DB it is stored as
abc@abc.com
yogesh_shtty@yahoo.com

Actual output i need is as
abc@abc.com,yogesh_shtty@yahoo.com

Can anyone help me what is wrong here
GRChandrashekarAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
why not something like this then..

CREATE OR REPLACE function get_email (xx number) return varchar2 is
    hold_emailid varchar2(4000);
begin
    for x in (select emailid
                from memberemail
               where MEMBER_ID=xx
               order by emailid) loop
        hold_emailid := hold_emailid||', '||x.emailid;
    end loop;
    return ltrim(hold_emailid,', ');
end;
/

run this after you create the above function.

SELECT get_email (member_id) FROM MEMBEREMAIL
WHERE MEMBER_ID=123
and rownum = 1

or simply use a dual select.

select get_email (123) FROM dual; --> but member_id is hardcoded here and you cannot pass 123,456 etc. only one value has to go in here.


0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
the output can only come with 3 email ids only when there are 3 records found by the below query. Provide the output of this query.

SELECT emailid, get_email (emailid) FROM MEMBEREMAIL
WHERE MEMBER_ID=123

0
 
GRChandrashekarAuthor Commented:
output attached
output.xls
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
or it might be possible that the emailid field in the table has already concatenated value for one of the record.
0
 
GRChandrashekarAuthor Commented:
It is not already concatenated value
Please see the output
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this :

CREATE OR REPLACE function get_email (x number) return varchar2 is
    hold_emailid varchar2(4000);
begin
    for x in (select emailid
                from memberemail
               where MEMBER_ID=x
               order by emailid) loop
        hold_emailid := hold_emailid||', '||x.emailid;
    end loop;
    return ltrim(hold_emailid,', ');
end;
/


run this after you create the above function.

SELECT get_email (member_id) FROM MEMBEREMAIL
WHERE MEMBER_ID=123


0
 
GRChandrashekarAuthor Commented:
PLS-00364: loop index variable 'X' use is invalid
0
 
Pratima PharandeCommented:
try this

CREATE OR REPLACE function get_email (emailid_in varchar2) return varchar2 is
    hold_emailid varchar2(4000);
begin
    for x in (select emailid
                from TEST
               ) loop
        hold_emailid := hold_emailid||', '||x.emailid;
    end loop;
    return ltrim(hold_emailid,', ');
end;


SELECT distinct get_email (emailid) FROM TEST
0
 
GRChandrashekarAuthor Commented:
It comes as

abc@abc.com, yogesh_shtty@yahoo.com, yogesh.shtty@gmail.com, abc@sbc.com, abc@a.com, systems@bangaloreclub.com, sys@sys.com, abc@abc.com, abc@a.com, yogesh@shetty.com, abc@dss.com
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this :

CREATE OR REPLACE function get_email (xx number) return varchar2 is
    hold_emailid varchar2(4000);
begin
    for x in (select emailid
                from memberemail
               where MEMBER_ID=xx
               order by emailid) loop
        hold_emailid := hold_emailid||', '||x.emailid;
    end loop;
    return ltrim(hold_emailid,', ');
end;
/


run this after you create the above function.

SELECT get_email (member_id) FROM MEMBEREMAIL
WHERE MEMBER_ID=123
0
 
GRChandrashekarAuthor Commented:
Now the ouput as

abc@abc.com, yogesh_shtty@yahoo.com
abc@abc.com, yogesh_shtty@yahoo.com
0
 
dqmqCommented:
I think this is what you want:

CREATE OR REPLACE function get_email (memberid_in integer) return varchar2 is
    hold_emailid varchar2(4000);
begin
    for x in (select emailid
                from memberemail
               where memberid = memberid_in
               order by emailid) loop
        hold_emailid := hold_emailid||', '||x.emailid;
    end loop;
    return ltrim(hold_emailid,', ');
end;
/

SELECT get_email (123) FROM DUAL;


0
 
Pratima PharandeCommented:
try this

CREATE OR REPLACE function get_email (emailid_in varchar2) return varchar2 is
    hold_emailid varchar2(4000);
begin
    for x in (select emailid
                from TEST
               ) loop
        hold_emailid := hold_emailid||', '||x.emailid;
    end loop;
    return ltrim(hold_emailid,', ');
end;


SELECT distinct get_email (emailid) FROM TEST
WHERE MEMBER_ID=123
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
or you can try this :

CREATE OR REPLACE function get_email (xx number) return varchar2 is
    hold_emailid varchar2(4000);
begin
    for x in (select emailid
                from memberemail
               where MEMBER_ID=xx
               order by emailid) loop
        hold_emailid := hold_emailid||', '||x.emailid;
    end loop;
    return ltrim(hold_emailid,', ');
end;
/


run this after you create the above function.

SELECT distinct get_email (member_id) FROM MEMBEREMAIL
WHERE MEMBER_ID=123
0
 
GRChandrashekarAuthor Commented:
@ nav_kum_v: ID: 35510202
This is fine. But is there a way to use distinct in function itself.
Because I am calling this func from other query and using distinct outside the function will create problem
0
 
GRChandrashekarAuthor Commented:
ID: 35510214
@nav_kum_v:

Now in my actual query below, it does not return email ID for MEMBER_ID 123
/* Formatted on 03/05/2011 11:31:33 AM (QP5 v5.136.908.31019) */
SELECT MEMBER.MEMBER_ID,
       MEMBER.MEMBERNO,
       TITLE.DESCRIPTION AS TITLE,
       MEMBER.NAME AS NAME,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 1) AS ADDRESS1,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 2) AS ADDRESS2,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 3) AS ADDRESS3,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 4) AS ADDRESS4,
       UPPER (CITY.DESCRIPTION) || UPPER (MEMBERADDRESS.CITYOTHER)
       || NVL2 (UPPER (MEMBERADDRESS.PINCODE),
                ' - ' || UPPER (MEMBERADDRESS.PINCODE),
                NULL)
          AS CITY,
       MEMBERADDRESS.PHONENUMBER1
       || NVL2 (MEMBERADDRESS.PHONENUMBER2,
                ',' || MEMBERADDRESS.PHONENUMBER2,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER3,
                ',' || MEMBERADDRESS.PHONENUMBER3,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER4,
                ',' || MEMBERADDRESS.PHONENUMBER4,
                NULL)
          AS LANDLINE,
       MEMBERADDRESS.MOBILENUMBER1
       || NVL2 (MEMBERADDRESS.MOBILENUMBER2,
                ',' || MEMBERADDRESS.MOBILENUMBER2,
                NULL)
          AS MOBILE,
       GET_EMAIL (MEMBER_ID),
       STATE.DESCRIPTION AS STATE,
       COUNTRY.DESCRIPTION AS COUNTRY,
       NVL2 (ECS.MEMBER_ID, 'ECS MEMBER', NULL) ECSOPTION,
       CATEGORYSUBGROUP.ABBREVIATION
  FROM MEMBER
       INNER JOIN MEMBERADDRESS
          ON MEMBER.MEMBER_ID = MEMBERADDRESS.MEMBER_ID
       LEFT JOIN MEMBEREMAIL
          ON     MEMBER.MEMBER_ID = MEMBEREMAIL.MEMBER_ID
             AND MEMBEREMAIL.FLAG = 0
             AND MEMBEREMAIL.ISBILL = 1
       LEFT JOIN TITLE
          ON TITLE.TITLE_ID = MEMBER.TITLE_ID
       LEFT JOIN CITY
          ON CITY.CITY_ID = MEMBERADDRESS.CITY_ID
       LEFT JOIN STATE
          ON STATE.STATE_ID = MEMBERADDRESS.STATE_ID
       LEFT JOIN COUNTRY
          ON COUNTRY.COUNTRY_ID = MEMBERADDRESS.COUNTRY_ID
       LEFT JOIN ECS
          ON ECS.MEMBER_ID = MEMBER.MEMBER_ID
       LEFT JOIN CATEGORYSUBGROUP
          ON CATEGORYSUBGROUP.CATEGORYSUBGROUP_ID =
                MEMBER.CATEGORYSUBGROUP_ID
 WHERE MEMBERADDRESS.FLAG = 0 AND MEMBERADDRESS.ISCORRESPONDENCEADDRESS = 1
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Can you give the output of this query in excel so that i can understand what it returns.
0
 
GRChandrashekarAuthor Commented:
Here it is
output.xls
0
 
dqmqCommented:
Does your complex query work without get_mail() in the select list?  Then it will return exactly the same number of rows with the get_mail() in the select list.  The fact, that it returns the same email list for the same memberid multiple times is a consequence of the structure of your complex query, not a consequence of the get_mail() function.






0
 
GRChandrashekarAuthor Commented:
Yes it works without get_mail() in the select list
If i remove GET_EMAIL (MEMBER_ID) then also it returns 68 rows
0
 
dqmqCommented:
So what is the problem?  If you want other than 68 rows you need to change the structure of the main query.  The function submitted by both nav_kum_v and myself works properly, no?
0
 
GRChandrashekarAuthor Commented:
It does not return email ID' for 123
0
 
Pratima PharandeCommented:
r u getting emailid in first col


SELECT emailid,MEMBER.MEMBER_ID,
       MEMBER.MEMBERNO,
       TITLE.DESCRIPTION AS TITLE,
       MEMBER.NAME AS NAME,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 1) AS ADDRESS1,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 2) AS ADDRESS2,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 3) AS ADDRESS3,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 4) AS ADDRESS4,
       UPPER (CITY.DESCRIPTION) || UPPER (MEMBERADDRESS.CITYOTHER)
       || NVL2 (UPPER (MEMBERADDRESS.PINCODE),
                ' - ' || UPPER (MEMBERADDRESS.PINCODE),
                NULL)
          AS CITY,
       MEMBERADDRESS.PHONENUMBER1
       || NVL2 (MEMBERADDRESS.PHONENUMBER2,
                ',' || MEMBERADDRESS.PHONENUMBER2,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER3,
                ',' || MEMBERADDRESS.PHONENUMBER3,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER4,
                ',' || MEMBERADDRESS.PHONENUMBER4,
                NULL)
          AS LANDLINE,
       MEMBERADDRESS.MOBILENUMBER1
       || NVL2 (MEMBERADDRESS.MOBILENUMBER2,
                ',' || MEMBERADDRESS.MOBILENUMBER2,
                NULL)
          AS MOBILE,
       GET_EMAIL (MEMBER_ID),
       STATE.DESCRIPTION AS STATE,
       COUNTRY.DESCRIPTION AS COUNTRY,
       NVL2 (ECS.MEMBER_ID, 'ECS MEMBER', NULL) ECSOPTION,
       CATEGORYSUBGROUP.ABBREVIATION
  FROM MEMBER
       INNER JOIN MEMBERADDRESS
          ON MEMBER.MEMBER_ID = MEMBERADDRESS.MEMBER_ID
       LEFT JOIN MEMBEREMAIL
          ON     MEMBER.MEMBER_ID = MEMBEREMAIL.MEMBER_ID
             AND MEMBEREMAIL.FLAG = 0
             AND MEMBEREMAIL.ISBILL = 1
       LEFT JOIN TITLE
          ON TITLE.TITLE_ID = MEMBER.TITLE_ID
       LEFT JOIN CITY
          ON CITY.CITY_ID = MEMBERADDRESS.CITY_ID
       LEFT JOIN STATE
          ON STATE.STATE_ID = MEMBERADDRESS.STATE_ID
       LEFT JOIN COUNTRY
          ON COUNTRY.COUNTRY_ID = MEMBERADDRESS.COUNTRY_ID
       LEFT JOIN ECS
          ON ECS.MEMBER_ID = MEMBER.MEMBER_ID
       LEFT JOIN CATEGORYSUBGROUP
          ON CATEGORYSUBGROUP.CATEGORYSUBGROUP_ID =
                MEMBER.CATEGORYSUBGROUP_ID
 WHERE MEMBERADDRESS.FLAG = 0 AND MEMBERADDRESS.ISCORRESPONDENCEADDRESS = 1
0
 
GRChandrashekarAuthor Commented:
Yes I am getting it in first column
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Hi,

The below query does not have the table name alias prefixed for the column name, so not sure how it works.

SELECT MEMBER.MEMBER_ID,
       MEMBER.MEMBERNO,
       TITLE.DESCRIPTION AS TITLE,
       MEMBER.NAME AS NAME,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 1) AS ADDRESS1,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 2) AS ADDRESS2,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 3) AS ADDRESS3,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 4) AS ADDRESS4,
       UPPER (CITY.DESCRIPTION) || UPPER (MEMBERADDRESS.CITYOTHER)
       || NVL2 (UPPER (MEMBERADDRESS.PINCODE),
                ' - ' || UPPER (MEMBERADDRESS.PINCODE),
                NULL)
          AS CITY,
       MEMBERADDRESS.PHONENUMBER1
       || NVL2 (MEMBERADDRESS.PHONENUMBER2,
                ',' || MEMBERADDRESS.PHONENUMBER2,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER3,
                ',' || MEMBERADDRESS.PHONENUMBER3,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER4,
                ',' || MEMBERADDRESS.PHONENUMBER4,
                NULL)
          AS LANDLINE,
       MEMBERADDRESS.MOBILENUMBER1
       || NVL2 (MEMBERADDRESS.MOBILENUMBER2,
                ',' || MEMBERADDRESS.MOBILENUMBER2,
                NULL)
          AS MOBILE,
       GET_EMAIL (MEMBER_ID),  --> what is the table prefix here ????????????????????????????????
       STATE.DESCRIPTION AS STATE,
       COUNTRY.DESCRIPTION AS COUNTRY,
       NVL2 (ECS.MEMBER_ID, 'ECS MEMBER', NULL) ECSOPTION,
       CATEGORYSUBGROUP.ABBREVIATION
  FROM MEMBER
       INNER JOIN MEMBERADDRESS
          ON MEMBER.MEMBER_ID = MEMBERADDRESS.MEMBER_ID
       LEFT JOIN MEMBEREMAIL
          ON     MEMBER.MEMBER_ID = MEMBEREMAIL.MEMBER_ID
             AND MEMBEREMAIL.FLAG = 0
             AND MEMBEREMAIL.ISBILL = 1
       LEFT JOIN TITLE
          ON TITLE.TITLE_ID = MEMBER.TITLE_ID
       LEFT JOIN CITY
          ON CITY.CITY_ID = MEMBERADDRESS.CITY_ID
       LEFT JOIN STATE
          ON STATE.STATE_ID = MEMBERADDRESS.STATE_ID
       LEFT JOIN COUNTRY
          ON COUNTRY.COUNTRY_ID = MEMBERADDRESS.COUNTRY_ID
       LEFT JOIN ECS
          ON ECS.MEMBER_ID = MEMBER.MEMBER_ID
       LEFT JOIN CATEGORYSUBGROUP
          ON CATEGORYSUBGROUP.CATEGORYSUBGROUP_ID =
                MEMBER.CATEGORYSUBGROUP_ID
 WHERE MEMBERADDRESS.FLAG = 0 AND MEMBERADDRESS.ISCORRESPONDENCEADDRESS = 1

Also can you provide the output of the below query in excel ?

SELECT MEMBER.MEMBER_ID,
       MEMBER.MEMBERNO,
       TITLE.DESCRIPTION AS TITLE,
       MEMBER.NAME AS NAME,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 1) AS ADDRESS1,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 2) AS ADDRESS2,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 3) AS ADDRESS3,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 4) AS ADDRESS4,
       UPPER (CITY.DESCRIPTION) || UPPER (MEMBERADDRESS.CITYOTHER)
       || NVL2 (UPPER (MEMBERADDRESS.PINCODE),
                ' - ' || UPPER (MEMBERADDRESS.PINCODE),
                NULL)
          AS CITY,
       MEMBERADDRESS.PHONENUMBER1
       || NVL2 (MEMBERADDRESS.PHONENUMBER2,
                ',' || MEMBERADDRESS.PHONENUMBER2,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER3,
                ',' || MEMBERADDRESS.PHONENUMBER3,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER4,
                ',' || MEMBERADDRESS.PHONENUMBER4,
                NULL)
          AS LANDLINE,
       MEMBERADDRESS.MOBILENUMBER1
       || NVL2 (MEMBERADDRESS.MOBILENUMBER2,
                ',' || MEMBERADDRESS.MOBILENUMBER2,
                NULL)
          AS MOBILE,
       STATE.DESCRIPTION AS STATE,
       COUNTRY.DESCRIPTION AS COUNTRY,
       NVL2 (ECS.MEMBER_ID, 'ECS MEMBER', NULL) ECSOPTION,
       CATEGORYSUBGROUP.ABBREVIATION,
MEMBEREMAIL.member_id, MEMBEREMAIL.emailid
  FROM MEMBER
       INNER JOIN MEMBERADDRESS
          ON MEMBER.MEMBER_ID = MEMBERADDRESS.MEMBER_ID
       LEFT JOIN MEMBEREMAIL
          ON     MEMBER.MEMBER_ID = MEMBEREMAIL.MEMBER_ID
             AND MEMBEREMAIL.FLAG = 0
             AND MEMBEREMAIL.ISBILL = 1
       LEFT JOIN TITLE
          ON TITLE.TITLE_ID = MEMBER.TITLE_ID
       LEFT JOIN CITY
          ON CITY.CITY_ID = MEMBERADDRESS.CITY_ID
       LEFT JOIN STATE
          ON STATE.STATE_ID = MEMBERADDRESS.STATE_ID
       LEFT JOIN COUNTRY
          ON COUNTRY.COUNTRY_ID = MEMBERADDRESS.COUNTRY_ID
       LEFT JOIN ECS
          ON ECS.MEMBER_ID = MEMBER.MEMBER_ID
       LEFT JOIN CATEGORYSUBGROUP
          ON CATEGORYSUBGROUP.CATEGORYSUBGROUP_ID =
                MEMBER.CATEGORYSUBGROUP_ID
 WHERE MEMBERADDRESS.FLAG = 0 AND MEMBERADDRESS.ISCORRESPONDENCEADDRESS = 1
order by member.member_id
0
 
GRChandrashekarAuthor Commented:
Also can you provide the output of the below query in excel ?

Attached
ExcelOutput.xls
0
 
GRChandrashekarAuthor Commented:
GET_EMAIL (MEMBER_ID),  --> what is the table prefix here ????????????????????????????????

There is left outer join

LEFT JOIN MEMBEREMAIL
          ON     MEMBER.MEMBER_ID = MEMBEREMAIL.MEMBER_ID
             AND MEMBEREMAIL.FLAG = 0
             AND MEMBEREMAIL.ISBILL = 1
0
 
dqmqCommented:
What appears in the get_email(memberID) column for memberid = 123?
0
 
GRChandrashekarAuthor Commented:
Now changed this line
 GET_EMAIL (MEMBER_ID),  --> what is the table prefix here ????????????????????????????????

GET_EMAIL (MEMBER.MEMBER_ID),

now ouput comes but it is not distinct
0
 
dqmqCommented:
How many rows do you get from:

Select * from member where member_id=123;

select * from ecs where member_id = 123;

select * from MEMBERADDRESS where MEMBER_ID - 123;
0
 
dqmqCommented:



>now ouput comes
Very good. kudos to  nav_kum_v for catching that.

>but it is not distinct
And it should not be...unless you restructure the main query to return member_id only once

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Ok. the prefix made it to get the output but why it is not distinct is because you have duplicate records in the excel with the same email id for more than 1 record for member id 123. For example, there are 2 records having the same email id and another 2 records having the same email id.

abc@abc.com
abc@abc.com
yogesh_shtty@yahoo.com
yogesh_shtty@yahoo.com

Can you confirm that the current output for the get_email(..) column is the below :

abc@abc.com,abc@abc.com, yogesh_shtty@yahoo.com, yogesh_shtty@yahoo.com

Try to recreate the function and then run the query again to test it.

CREATE OR REPLACE function get_email (xx number) return varchar2 is
    hold_emailid varchar2(4000);
begin
    for x in (select distinct emailid
                from memberemail
               where MEMBER_ID=xx
               order by emailid) loop
        hold_emailid := hold_emailid||', '||x.emailid;
    end loop;
    return ltrim(hold_emailid,', ');
end;
/

Thanks
0
 
GRChandrashekarAuthor Commented:
Output attached
ExcelOutput.xls
0
 
GRChandrashekarAuthor Commented:
Row number 3 & 5 are duplicates
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Ok. Fine now the email id column issue is already resolved.

Now the question is why you are getting 2 extra rows in your output which is because of the join conditions etc used by your query and i would consider that as a new question as the investigation required for that is different to what is being discussed here as part of the email id column/function issue.
0
 
dqmqCommented:
I agree, the function works and credit should be given.

As for the dup rows, that occurs because memberID = 123 has two records in the memberemail table.  So, each email address produces a separate row with all email addresses for the same member concatenated together (by virtue of the function).

Try reducing your email set to unique id's, as attached
/* Formatted on 03/05/2011 11:31:33 AM (QP5 v5.136.908.31019) */
SELECT MEMBER.MEMBER_ID,
       MEMBER.MEMBERNO,
       TITLE.DESCRIPTION AS TITLE,
       MEMBER.NAME AS NAME,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 1) AS ADDRESS1,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 2) AS ADDRESS2,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 3) AS ADDRESS3,
       DELIMITED_ELEMENT (UPPER (ADDRESS), '$', 4) AS ADDRESS4,
       UPPER (CITY.DESCRIPTION) || UPPER (MEMBERADDRESS.CITYOTHER)
       || NVL2 (UPPER (MEMBERADDRESS.PINCODE),
                ' - ' || UPPER (MEMBERADDRESS.PINCODE),
                NULL)
          AS CITY,
       MEMBERADDRESS.PHONENUMBER1
       || NVL2 (MEMBERADDRESS.PHONENUMBER2,
                ',' || MEMBERADDRESS.PHONENUMBER2,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER3,
                ',' || MEMBERADDRESS.PHONENUMBER3,
                NULL)
       || NVL2 (MEMBERADDRESS.PHONENUMBER4,
                ',' || MEMBERADDRESS.PHONENUMBER4,
                NULL)
          AS LANDLINE,
       MEMBERADDRESS.MOBILENUMBER1
       || NVL2 (MEMBERADDRESS.MOBILENUMBER2,
                ',' || MEMBERADDRESS.MOBILENUMBER2,
                NULL)
          AS MOBILE,
       GET_EMAIL (member.MEMBER_ID),
       STATE.DESCRIPTION AS STATE,
       COUNTRY.DESCRIPTION AS COUNTRY,
       NVL2 (ECS.MEMBER_ID, 'ECS MEMBER', NULL) ECSOPTION,
       CATEGORYSUBGROUP.ABBREVIATION
  FROM MEMBER
       INNER JOIN MEMBERADDRESS
          ON MEMBER.MEMBER_ID = MEMBERADDRESS.MEMBER_ID
       LEFT JOIN 

(Select member_ID from MEMBEREMAIL where
      MEMBEREMAIL.FLAG = 0
      AND MEMBEREMAIL.ISBILL = 1
      group by member_id) email
      ON MEMBER.MEMBER_ID = EMAIL.MEMBER_ID


       LEFT JOIN TITLE
          ON TITLE.TITLE_ID = MEMBER.TITLE_ID
       LEFT JOIN CITY
          ON CITY.CITY_ID = MEMBERADDRESS.CITY_ID
       LEFT JOIN STATE
          ON STATE.STATE_ID = MEMBERADDRESS.STATE_ID
       LEFT JOIN COUNTRY
          ON COUNTRY.COUNTRY_ID = MEMBERADDRESS.COUNTRY_ID
       LEFT JOIN ECS
          ON ECS.MEMBER_ID = MEMBER.MEMBER_ID
       LEFT JOIN CATEGORYSUBGROUP
          ON CATEGORYSUBGROUP.CATEGORYSUBGROUP_ID =
                MEMBER.CATEGORYSUBGROUP_ID
 WHERE MEMBERADDRESS.FLAG = 0 AND MEMBERADDRESS.ISCORRESPONDENCEADDRESS = 1

Open in new window

0
All Courses

From novice to tech pro — start learning today.