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
)
Select rows where a column is not null if multiple values or null if 1 value
You can use a NOT EXISTS:
SELECT DISTINCT T.Title
, T.Value
FROM mytable T
WHERE T.Value IS NOT NULL
OR NOT EXISTS (
SELECT NULL
FROM mytable T2
WHERE T2.Value IS NOT NULL
AND T2.Title = T1.Title
)
Optimize way of Null checking for multiple columns
You can check the row for not NULL values in three ways:
COALESCE(col1, col2, col3) IS NOT NULL
col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL
ISNULL(col1, ISNULL(col2, ISNULL(col3, NULL))) IS NOT NULL
You can use the Microsoft SQL Server Management Studio to compare multiple querys.
result of comparison:
COALESCE
vs.IS NOT NULL
: 57% to 43%COALESCE
vs.ISNULL
: 56% to 44%IS NOT NULL
vs.ISNULL
: 49% to 51%
So using IS NOT NULL
is the fastest way to check if a row has a column without a NULL value. In case of readability the COALESCE
can be much shorter than a IS NOT NULL
or ISNULL
comparison. You can decide between readability and speed.
check if all rows have column not null
Applying Tim's logic for all rows with the same zamodb_id
using Windowed Aggregates:
with cte as
( select z.zamodb_id,zp.zamodbpoz_id,zp.data_planowana
,count(*) over (partition by z.zamodb_id) as cntStar
,count(zp.data_planowana) over (partition by z.zamodb_id) as cntCol
from zamodbpoz zp
....
)
select *
from cte
where cntStar = cntCol
If a column is not null then convert into a row for multiple columns
CROSS APPLY
combined with UNION ALL
is very useful here:
SELECT
t.col1, t.col2, t.col3,
v.*
FROM table t
CROSS APPLY (
SELECT col4, NULL, NULL
WHERE col4 IS NOT NULL
UNION ALL
SELECT NULL, col5, NULL
WHERE col5 IS NOT NULL
UNION ALL
SELECT NULL, NULL, col6
WHERE col6 IS NOT NULL
) v
If you have many columns this gets tedious. Futhermore, this type of table design is generally incorrect. What you need is a straightforward UNPIVOT
:
SELECT
upvt.col1,
upvt.col2,
upvt.col3,
upvt.ColName,
upvt.Value
FROM table t
UNPIVOT ( Value FOR ColName IN
(Col4, Col5, Col6, Col7, Col8, Col9)
) upvt
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
Sql: Update Column With Increment Numbers Based on 2 Columns
How to Delete a MySQL Record After a Certain Time
How to Check If Value Is Inserted Successfully or Not
Mysql - Get All Records That Have More Than 1 Record for the Same Id
Truncate Seconds and Milliseconds in SQL
Duplicate Rows When Joining Tables
Max and Min Sal With Employee Name in One Query
Postgresql Error: Syntax Error At or Near "Varchar"
Sql Count All Word Occurrences from a Table
Sql - How to Sum/Aggregate Certain Rows in a Table
How to Use SQL Like Condition With Multiple Values in Postgresql
Best Way to Iterate Through Columns in a SQL Table
Database Corruption With Mariadb:Table Doesn't Exist in Engine
How to Combine First Name, Middle Name and Last Name in SQL Server
How to Directly View Blobs in MySQL Workbench
Show Zero If There Is No Record Count - Oracle SQL Query