× 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.



Hmm to clarify the last bit which I explained badly and also typed INSERT
INTO when I meant SELECT INTO...

At the end I meant to say, I know I could run that JSON sql into a host
variable - say WkJSON

Then to insert into a file which had a timestamp and a CLOB, I could run a
second SQL something like:

Insert into table values( current_timestamp, :WkJSON );

But I want to avoid the first step and put the JSON-building SQL where the
:WkJSON host variable is...

Where's Birgitta when you need her, that's what I say!

I'll sleep on it and try again in the morning :-)
best regards,
Craig

On 14 March 2018 at 19:46, Craig Richards <craig@xxxxxxxxxxxxxxxx> wrote:

I'm sure this is possible but for some reason I'm struggling with the
syntax...

I have some SQL code that builds a JSON object:

with t0 as (
select t1.istows as warehouse, t1.isfacy as fromWarehouse, t1.isshpr as
intkRef, t2.sdid# as itemRef,
char( date( timestamp_format( char( t3.imfind ), 'YYYYMMDD') ), ISO ) as
manuDate,
strip( t4.pdname, T ) as itemText,
case when ( t3.imtype = '8' or t3.imtype = '9' ) then 'EA'
else 'SM'
end as UOM,
t4.pdfnwt as weight,
case when ( t3.imtype = '8' or t3.imtype = '9' ) then 0
else Dec( ( t2.sdwide / 100 ), 5, 2 )
end as width,
case when ( t3.imtype = '8' or t3.imtype = '9' ) then 0
else Dec( ( t2.sdsize / 100 ), 5, 2 )
end as length,
case when ( t3.imtype = '8' or t3.imtype = '9' ) then t2.sdwide
else 0
end as quantity,
t3.improd as prodCode, t3.imcolr as colourcode,
strip( t5.coname, T ) as colourText,
t3.imdylt as dyelot, t3.imhold as QCHold, t3.iminsp as faultCode,
t3.imstat as Grade, t3.imType as stockType
from invshdr t1 join
invsdtl t2 on
t1.isshpr = t2.sdshpr
join
invmast t3 on
t2.sdid# = t3.imid#
join
psprod00 t4 on
t3.improd = t4.pdprod
join
pscolr00 t5 on
t3.improd = t5.coprod and
t3.imcolr = t5.cocolr
where t1.isshpr = '010424')
select json_object(
'msgType' value 'stockASN',
'warehouse' value t0.warehouse,
'fromWarehouse' value t0.fromWarehouse,
'intkRef' value t0.intkRef,
'items' value
json_arrayagg(
json_object(
'itemRef' value t0.itemRef,
'manuDate' value t0.manuDate,
'itemText' value t0.itemText,
'UOM' value t0.UOM,
'weight' value t0.weight,
'width' value case when t0.UOM = 'SM' then t0.width else null
end,
'length' value case when t0.UOM = 'SM' then t0.length else
null end,
'quantity' value case when t0.UOM = 'EA' then t0.quantity else
null end,
'prodCode' value t0.prodCode,
'colourCode' value t0.colourCode,
'colourText' value t0.colourText,
'dyeLot' value t0.dyeLot,
'QCHold' value t0.QCHold,
'faultCode' value t0.faultCode,
'grade' value t0.grade,
'stockType' value t0.stockType ) ) )
from t0
group by t0.warehouse, t0.fromWarehouse, t0.intkRef;

That produces a one column one row result set which contains a JSON object
which contains an array of JSON objects.

I know I could have done it without a common table ( t0 ) but I thought
that might help for clarity and debugging.

I'd like to now do an single row insert into a table where the above SQL
is one of the columns on the table

Supposing I had a table with a timestamp and a CLOB and I'd like to insert
the current timestamp and use the above SQL to generate the JSON object to
go into the CLOB.

Obviously I could run that SQL with an INSERT INTO host variable or using
SET, but I assume I ought to be able to do it directly on the insert?

Anyone care to enlighten and old man?

thanks,
Craig


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.