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



El Fri, 06 Jan 2017 11:35:32 -0800
"James H. H. Lampert" <jamesl-6/ELSmrcqeUu8xhjR5IN5AC/G2K4zDHf@xxxxxxxxxxxxxxxx> escribió:
Suppose I have one PF that defines codes as follows:

CODE DEF
40 'FOO'
45 'BAR'
50 'BAZ'
. . .

and I have another PF with a field that contains zero or more of the
codes, space-delimited, e.g.

CODES
'40 45 '
'40 '
'45 50 '
'40 45 50 '
. . .


Is there a pure-SQL way to produce (in a result set, and ultimately in a
view), for each record in the second file, field that's a concatenation
of the DEFs for the record's codes, e.g.
CODES DEFS
'40 45 ' 'FOO BAR'
'40 ' 'FOO'
'45 50 ' 'FOO BAZ'
'40 45 50 ' 'FOO BAR BAZ'
. . .

???

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


Result:
....+....1....+....2....+....3....
DEF2 NEWDEF
40 FOO
40 45 FOO BAR
40 45 50 FOO BAR BAZ
45 50 BAR BAZ
******** End of data ********


Here are the tables:



> create table UNO (
CODE numeric(2,0),
DEF char(3)
)

....+....
CODE DEF
40 FOO
45 BAR
50 BAZ
******** End of data ********


> create table DOS (
DEF char(16)
)

....+....1....+.
DEF
40 45
40
40 45 50
45 50
******** End of data ********



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.