How to Query Longchar

Hello Guys, hope you all are doing well.

I have a Longchar variable that contains JSON data of 4 fields. I want to write a query and compare these longchar variable values with DB table/fields.

For Example: My Longchar variable contains below json data, I want to compare these JSON key : value pairs with customer table fields/values.

Code:
{"Customer": [
  {
    "cust-num": 121,
    "name": "Jack",
    "state": "Texas",
    "city": "Houston"
  }
]}

I am not getting how to query a Longchar variable for this purpose, please share your inputs on this.

Regards,
 

Stefan

Well-Known Member
Deserialize your json to a temp-table and use that, this should get you started:

Code:
def var lcjson as longchar no-undo.

lcjson = '~{"Customer": [
  ~{
    "cust-num": 121,
    "name": "Jack",
    "state": "Texas",
    "city": "Houston"
  }
]}'.

def var ht as handle no-undo.
def var hb as handle no-undo.

create temp-table ht.

ht:read-json( 'longchar', lcjson ).

hb = ht:default-buffer-handle.
hb:find-unique().

message hb::cust-num hb::name hb::state hb::city. // you now have the values which you can use in a query
 
Thanks for your reply @Stefan , Actually I don't have static key/field information in JSON file. It can have multiple fields : values pairs of different tables.

Requirement is to compare database CLOB field with longchar variable. Need to achieve something like below but it's not working:

Code:
if not can-find (first table-name
  WHERE table-name.json-field = lcjson-variable THEN
do:
end.

or buffer-compare

Regards.
 
Sorry, because initially I tried to do field to field comparison and then realize that field information in json is not static.

Yes, getting this error message (even after typecasting CLOB to string()) and help is also not available for error 11420.

1654068459780.png

Regards.
 
Last edited:

Stefan

Well-Known Member
You can compare them, you just cannot use them in a query. So I think you are going to need to rethink what you are trying to achieve.
 
Yes @Stefan , I want to compare two large objects, one is clob field of database table and second is a longchar variable. I tried using compare function also but that is also not working.

Regards
 

TomBascom

Curmudgeon
As Stefan says, you cannot do the comparison in a WHERE clause. So you will need to do it like this:
Code:
for each tableWithACLOBField:

  if tableWithACLOBField.clobField = myLONGChar then
    do:
      // do something
    end.

end.

Performance will depend greatly on the amount of data involved.
 
Top