Convert Comma separatedNumber to Name in PL SQL 1

[macsql1]

Hello- I have two tables. How I can cast BookCollection_ID number to Book_Name? Please help

Select A.BookCollection_ID from Bookpart A, BookName B where A.BookPart_ID = B.BookPart_ID

OutPut
1,2

Expected OutPut
ToyBook,FunBook

We need to separate 1,2 and extract Book_NAME from BookPart table.
i.e 1 as ToyBook and 2 as FunBook ?


1.CREATE TABLE BookPart (
BookPart_ID INTEGER NOT NULL,
LIMIT_MAX VARCHAR2 (255),
BookCollection_ID INTEGER,
PRIMARY KEY (BookPart_ID )

2.CREATE TABLE BookName (
BookName_ID INTEGER NOT NULL,
BookPart_ID INTEGER,
Business_ID INTEGER,
Book_NAME VARCHAR2 (255),
PRIMARY KEY (BookName_ID )

-mac

 

[Dagon]

So are you saying that there is a single row in BookPart which contains literally '1,2' as the BookCollection_Id? Are are there two rows with Ids 1 and 2?

 

[macsql1]

Yeah. I want to render BookCollection_ID (1,2) value as (ToyBook,FunBook). BookCollection_ID is manually enter by user like 1,2 or 2 or 1,2,3

BookPart
BookName_ID----Book_NAME-------BookPart_ID
1---------------ToyBook---------88
2---------------FunBook---------99
3---------------SchoolBook------77



BookName
BookPart_ID----BookCollection_ID----BookCollection_IDName(EXPECTED OUTPUT)
88-------------1,2------------------ToyBook,FunBook
99-------------1,2,3----------------ToyBook,FunBook,SchoolBook
77-------------2,3------------------FunBook,SchoolBook

 

[macsql1]

one more update

I have replaced ` to ,(comma) then try to render "1,2" as "ToyBook,FunBook" in result

replace('BOOKCOLLECTION_ID', '`', ',')

 

[macsql1]

One more update

BookCollection_ID is VARCHAR2 NOT integer.

CREATE TABLE BookPart (
BookPart_ID INTEGER NOT NULL,
LIMIT_MAX VARCHAR2 (255),
BookCollection_ID VARCHAR2 (255),
PRIMARY KEY (BookPart_ID )

 

[Dagon]

I'm a bit confused on your data since it seems to change every time you post something. However, one option would be to write a small PL/SQL function which takes the list of IDs and translates it to a list of book names. Something like this:

CREATE TABLE  BookPart (
  BookPart_ID          INTEGER       NOT NULL,
  LIMIT_MAX               VARCHAR2 (255),
  BookCollection_ID      VARCHAR2(255),
  PRIMARY KEY (BookPart_ID    ));
  
CREATE TABLE BookName (
  BookName_ID       INTEGER       NOT NULL,
  BookPart_ID          INTEGER,
  Business_ID     INTEGER,
  Book_NAME     VARCHAR2 (255),
  PRIMARY KEY (BookName_ID      ));
  
  insert into bookpart values (1, 2,  '1,2');
  insert into bookpart values (2, 3,  '1,2,3');
   insert into bookpart values (3, 2,  '2,3');
   
   insert into bookName  values (1, 1, 1, 'ToyBook');
   insert into bookName  values (2, 1, 1, 'FunBook');
 insert into bookName  values (3, 1, 1, 'SchoolBook');  
   
  CREATE or replace function getbookList (p_list in varchar2) return varchar2 is
     listElement varchar2(255);
     tmpBookName varchar2(255);
     outString varchar2(2000);
     strPos integer := 1;
     endPos integer;
     list varchar2(2000);
  begin
    list := p_list || ',';
    strPos := 1;
    while strPos < length(list) loop
      endPos := instr(list, ',', strPos);
      listElement := substr(list,strPos, endPos-strPos);
      select book_name
        into tmpBookName
        from bookName
        where bookName_id = listElement;
      outString := outString||tmpBookName||',';
      strPos := endPos+1;
    end loop;
    return regexp_replace(outString, ',$');
 end;
 /

select getbooklist(bookcollection_id) from bookpart;

GETBOOKLIST(BOOKCOLLECTION_ID)
------------------------------
ToyBook,FunBook
ToyBook,FunBook,SchoolBook
FunBook,SchoolBook

 

[macsql1]

Thanks A lot.