Re: SQL Mental Block

Why an additional layer?
Why not simply adding, the current timestamp to the final SELECT?

Something like this:

Insert into YourTable (JSONCLOB, YourTimestamp)
With ....
Select JSON_Object(....), Current_Timestamp
From ....

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Mittwoch, 14. März 2018 23:50
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxx>
Subject: Re: SQL Mental Block

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

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

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