Geek Answers Handbook

Using IS NOT NULL for multiple columns

I want to check if the null constraint is for multiple columns in one SQL statement in the WHERE , is there a way to do this? Also, I do not want the forced constraint of type NOT NULL be defined in the column definition.

 SELECT * FROM AB_DS_TRANSACTIONS WHERE FK_VIOLATION IS NULL AND TRANSACTION_ID NOT IN( SELECT distinct TRANSACTION_ID FROM AB_TRANSACTIONS) AND COUNTRY_ID IS NOT NULL AND GEO_CUST_COUNTRY_ID IS NOT NULL AND INVOICE_DATE IS NOT NULL AND ABB_GLOBALID IS NOT NULL AND SALES_ORG_ID IS NOT NULL AND DIST_ID IS NOT NULL AND CUSTOMER_ID IS NOT NULL AND REPORT_UNIT_ID IS NOT NULL AND CURR_INVOICE IS NOT NULL AND DIVISION_CODE IS NOT NULL

So instead of using IS NOT NULL over and over again, I want to simplify things

+6
source share
2 answers

you can use

 SELECT * FROM table1 WHERE NOT (Column1 IS NULL OR Column2 IS NULL OR Column3 IS NULL OR Column4 IS NULL IS NOT NULL)

According to OP comment, Update reply

Insert rows using INSERT and SELECT subqueries

 INSERT INTO Table_A SELECT column1, column2, column3,column4 FROM Table_B WHERE NOT (Column1 IS NULL OR Column2 IS NULL OR Column3 IS NULL OR Column4 IS NULL IS NOT NULL);

Your request

I can reduce 50 characters

 SELECT * FROM AB_DS_TRANSACTIONS WHERE FK_VIOLATION IS NULL AND TRANSACTION_ID NOT IN(SELECT distinct TRANSACTION_ID FROM AB_TRANSACTIONS) AND NOT ( COUNTRY_ID IS NULL OR GEO_CUST_COUNTRY_ID IS NULL OR INVOICE_DATE IS NULL OR ABB_GLOBALID IS NULL OR SALES_ORG_ID IS NULL OR DIST_ID IS NULL OR CUSTOMER_ID IS NULL OR REPORT_UNIT_ID IS NULL OR CURR_INVOICE IS NULL OR DIVISION_CODE IS NULL )
+7
source

I think the syntax you are looking to show only those lines where all are not null is

 SELECT * from table_B where COLUMN1 is not null and COLUMN2 is not null and COLUMN3 is not null
+1
source

More articles:


All Articles