Re: SQL question

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 ********