Sql – Oracle SQL Select where clob field is not empty/null

cloboracle10gsql

I am trying to get rows from an archive database for populating a test environment DB. I need rows where a specific field called "AUDIT_LOG" which is CLOB cannot be empty null.

I have tried the following simple select statements but I get

ORA-00932: inconsistent datatypes: expected – got CLOB

The statements I have tried:

SELECT * FROM SIEBEL.S_AUDIT_ITEM WHERE AUDIT_LOG = ''

SELECT * FROM SIEBEL.S_AUDIT_ITEM WHERE AUDIT_LOG != NULL

SELECT * FROM SIEBEL.S_AUDIT_ITEM WHERE AUDIT_LOG = 0

Does a NULL in CLOB equate to an empty character string. I have a feeling maybe length can be used for the column.

Thanks

Best Answer

to check NULL, regardless of datatype, you use IS NOT NULL or IS NULL

WHERE AUDIT_LOG IS NOT NULL

But keep in mind that for CLOBs, an EMPTY_CLOB() has no characters but is not the same as NULL. If you want to exclude EMPTY_CLOB(), use @user3837669's answer that uses a LENGTH comparison.

Related Solutions

Sql – How to perform an IF…THEN in an SQL SELECT

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

Sql – How to UPDATE from a SELECT in SQL Server

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

Best Answer

Related Solutions

Sql – How to perform an IF…THEN in an SQL SELECT

Sql – How to UPDATE from a SELECT in SQL Server

Related Topic