Re: SQL Mental Block -- RPG400-L

Craig,

Just add another layer of CTE...

insert into mytable
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')

, myJSON as (
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 ) ) ) as myData
from t0
group by t0.warehouse, t0.fromWarehouse, t0.intkRef
)
select mydata, current_timestamp
from myJSON;

Charles

On Wed, Mar 14, 2018 at 4:30 PM, Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

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

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD