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


  • Subject: RE: Converting QM Query to rpg embedded SQL
  • From: "Danny Rodius" <danny.rodius@xxxxxxxxx>
  • Date: Tue, 20 Feb 2018 16:39:23 +0100
  • Ironport-phdr: 9a23:ENVHchYt39RZlB8ou+wkcmn/LSx+4OfEezUN459isYplN5qZrsu5bnLW6fgltlLVR4KTs6sC17KN9fi4EUU7or+5+EgYd5JNUxJXwe43pCcHRPC/NEvgMfTxZDY7FskRHHVs/nW8LFQHUJ2mPw6arXK99yMdFQviPgRpOOv1BpTSj8Oq3Oyu5pHfeQpFiCazbL9oMBm6sRjau9ULj4dlNqs/0AbCrGFSe+RRy2NoJFaTkAj568yt4pNt8Dletuw4+cJYXqr0Y6o3TbpDDDQ7KG81/9HktQPCTQSU+HQRVHgdnwdSDAjE6BH6WYrxsjf/u+Fg1iSWIdH6QLYpUjul8qlrVQToiD8ZODEl7GHZhMtwjKdBrxKgoRx03orYbY6ROfZ7eK7WYNEUSndbXstJVyJPHI28YIkSAeQPPuhXoJXyqVQJoxuiHgSsGOHiyjBUinPowaE2zeIsGhzG0gw6GNIOtWzZoNv7OqgIV+C1yqnJxijfYfNRxDj96ZXIcgsvrfqRWr9/bNDeyU8pFwLFlFmQs4vlMC6U1uQQqGiW9PFgVeGvim4htQ5xviKjydwyhYTQgI8e117K9SJ8wIkvJN24TlZ2YdulEJRNrSGVLY12Qsw8TG1ypCk6zbgGtYa0fSgPzJQn3BnfZOafc4SS5RLjTumRLS9ii3JgYr2+hhGy8U66xuLgUcm01U5GoTBAktjNsHABzQDT6s+BR/Bg/UmhwS6C2gPO5u1eP0w5mqXWJ4Q/zrM/lpcfq1nPEy32lUjwkaSYbF8r+vKy5OTierjmo5icOJJqhQzmKaQun9C/Afw/MggTQ2iX4eS826Pn/U3+WLhEgOc5kq/fsJHaP8oWuLa1Aw5T0ok99xayFzSr3MkCkXUZI19IdwiLg5bmNlzOOvz0EPWyjlS0nDdu3f/GP7nhApvXLnjElbfsZaty5FRayAo1zdFT/YhUCrYcL/LyR0/xr8fVDgQ6Mw20xeboFsl92pkFVm2RGqOZKr/dsUeU5uIzJOmBfJUVuC7yK/Uq+/LikWY1mVsGcKms3JsYdWq4Ee5gI0WWf3XgmMsOEWAPvll2cerxlVfXUSJPf23gGOU45yonE8SjDIvZT4aijfqK2yL8G5RXYmVPDBeIEGz0doOKXLAWcC3Oepcklm84Uu3rBLUZ+lm2rgj10bNiK+vS9WUfs9r/1NU9xsf23RB6wXxdNIPBiznFRmd41mIzSiU30btL4AM3ggOZyack26QdEfRW5v9TWwE1ONjXyOksWP7oXQeUNPuATlC3Cu+hDTAsVZh5l9MHZUBsXc+lghHexAKxAK4TmqDNDpFiofGU5GT4O8sokyWO76ImlVRzHppC
  • List-archive: <https://archive.midrange.com/rpg400-l/>
  • List-help: <mailto:rpg400-l-request@midrange.com?subject=help>
  • List-id: "RPG programming on the IBM i \(AS/400 and iSeries\)" <rpg400-l.midrange.com>
  • List-post: <mailto:rpg400-l@midrange.com>
  • List-subscribe: <https://lists.midrange.com/mailman/listinfo/rpg400-l>, <mailto:rpg400-l-request@midrange.com?subject=subscribe>
  • List-unsubscribe: <https://lists.midrange.com/mailman/options/rpg400-l>, <mailto:rpg400-l-request@midrange.com?subject=unsubscribe>

@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


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.