Re: Converting QM Query to rpg embedded SQL

Brian P is right, you cannot use host variable with CREATE TABLE, for you
to do this you should first create the table with the needed field, it
could be a select with not data and then do an insert with your CTE.


On Tue, Feb 20, 2018 at 4:07 PM, Brian Parkins <goodprophet.bp@xxxxxxxxx>
wrote:

<phew!>

To my untrained eye, I don't believe you can use Host Variables on a
CREATE TABLE statement. (There doesn't appear to be a definitive reference
of where you CAN use HV's. The manual simply states they are "commonly
used" on WHERE, INTO, SELECT, SET, VALUES and CALL.)

Perhaps one of resident experts could add some insight?

If this is a showstopper, then you'll need to consider using Dynamic SQL
embedded in your RPG IV code.

Brian.


On 20/02/2018 15:39, Danny Rodius wrote:

@Paul & Brian

Please find the sql hereafter:

CREATE TABLE QTEMP/DELIVER AS (

with cte_hdr as (
SELECT * FROM anaqrep JOIN anarrep ON aqvvne = arvvne WHERE aqvvne =
:PACK),

cte_hdr_all as (
SELECT aqsjce, a9sqce, a9fhts, xmlelement(Name "Lines",
xmlagg(xmlelement(Name "DeliveryGoodsLines",
xmlforest(trim(char(ARBFPR, '.')) as "CostPrice",
trim(artnce) as "ItemID", trim(artoce) as "ConfigID",
trim(arzsce) as "InventTransID", trim(char(ardoqt, '.')) as "Qty",
trim(aqzrce) as "SalesID")))) as xml_scalar
FROM cte_hdr JOIN ama9rep ON aqvvne = a9vvne
WHERE a9elsv < 90
GROUP BY aqsjce, a9sqce, a9fhts)

SELECT xmlserialize(xmlelement (Name "SOAP:Envelope",
xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP"),
xmlelement (Name "SOAP:Body", xmlelement(Name "ns0:DeliveredGoodsRequest",
xmlnamespaces('http://VDW.AS400.Schemas.DeliveredGoodsRequest' AS "ns0"),
xmlelement(Name "Company", trim(aqsjce)),xmlelement(Name "UserID",
trim(a9sqce)), xmlelement(Name "Username", trim(a9fhts)),
xmlagg(xml_scalar))
)) as clob(1m) including xmldeclaration ) as xml_doc
FROM cte_hdr_all GROUP BY aqsjce, a9sqce, a9fhts

) WITH DATA

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Brian
Parkins
Sent: dinsdag 20 februari 2018 15:27
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxx>
Subject: Re: Converting QM Query to rpg embedded SQL

Danny, the &VAR is for runtime substitution in the CL command, STRQMQRY.
Depending on where it is used in the SQL statement, it may not be valid
as a
host variable when embedded in an RPG IV program.

For example, a host variable cannot be used on the ORDER BY clause.

We need to see the SQL statement to be of more help.

Brian.

On 20/02/2018 12:45, Danny Rodius wrote:

Hello,

Have a QM query and want to convert it to embedded SQL.

However the SQL has a variable in it defined as &var.


When I change this to :var it gives me the error "host variable not
permitted here".

Any idea from where this comes? Why in the QM query its accepts the
variable but no in embedded SQL.


Any ideas?


Best regards

Danny

--

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