Using IS NOT NULL for multiple columns
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)
As per OP comment, Updating answer
Inserting Rows by 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 query
I am able to reduce 50 chars approx
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
)
Checking whether any two of ten fields are different (not NULL or equal)
UNPIVOT
the columns to rows and then GROUP BY
your primary key and COUNT
the DISTINCT
values in the unpivoted columns to see if there is more than one unique value:
Oracle 11 Setup:
CREATE TABLE table_name ( id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 ) AS
SELECT 1, 'A', 'A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
SELECT 2, 'A', NULL, 'B', NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL UNION ALL
SELECT 3, 'A', NULL, 'A', 'A', NULL, 'A', 'A', 'A', 'A', 'A' FROM DUAL UNION ALL
SELECT 4, 'A', NULL, 'A', 'A', 'B', NULL, NULL, NULL, NULL, NULL FROM DUAL;
Query:
SELECT id
FROM table_name
UNPIVOT ( value FOR name IN ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 ) )
GROUP BY id
HAVING COUNT( DISTINCT value ) > 1
Output:
| ID |
| -: |
| 2 |
| 4 |
db<>fiddle here
Related Topics
Creating Trigger That Runs on Two Tables
MySQL Procedure to Update Numeric Reference in Previous Rows When One Is Updated
How to Relate 3 Tables Depending on Event
Is Too Many Left Joins a Code Smell
Sql Server: Do I Need to Use Go Statements Between Batches
Why Can't I Use Select ... for Update with Aggregate Functions
Sql String Manipulation [Get All Text Left of '(']
How to Retrieve The Date Part Out of a Datetime Result Column in Sqlite
Why Does This Oracle Drop Column Alter The Default Value of Another Column
How to Insert into a Table with Just One Identity Column (Sql Express)
Select All Projects That Have Matching Tags
Oracle Unique Constraint Based on Column Value
How to Search for Names with Apostrophe in SQL Server
Execute Procedure in a Trigger
Sqlite3 Database Is Locked in Azure
How to Copy Schema and Some Data from SQL Server to Another Instance