How to do split and left join in oracle

问题:

I work with toad for oracle and I have a database with multiple associations(1-n). I want to associate the varchar column (example :1,2,3) in table with the int (id)(example : 1) column of the other table . I am creating a view for this operation in database, but the outer apply and cross apply do not work.

Also did not work in the dbo.split command found in sql.

How can do it?

SELECT
      a.ID AS ID,
      a.ADI_NUMARASI AS aNAME,
      c.ID AS CODEID,   
      c.VALUE AS cVALUE   
 FROM  CUSTOMER a OUTER APPLY [dbo].[Split](a.TypeIDs, ',') Types 
LEFT JOIN CODE c ON c.ID= Types.Item  AND c.DOMAINID = a.DOMAINID

Example tables

table CUSTOMER                                     table CODE
 ID | ADI_NUMARASI | TYPEIDS |DOMAINID         ID | DOMAINID | VALUE
 1      TEST1        1,2,3     1                1      1        XXX
                                                2      1        YYY
                                                3      2        ZZZ
                                                3      1        KKK

expected result

 ID | aNAME | CODEID | cVALUE
 1    TEST1      1       XXX
 1    TEST1      2       YYY
 1    TEST1      3       KKK

回答1:

Try:

SELECT cu.ID , cu.ADI_NUMARASI , co.ID , co.VALUE
FROM 
(
  SELECT id,ADI_NUMARASI,TYPEIDS,DOMAINID,
  regexp_substr(TYPEIDS,'[^,]+', 1, level) typeidsnew 
  FROM Customer 
  GROUP BY id,ADI_NUMARASI,TYPEIDS,DOMAINID,
  regexp_substr(TYPEIDS, '[^,]+', 1, level)
  connect by regexp_substr(TYPEIDS, '[^,]+', 1, level) is not null
) cu 
LEFT JOIN Code co ON cu.typeidsnew = co.ID
AND cu.DomainId = co.domainId 
ORDER BY cu.ID,co.ID

Output: http://sqlfiddle.com/#!4/177e8/1

回答2:

you can try this one

WITH splitedValues as (SELECT TRIM(REGEXP_SUBSTR(TYPEIDS, '[^,]+', 1, level)) as TYPEID 
                             , ADI_NUMARASI                                 
                             , DOMAINID
                        FROM CUSTOMER
                      CONNECT BY level <= REGEXP_COUNT(TYPEIDS, '[^,]+'))
select * 
  from splitedValues s 
  join code c on s.TYPEID = c.id and s.domainid =  c.domainid