Re: SQL Mental Block

[Craig Richards <craig@xxxxxxxxxxxxxxxx>]

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