Does Servicenow support Schema on read feature

[UpmaR]

Does ServiceNow support Schema on read with SQL like 

SELECT CAST(order_amount AS INT)
FROM Order_table

We need to do type casting in memory where Order amount is stored as String in the database. Our goal is something like this which identifies the field type in memory

gr.addQuery('CAST(u_order_amount AS INT)', '>', 1000); //not exactly this syntax, this is just an example

Client wants to implement a Datalake table in ServiceNow with all the fields as string type or data is stored in a JSON field and while accessing for query and filerting, they wants to do casting for field type. Mapping between logical name of the field, backend name of the field and field type will be stored in the mapping table. Please let me know if field casting is passible in Servicenow in semantic layer without defining proper field types in the table?

#CMDB # Integration # Data Lake #ITSM

[Aditya_hublikar]

Hello @UpmaR ,

As per my understanding i think its not possible , but why you dont parse it while querying like 

var gr = new GlideRecord('u_order_table');
gr.query();

while (gr.next()) {
var amount = parseInt(gr.u_order_amount);
if (amount > 1000) {
gs.info(gr.number);// perform your operation}
}

If this helps you then mark it as helpful and accept as solution.

Regards,

Aditya

[UpmaR]

Hi @Aditya_hublikar  Thanks for your response. I want to use it as addQuery(). If I do parsing within while loop then it will first do full scan of the table and then one by one value will parsed and compared. I want to to cast the field as integer while querying itself as a where clause.

[Aditya_hublikar · Accepted solution]

Hello @UpmaR ,

Then you need to glide sys_dictionary table filtered out your field then  update its type with integer then perform your addQuery() then  after your operation  once done , you can again change its datatype back by gliding sys_dictionary table. But i think this is not  recommended approach.

In servicenow during querying you can't do type cast .

[UpmaR]

Thanks @Aditya_hublikar . I also feel it's limitation in ServiceNow, we can't do type casting while querying as it's possible in Sql. It's good to know the workaround