×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
I'm able to select non-nested fields and populate a database file using Json_Table. I'm not able to correctly Select nested fields. I keep on getting SQL0206 errors.
This works perfectly: exec sql insert into cheetpf (arrivtime, arrivstat, carrier, city, codamt) select arrivalTime, arriveDepartStatus, carrier, city, codAmount from jsonin4 a, json_table (a.JDATA, '$.orders[*] ' columns ( arrivalTime varchar(100) path '$.arrivalTime' , arriveDepartStatus varchar(100) path '$.arriveDepartStatus' , carrier varchar(100) path '$.carrier' , city varchar(100) path '$.city' , codAmount varchar(100) path '$.codAmount' ) ) ;
This doesn't work and every field after codAmount gives a SQL0206 error: exec sql insert into cheetpf (arrivtime, arrivstat, carrier, city, codamt, itmcube, itmdesc, itmno, itmstat, itmmodel, itmpcs, itmqry, itmunits, itmwght) select arrivalTime, arriveDepartStatus, carrier, city, codAmount, cube , description, itemNumber, itemStatus, model, pieces, quantity, units , weight from jsonin4 a, json_table (a.JDATA, '$.orders[*] ' columns ( arrivalTime varchar(100) path '$.arrivalTime' , arriveDepartStatus varchar(100) path '$.arriveDepartStatus' , carrier varchar(100) path '$.carrier' , city varchar(100) path '$.city' , codAmount varchar(100) path '$.codAmount', nested path '$.items[*]' Columns ( "cube" varchar(50), "description" varchar(100), "itemNumber" varchar(20), "itemStatus" varchar(20), "model" varchar(50), "pieces" varchar(20), "quantity" varchar(20), "units" varchar(20), "weight" varchar(20) ) ) ) ;
I suppose I could just use Select *, but I'd like to know how to specify only the fields I need.
Thanks in advance.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.