?
Solved

Column when data exists in table

Posted on 2011-05-02
9
Medium Priority
?
317 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:GRChandrashekar
  • 5
  • 3
9 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35505331
I believe that is a LEFT OUTER join.
0
 

Author Comment

by:GRChandrashekar
ID: 35505338
YES
0
 
LVL 78

Expert Comment

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

http://www.dba-oracle.com/tips_oracle_left_outer_join.htm
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:GRChandrashekar
ID: 35505359
YES added 'LEFT OUTER JOIN' to the SQL
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35505368
I don't see it in what you posted.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 35505401
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
 

Author Comment

by:GRChandrashekar
ID: 35505421
My problem is not joins

In select statement I need an output as "OPTED FOR ECS" if data exists in ECS table
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35505431
Good to know.  Thanks for he link.  That was my guess.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35505704
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question