Need big time help with Cursor

[ekta22]

Hi All,

I am trying to use cursor to concatenate data from multiple fields into one. Let me try to explain a little more. I have two tables - Header table and Comments table. Header table ID can have multiple comments sequences which in turn can have multiple comments. Makes sense.. no. here is some data to make it a little more clear.
Header table stores the hdr_ID and the top sequence_number. While the comments table stores all the sequences and comments for each sequence.
Header Table
hdr_ID sequence_number
1----------2
2----------3

Comments table
hdr_ID sequence_number comments
1----------1------------comment1
1----------1------------comment2
1----------2------------comment1
1----------2------------comment2
1----------2------------comment3
2----------1------------aaa
2----------2------------aaa
2----------2------------bbb
2----------2------------ccc
2----------3------------aaa

RESULT
hdr_ID sequence_number comments
1----------1------------comment1 comment2
1----------2------------comment1 comment2 comment3
2----------1------------aaa
2----------2------------aaa bbb ccc
2----------3------------aaa

How can I do this using a cursor. It's driving me nuts?

Thanks!

 

[SantaMufasa]

Ekta,

Following is SQL code (that uses my user-defined function, "matrix"), which does what you want:

col comments format a30
select hdr_ID
      ,sequence_number
      ,matrix('select comments from comments '
            ||' where hdr_ID = '||hdr_id
            ||'   and sequence_number = '||sequence_number) comments
  from comments
 group by hdr_id,sequence_number;

HDR_ID SEQUENCE_NUMBER COMMENTS
------ --------------- --------------------------
     1               1 comment1 comment2
     1               2 comment1 comment2 comment3
     2               1 aaa
     2               2 aaa bbb ccc
     2               3 aaa

Here is the code for my "matrix" function:

CREATE OR REPLACE FUNCTION matrix (query_in in VARCHAR2) RETURN VARCHAR2 IS
    incoming    varchar2(4000);
    hold_result varchar2(4000);
    delimiter   varchar2(10) := ' '; -- place your delimiter of choice here
    c sys_refcursor;
Begin
    open c for query_in;
    loop
        fetch c into incoming;
        exit when c%notfound;
        hold_result := hold_result||delimiter||incoming;
    end loop;
    return ltrim(hold_result,delimiter);
END;
/

Function created.
***************************************************************************

If you wish to use a delimiter different from a blank space, then replace the initial value of "delimiter varchar2(10) := ' ';" with something besides a blank.

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

Thanks.. I will try it out. Just one thing.. In my case these comments can be as big as 80 lines so varchar2 won't be sufficient. My database field is CLOB. And I want to add a line feed character when I concatenate these comments.

 

[SantaMufasa]

No problem, Ekta...a minor modification makes "matrix" work for both CLOBs and for standard VARCHARs. A second modification (to "DELIMITER") uses an Oracle CHR(10), which is a <return>, to throw a new line between each comment. In fact, with "CHR(10)||CHR(10)", you can cause new comments to be double-spaced.

First, here is a DESCRIBE COMMENTS that shows the COMMENTS column as a CLOB:

Name                    Null?    Type
----------------------- -------- ------
HDR_ID                           NUMBER
SEQUENCE_NUMBER                  NUMBER
COMMENTS                         CLOB

select * from comments;

HDR_ID SEQUENCE_NUMBER COMMENTS
------ --------------- --------
     1               1 comment1
     1               1 comment2
     1               2 comment1
     1               2 comment2
     1               2 comment3
     2               1 aaa
     2               2 aaa
     2               2 bbb
     2               2 ccc
     2               3 aaa

Here is the modified "matrix" function:

CREATE OR REPLACE FUNCTION matrix (query_in in VARCHAR2) RETURN clob IS
    incoming    varchar2(4000);
    hold_result clob;
    delimiter   varchar2(10) := chr(10);
    -- place your delimiter of choice above; chr(10) = <return> chr.
    c sys_refcursor;
Begin
    open c for query_in;
    loop
        fetch c into incoming;
        exit when c%notfound;
        hold_result := hold_result||delimiter||incoming;
    end loop;
    return ltrim(hold_result,delimiter);
END;
/

Function created.

Here is an invocation of the same SQL statement as before, but now processing a CLOB column, separating comments with a <return>:

select hdr_ID
      ,sequence_number
      ,matrix('select comments from comments '
            ||' where hdr_ID = '||hdr_id
            ||'   and sequence_number = '||sequence_number) comments
  from comments
 group by hdr_id,sequence_number;

HDR_ID SEQUENCE_NUMBER COMMENTS
------ --------------- --------
     1               1 comment1
                       comment2
     1               2 comment1
                       comment2
                       comment3
     2               1 aaa
     2               2 aaa
                       bbb
                       ccc
     2               3 aaa

5 rows selected.

Notice "5 rows selected" despite there being 10 lines of output resulting from the <returns> between each comment.

Let us know your thoughts.




Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Turkbear]

Hi Dave,
Love the function, but when passing a value to a Varchar2 or Char field you will need to modify query to handle the quotes..
I used:

select empl_nbr,empl_nm,matrix('select empl_stat_desc from hr_cur_rptng'
||' where empl_nbr = '||chr(39)||empl_nbr||chr(39)) statuses
from hr_cur_rptng
where empl_stat_cd <> 'A' and empl_stat_cd <> 'R'
group by empl_nbr,empl_nm
/

The old standby Chr(39) handles placing the value in single-quotes





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Actually, Turk, since the variable values that we happened to be passing in the above scenario are numbers, we don't need surrounding quotes, especially if we are concatenating them to string expressions.

But you are correct...If you use my "Matrix" function and the WHERE-clause expressions involve literal string expressions, then you must account for embedding literal single quotes in the SELECT statement you are using as an argument to the "matrix" function.

One can achieve the embedding of the single quote in a variety of ways, including yours:

1) using "chr(39)" to represent a single quote
2) using two successive single quotes to represent one single quote within the literal string
3) using some other quote-like character such as a "backward quote" (e.g., "`", i.e., chr(96)) to visually represent a single quote, then use either the TRANSLATE or the REPLACE functions to transform the "`" to a single quote.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Turkbear]

Hi,
I should have prefaced my code with the note that it was for those cases that needed string literals,not the case you were responding to -

Since I have always had problems with the 2 successive quotes ( probably from not trying hard enough to use them)
I use chr(39) all the time ( as you undoubtedly know,those CHR functions are also very handy if you need to insert odd chars into database fields where needed - especially those that Oracle's parser would interpret as commands or prompts and someone fails to escape them- like me-)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ekta22]

Sorry for being late in replying. Our database crashed so couldn't try it out. I have implemented the code above in my stored procedure that will pick comments from the old table and load the data in our new table that will store concatenated comments. The Proc compiles just fine but when I try to run it.. it fails saying

ORA-00979: not a GROUP BY expression
ORA-06512: at "STAGING.LOAD_COMMENT", line 4
ORA-06512: at line 2

 

[SantaMufasa]

Ekta,

Please post your code that generated the error so that we can suggest both the exact cause and the solution. (Without your code it is like my posting the error, "ORA-00936: missing expression" without code and asking what caused it and how to fix it. <smile> )

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

True..

Here is my code

create or replace
PROCEDURE LOAD_COMMENT AS
BEGIN

INSERT INTO LOG_COMMENT (
    LOG_COMMENT.LOG_ID,
    LOG_COMMENT.LOG_COMMENT_TEXT,
    LOG_COMMENT.MODIFIED_DATETIME,
    LOG_COMMENT.MODIFIED_BY)
SELECT 
    LOG.LOG_ID,
    comments(     
    'select mms.LOGLCO.COMMENT_TEXT from mms.LOGLCO'
    || ' where mms.LOGLCO.MPS_FAC_BASE_IDENT ||  mms.LOGLCO.MPS_FAC_SUFX_IDENT = ''' || mms.LOGLCO.MPS_FAC_BASE_IDENT || mms.LOGLCO.MPS_FAC_SUFX_IDENT || ''''
    || ' and mms.LOGLCO.ENTRY_YEAR = '|| mms.LOGLCO.ENTRY_YEAR
    || ' and mms.LOGLCO.ENTRY_NUMBER = ' || mms.LOGLCO.ENTRY_NUMBER
    || ' and mms.LOGLCO.ENTRY_SEQUENCE = ' || mms.LOGLCO.ENTRY_SEQUENCE
    || ' order by mms.loglco.COMMENT_SEQUENCE'
    ),
    NVL(LOGLCO.LAST_MODIFIED_DATE_TIME, SYSDATE),
    NVL(PEOPLE.ID, 100)
    FROM MMS.LOGLCO 
    LEFT OUTER JOIN PEOPLE
ON TRIM(LOGLCO.LAST_MODIFIED_USER_INITIALS) = PEOPLE.LOGON_INITIALS
AND TRIM(LOGLCO.LAST_MODIFIED_USER_FAC_REGION) || TRIM(LOGLCO.LAST_MODIFIED_USER_FAC_SECTOR) = PEOPLE.LOGON_SECTOR_CODE
JOIN LOG ON TRIM(LOGLCO.MPS_FAC_BASE_IDENT)||TRIM(LOGLCO.MPS_FAC_SUFX_IDENT)||' '||TRIM(LOGLCO.ENTRY_YEAR)||' '||TRIM(LOGLCO.ENTRY_NUMBER) = LOG.LEGACY_LOGID
GROUP BY MMS.LOGLCO.MPS_FAC_BASE_IDENT || MMS.LOGLCO.MPS_FAC_SUFX_IDENT,
         MMS.LOGLCO.ENTRY_YEAR, MMS.LOGLCO.ENTRY_NUMBER
        ,MMS.LOGLCO.ENTRY_SEQUENCE; 
     
      
END LOAD_COMMENT;

 

[SantaMufasa]

ekta,

I cannot test your code myself, but modify your "GROUP BY" to instead read:

...
 GROUP BY LOG.LOG_ID,
       NVL(LOGLCO.LAST_MODIFIED_DATE_TIME, SYSDATE),
       NVL(PEOPLE.ID, 100)...

Let us know if that improves things.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

Still same error..:-(

 

[SantaMufasa]

Since I haven't your data/tables, I cannot test your code to the point of being error free, but the "absolute" rule of Oracle (and probably other vendors) when it comes to "GROUPed" expressions is that once you have referred to even a single GROUPed expression in a SELECT clause (either via an "aggregate" function [e.g., MIN, MAX, AVG, SUM, COUNT, et cetera]; or by the "GROUP BY" clause itself), then ALL remaining expressions that you mention in the same SELECT must also appear in the GROUP BY clause.

So, one method of isolating your problem is to cut down your query to "bare minimums" until the error goes away, then start adding back in query components until you identify the offending code piece.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

Hello,

Happy Friday!

Just wanted to give you an update that I got it to work.. and your function works great. Thanks.

I had to do my group by select statement within another select to make it work.