Re: SQL question

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