Reputation: 682

Cannot filter out NULL contains column

I have joined 5 tables and I got the final result, this is the query given below.

SELECT *
FROM (SELECT O.CUSTADDRESSID,
             O.ACCOUNTNO,
             O.ADDRESSTYPE,
             O.ADDRESSLINE1,
             O.ADDRESSLINE2,
             O.ADDRESSLINE3,
             O.CITY,
             O.STATE,
             O.COUNTRY,
             O.ZIP1,
             O.ISACTIVE AS ISACTIVE_ADDRESS,
             O.ISCOMMUNICATION AS ISCOMMUNICATION_ADDRESS,
             D.ORGANISATIONNAME,
             D.DATEOFINCORPORATION,
             D.PANCARDNUMBER,
             D.ORGANIZATIONTYPEID,
             P.CUSTMAILID,
             P.EMAILTYPE,
             P.EMAILADDRESS,
             P.ISACTIVE AS ISACTIVE_MAILID,
             P.ISCOMMUNICATION AS ISCOMMUNICATION_MAILID,
             Q.LOGINID,
             Q.USERNAME,
             Q.PASSWORD,
             Q.LAST_LOGINDATE,
             Q.LAST_PWD_MODIFIEDDATE,
             Q.CURRENT_PWD_EXPIRYDATE,
             Q.PWD_ATTEMPTS_COUNT,
             Q.PINNUMBER,
             Q.ISLOCKED,
             Q.THEMES,
             Q.LANGUAGES,
             Q.STATUSID,
             Q.USERTYPEID,
             Q.ROLENAME,
             Q.SQ_ATTEMPTCOUNT,
             Q.SQ_LOCKOUTTIME,
             R.CUSTPHONEID,
             R.PHONETYPE,
             R.PHONENUMBER,
             R.EXTENTION,
             R.ISACTIVE AS ISACTIVE_PHONES,
             R.ISCOMMUNICATION AS ISCOMMUNICATION_PHONES
      FROM ISSUER.TOLLPLUS.TP_CUSTOMER_ADDRESSES O
           FULL OUTER JOIN ISSUER.TOLLPLUS.TP_CUSTOMER_BUSINESS D ON O.ACCOUNTNO = D.ACCOUNTNO
           FULL OUTER JOIN ISSUER.TOLLPLUS.TP_CUSTOMER_EMAILS P ON D.ACCOUNTNO = P.ACCOUNTNO
           FULL OUTER JOIN ISSUER.TOLLPLUS.TP_CUSTOMER_LOGINS Q ON P.ACCOUNTNO = Q.ACCOUNTNO
           FULL OUTER JOIN ISSUER.TOLLPLUS.TP_CUSTOMER_PHONES R ON Q.ACCOUNTNO = R.ACCOUNTNO) A
GROUP BY CUSTADDRESSID,
         ACCOUNTNO,
         ADDRESSTYPE,
         ADDRESSLINE1,
         ADDRESSLINE2,
         ADDRESSLINE3,
         CITY,
         STATE,
         COUNTRY,
         ZIP1,
         ISACTIVE_ADDRESS,
         ISCOMMUNICATION_ADDRESS,
         ORGANISATIONNAME,
         DATEOFINCORPORATION,
         PANCARDNUMBER,
         ORGANIZATIONTYPEID,
         CUSTMAILID,
         EMAILTYPE,
         EMAILADDRESS,
         ISACTIVE_MAILID,
         ISCOMMUNICATION_MAILID,
         LOGINID,
         USERNAME,
         PASSWORD,
         LAST_LOGINDATE,
         LAST_PWD_MODIFIEDDATE,
         CURRENT_PWD_EXPIRYDATE,
         PWD_ATTEMPTS_COUNT,
         PINNUMBER,
         ISLOCKED,
         THEMES,
         LANGUAGES,
         STATUSID,
         USERTYPEID,
         ROLENAME,
         SQ_ATTEMPTCOUNT,
         SQ_LOCKOUTTIME,
         CUSTPHONEID,
         PHONETYPE,
         PHONENUMBER,
         EXTENTION,
         ISACTIVE_PHONES,
         ISCOMMUNICATION_PHONES
ORDER BY ACCOUNTNO;

4 result rows are given for example:

    CUSTADDRESSID   ACCOUNTNO   ADDRESSTYPE ADDRESSLINE1    ADDRESSLINE2    ADDRESSLINE3    CITY    STATE   COUNTRY ZIP1    ISACTIVE_ADDRESS    ISCOMMUNICATION_ADDRESS ORGANISATIONNAME    DATEOFINCORPORATION PANCARDNUMBER   ORGANIZATIONTYPEID  CUSTMAILID  EMAILTYPE   EMAILADDRESS    ISACTIVE_MAILID ISCOMMUNICATION_MAILID  LOGINID USERNAME    PASSWORD    LAST_LOGINDATE  LAST_PWD_MODIFIEDDATE   CURRENT_PWD_EXPIRYDATE  PWD_ATTEMPTS_COUNT  PINNUMBER   ISLOCKED    THEMES  LANGUAGES   STATUSID    USERTYPEID  ROLENAME    SQ_ATTEMPTCOUNT SQ_LOCKOUTTIME  CUSTPHONEID PHONETYPE   PHONENUMBER EXTENTION   ISACTIVE_PHONES ISCOMMUNICATION_PHONES
    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    VISHWAS ROADLINES BELGAUM   2009-04-01 00:00:00.000 AWBPK6828R  2103    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
CUSTADDRESSID   ACCOUNTNO   ADDRESSTYPE ADDRESSLINE1    ADDRESSLINE2    ADDRESSLINE3    CITY    STATE   COUNTRY ZIP1    ISACTIVE_ADDRESS    ISCOMMUNICATION_ADDRESS ORGANISATIONNAME    DATEOFINCORPORATION PANCARDNUMBER   ORGANIZATIONTYPEID  CUSTMAILID  EMAILTYPE   EMAILADDRESS    ISACTIVE_MAILID ISCOMMUNICATION_MAILID  LOGINID USERNAME    PASSWORD    LAST_LOGINDATE  LAST_PWD_MODIFIEDDATE   CURRENT_PWD_EXPIRYDATE  PWD_ATTEMPTS_COUNT  PINNUMBER   ISLOCKED    THEMES  LANGUAGES   STATUSID    USERTYPEID  ROLENAME    SQ_ATTEMPTCOUNT SQ_LOCKOUTTIME  CUSTPHONEID PHONETYPE   PHONENUMBER EXTENTION   ISACTIVE_PHONES ISCOMMUNICATION_PHONES
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    WHITEINK CONSULTANCY PRIVATE LIMITED    2013-08-27 00:00:00.000 AABCW4232E  3041    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
CUSTADDRESSID   ACCOUNTNO   ADDRESSTYPE ADDRESSLINE1    ADDRESSLINE2    ADDRESSLINE3    CITY    STATE   COUNTRY ZIP1    ISACTIVE_ADDRESS    ISCOMMUNICATION_ADDRESS ORGANISATIONNAME    DATEOFINCORPORATION PANCARDNUMBER   ORGANIZATIONTYPEID  CUSTMAILID  EMAILTYPE   EMAILADDRESS    ISACTIVE_MAILID ISCOMMUNICATION_MAILID  LOGINID USERNAME    PASSWORD    LAST_LOGINDATE  LAST_PWD_MODIFIEDDATE   CURRENT_PWD_EXPIRYDATE  PWD_ATTEMPTS_COUNT  PINNUMBER   ISLOCKED    THEMES  LANGUAGES   STATUSID    USERTYPEID  ROLENAME    SQ_ATTEMPTCOUNT SQ_LOCKOUTTIME  CUSTPHONEID PHONETYPE   PHONENUMBER EXTENTION   ISACTIVE_PHONES ISCOMMUNICATION_PHONES
1   10000000    Business    Madhapur    Mega Hils   NULL    Hyderbad    AP  IND 789564  1   1   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
CUSTADDRESSID   ACCOUNTNO   ADDRESSTYPE ADDRESSLINE1    ADDRESSLINE2    ADDRESSLINE3    CITY    STATE   COUNTRY ZIP1    ISACTIVE_ADDRESS    ISCOMMUNICATION_ADDRESS ORGANISATIONNAME    DATEOFINCORPORATION PANCARDNUMBER   ORGANIZATIONTYPEID  CUSTMAILID  EMAILTYPE   EMAILADDRESS    ISACTIVE_MAILID ISCOMMUNICATION_MAILID  LOGINID USERNAME    PASSWORD    LAST_LOGINDATE  LAST_PWD_MODIFIEDDATE   CURRENT_PWD_EXPIRYDATE  PWD_ATTEMPTS_COUNT  PINNUMBER   ISLOCKED    THEMES  LANGUAGES   STATUSID    USERTYPEID  ROLENAME    SQ_ATTEMPTCOUNT SQ_LOCKOUTTIME  CUSTPHONEID PHONETYPE   PHONENUMBER EXTENTION   ISACTIVE_PHONES ISCOMMUNICATION_PHONES
21  10000789    Business    IBTTA Interoperability          Plano   AP  IND 533126  1   1   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

But the ACCOUNTNO contains NULL. I want to filter out the NULL values contains in ACCOUNTNO So I gave WHERE ACCOUNTNO != NULLbut THE RESULT I AM GETTING NO VALUES, EMPTY ROWS

CUSTADDRESSID   ACCOUNTNO   ADDRESSTYPE ADDRESSLINE1    ADDRESSLINE2    ADDRESSLINE3    CITY    STATE   COUNTRY ZIP1    ISACTIVE_ADDRESS    ISCOMMUNICATION_ADDRESS ORGANISATIONNAME    DATEOFINCORPORATION PANCARDNUMBER   ORGANIZATIONTYPEID  CUSTMAILID  EMAILTYPE   EMAILADDRESS    ISACTIVE_MAILID ISCOMMUNICATION_MAILID  LOGINID USERNAME    PASSWORD    LAST_LOGINDATE  LAST_PWD_MODIFIEDDATE   CURRENT_PWD_EXPIRYDATE  PWD_ATTEMPTS_COUNT  PINNUMBER   ISLOCKED    THEMES  LANGUAGES   STATUSID    USERTYPEID  ROLENAME    SQ_ATTEMPTCOUNT SQ_LOCKOUTTIME  CUSTPHONEID PHONETYPE   PHONENUMBER EXTENTION   ISACTIVE_PHONES ISCOMMUNICATION_PHONES

Need Help. Thanks.

Upvotes: 0

Answers (2)

Thom A

Reputation: 95698

This doesn't answer the OP's question, however, there no need to use a subquery here, nor that HUGE GROUP BY clause. Simply use DISTINCT (Using DISTINCT with SELECT):

SELECT DISTINCT
       O.CUSTADDRESSID,
       O.ACCOUNTNO,
       O.ADDRESSTYPE,
       O.ADDRESSLINE1,
       O.ADDRESSLINE2,
       O.ADDRESSLINE3,
       O.CITY,
       O.STATE,
       O.COUNTRY,
       O.ZIP1,
       O.ISACTIVE AS ISACTIVE_ADDRESS,
       O.ISCOMMUNICATION AS ISCOMMUNICATION_ADDRESS,
       D.ORGANISATIONNAME,
       D.DATEOFINCORPORATION,
       D.PANCARDNUMBER,
       D.ORGANIZATIONTYPEID,
       P.CUSTMAILID,
       P.EMAILTYPE,
       P.EMAILADDRESS,
       P.ISACTIVE AS ISACTIVE_MAILID,
       P.ISCOMMUNICATION AS ISCOMMUNICATION_MAILID,
       Q.LOGINID,
       Q.USERNAME,
       Q.PASSWORD,
       Q.LAST_LOGINDATE,
       Q.LAST_PWD_MODIFIEDDATE,
       Q.CURRENT_PWD_EXPIRYDATE,
       Q.PWD_ATTEMPTS_COUNT,
       Q.PINNUMBER,
       Q.ISLOCKED,
       Q.THEMES,
       Q.LANGUAGES,
       Q.STATUSID,
       Q.USERTYPEID,
       Q.ROLENAME,
       Q.SQ_ATTEMPTCOUNT,
       Q.SQ_LOCKOUTTIME,
       R.CUSTPHONEID,
       R.PHONETYPE,
       R.PHONENUMBER,
       R.EXTENTION,
       R.ISACTIVE AS ISACTIVE_PHONES,
       R.ISCOMMUNICATION AS ISCOMMUNICATION_PHONES
FROM ISSUER.TOLLPLUS.TP_CUSTOMER_ADDRESSES O
     FULL OUTER JOIN ISSUER.TOLLPLUS.TP_CUSTOMER_BUSINESS D ON O.ACCOUNTNO = D.ACCOUNTNO
     FULL OUTER JOIN ISSUER.TOLLPLUS.TP_CUSTOMER_EMAILS P ON D.ACCOUNTNO = P.ACCOUNTNO
     FULL OUTER JOIN ISSUER.TOLLPLUS.TP_CUSTOMER_LOGINS Q ON P.ACCOUNTNO = Q.ACCOUNTNO
     FULL OUTER JOIN ISSUER.TOLLPLUS.TP_CUSTOMER_PHONES R ON Q.ACCOUNTNO = R.ACCOUNTNO
ORDER BY O.ACCOUNTNO;

Also, is the problem you're having due to you using FULL OUTER JOINs? I wonder if your rows of NULL problem will go away if you use an INNER JOIN..?

Upvotes: 0

Serkan Arslan

Reputation: 13393

you can check null values with IS

WHERE ACCOUNTNO IS NOT NULL

Upvotes: 4

Cannot filter out NULL contains column

Answers (2)

Related Questions