Re: SQL question

El Fri, 06 Jan 2017 15:28:19 -0800
"James H. H. Lampert" <jamesl-6/ELSmrcqeUu8xhjR5IN5AC/G2K4zDHf@xxxxxxxxxxxxxxxx> escribió:

On 1/6/17, 12:05 PM, Manuel Chaviano wrote:

===> with A as (
select UNO.CODE,UNO.DEF DEF1,DOS.DEF DEF2
from UNO,DOS
where regexp_like(DOS.DEF,digits(UNO.CODE))
)
select DEF2,
xmlserialize(
xmlagg(
xmltext(trim(DEF1)||' '
)
order by CODE
) as char(16)
) as NEWDEF
from A
group by DEF2

. . .

For whatever reason, I couldn't get the
"xmlserialize(xmlagg(xmltext()))" part to work:
> "xmltext(trim def1)||' ')"
wouldn't give me anything but error messages, and if I took out the
"||' '", it gave me garbage.

I ended up implementing what I needed as an external UDF, in ILE RPG.
Took me longer to remember how to implement UDFs in RPG (answer: look up
Scott Klement's instructions,
<https://www.scottklement.com/presentations/#RPGUDF>
than it took to actually do it.

Incidentally, for quite a few minutes, I was tearing my hair out trying
to figure out why slide 22 (bottom half of page 11) of the PDF wouldn't
also work in a STRSQL session, (it kept giving messages in the vein of
"FOO in *LIBL type *N not found"), before I shrugged, and tried testing
it with the actual field it was designed to be applied to, in a real
query of the real file, and found that it worked perfectly.

--
JHHL

Sorry to hear it did not work on your system.

I am at V7.1 with all TR's and I used STRSQL (interactive SQL).

I created the tables with "create table" and added the records from your
example using "insert into <table> values(...)".

I copied the SQL from this reply, just to make sure there are no typos and it still works.




In the Redbook "IBM i 7.1 Technical Overview with Technology Refresh December 2014-"
in the "Summary of Changes, December 2014, Fourth Edition (page xxix)" is the following:

"This fourth edition of the book includes all the enhancements that are available in the next two
Technology Refreshes (TR6 and TR7) that were announced in 2013. There are
enhancements basically in every topic that is related to IBM i. For this reason, every chapter
was modified to include these enhancements."

In "Section 5.2.1 - XML Support" describes the XML capabilities on DB2.

I remember writing some UDF's in RPG myself.
I used to copy the source from one to the other so I did not have to re-type the DS and PARMS.

I was able to write a UDFT (returning a table) that was difficult.
I remember using in the RPGSQL program the so called "call type" to find out whether it was the start, the middle or the end; and using the "Scratchpad" feature to "remember" values between the calls.



The silde in page 22:

The message that "the function xyz was not found" could mean a mismatch in the parms data type.

I tried this with my own:


===> select * from table(FNDSTS('nn')) as X
FNDSTS in *LIBL type *N not found.

I made the parm character on purpose, I know it should be numeric.

Passing a number made it "find the function"!

Good luck!