Column when data exists in table

Dear Experts, In the below query, I need a small help. If Data exists in " LEFT JOIN ECS" I need to add output column as "ECS OPTION" and null if there is no data in " LEFT JOIN ECS" then null

/* Formatted on 02/05/2011 8:23:35 PM (QP5 v5.136.908.31019) */
  SELECT DISTINCT
         MEMBER.MEMBER_ID,
         MEMBER.NAME AS SORTIDENTIFIER,
         MEMBER.MEMBERNO,
         TITLE.DESCRIPTION || MEMBER.NAME AS NAME,
         DELIMITED_ELEMENT (ADDRESS, '$', 1) AS ADDRESS1,
         DELIMITED_ELEMENT (ADDRESS, '$', 2) AS ADDRESS2,
         DELIMITED_ELEMENT (ADDRESS, '$', 3) AS ADDRESS3,
         DELIMITED_ELEMENT (ADDRESS, '$', 4) AS ADDRESS4,
            CITY.DESCRIPTION
         || MEMBERADDRESS.CITYOTHER
         || NVL2 (MEMBERADDRESS.PINCODE, ' - ' || 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,
         MEMBEREMAIL.EMAILID,
         STATE.DESCRIPTION AS STATE,
         COUNTRY.DESCRIPTION AS COUNTRY,
         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 MEMBER.MEMBERNO = MEMBEREMAIL.USERID
         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
         AND MEMBEREMAIL.FLAG = 0
ORDER BY SUBSTR (MEMBER.MEMBERNO, 1, INSTR (MEMBER.MEMBERNO, '-') - 1),
         TO_NUMBER (
            SUBSTR (MEMBER.MEMBERNO, INSTR (MEMBER.MEMBERNO, '-') + 1)) ASC
GRChandrashekarAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I'm not sure I'm understanding but if you want a column added to the output try this:


nvl2(ECS.MEMBER_ID,'OPTED FOR ECS',null) mycolumn
0
 
slightwv (䄆 Netminder) Commented:
I believe that is a LEFT OUTER join.
0
 
GRChandrashekarAuthor Commented:
YES
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
slightwv (䄆 Netminder) Commented:
OK, and you added 'LEFT OUTER JOIN' to the SQL?

http://www.dba-oracle.com/tips_oracle_left_outer_join.htm
0
 
GRChandrashekarAuthor Commented:
YES added 'LEFT OUTER JOIN' to the SQL
0
 
slightwv (䄆 Netminder) Commented:
I don't see it in what you posted.
0
 
johnsoneSenior Oracle DBACommented:
The word OUTER is optional and not required.  LEFT JOIN is the same as LEFT OUTER JOIN.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/queries006.htm#i2054062
0
 
GRChandrashekarAuthor Commented:
My problem is not joins

In select statement I need an output as "OPTED FOR ECS" if data exists in ECS table
0
 
slightwv (䄆 Netminder) Commented:
Good to know.  Thanks for he link.  That was my guess.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.