Group by with Clob in Select-Statement

How to use GROUP BY on a CLOB column with Oracle?

After some fixes it seems that the major issue was in the group by

YOu have to use the same tables in the SELECT and in the GROUP BY

I also take only a substring of the CLOB to get it works. THe working request is :

    SELECT TABLE_A.ID,
       TABLE_A.VA,
       B.TRACE
FROM
(SELECT A.T_ID ID,
          MAX(A.V) VA
   FROM BDD.LOG A
   GROUP BY A.T_ID HAVING MAX(A.V) <= '1.00') TABLE_A,
                                                                BDD.T B
WHERE TABLE_A.ID = B.T_id;

Group by with CLOB in select-statement

A CLOB value cannot be used for grouping or inside a distinct clause.

The only chance you have is to convert the CLOB to a varchar but that means you cannot compare the complete contents of the column (note: those are columns, not rows). If you are certain that all your CLOB values are smaller than 8000 bytes, you can use something like this:

select min(dbms_lob.substr(column1)), column2
from foo
group by column2;

Query an Oracle CLOB column based on an IN clause

Storing comma-separated values in a single column violates every rule of normalization. That's going to make your queries much harder to write and much slower to run. You really ought to have a 1-to-many child table that stores the roles. If you do that, your queries will be much more efficient.

You could do something like

select id
  from my_tab
 where ',' || access_lvl || ',' like '%,RoleName-B,%'
    or ',' || access_lvl || ',' like '%,RoleName-C,%'
    or ',' || access_lvl || ',' like '%,RoleName-D,%'

That is going to be terribly slow but it will work.

Oracle SQL : Select Query : search if clob Contains a string with pattern matching

Generic string functions for parsing JSON inputs are dangerous - you will get false positives, for example, when something that looks like a JSON object is in fact embedded in a string value. (Illustrated by ID = 101 in my example below.)

The ideal scenario is that you are using Oracle 19 or higher; in that case you can use a simple call to json_exists as illustrated below. In the sample table I create, the first JSON string does not contain a member named apple. In the second row, the string does contain a member apple but the value is null. The first query I show (looking for all JSON with an apple member) will include this row in the output. The last query is what you need: it adds a filter so that a JSON string must include at least one apple member with non-null value (regardless of whether it also includes other members named apple, possibly with null value).

create table sample_data
( id      number primary key
, colname clob   check (colname is json)
);

insert into sample_data
    values (101, '{name:"Chen", age:83, values:["{apple:6}", "street"]}');

insert into sample_data
    values (102, '{data: {fruits: [{orange:33}, {apple:null}, {plum:44}]}}');
    
insert into sample_data
    values (103, '[{po:3, "prods":[{"apple":4}, {"banana":null}]},
                   {po:4, "prods":null}]');

Note that I intentionally mixed together quoted and unquoted member names, to verify that the queries below work correctly in all cases. (Remember also that member names in JSON are case sensitive, even in Oracle!)

select id
from   sample_data
where  json_exists(colname, '$..apple')
;


 ID
---
102
103

This is the query you need. Notice the .. in the path (meaning - find an object member named apple anywhere in the JSON) and the filter at the end.

select id
from   sample_data
where  json_exists(colname, '$..apple?(@ != null)')
;

 ID
---
103

---

Related Topics