Using For Loop to attain single string from multiple rows

[fibonaccii]

Thank you all for being so responsive to my queries. I have another one.

I am trying to use a for loop to attain a single string into a local variable from a multiple rows with same Roll_NO. Is there an easier way to extract the rows to the string.

I have a layout of the Table XYZ as below

RN Roll_NO Amount Date

1 123456 5.00 3/3/07
2 123456 10.00 6/7/07
3 123456 20.00 8/8/07
4 123456 30.00 9/9/07

A string that tells me := 3/3/07 $5.00 (next line)
6/7/07 $10.00
....

The Code I have looks as below:

History Varchar2(800);
RowCNT NUMBER;
crlf VARCHAR(2);

BEGIN
crlf := CHR(13) || CHR(10);--(next line designator)

Create Table Temp_V as
SELECT rownum rn, Roll_NO, Amount, Due_date
FROM TABLE.DBLINK
where ROLL_NO = '123456'
and (extract(year from due_Date)) = EXTRACT(YEAR FROM sysdate)
and SUBSTR(ORIGIN,1,3) in ('FTX','ITX','STX')
order by due_date;

Begin

SELECT COUNT(*) into RowCNT
from Temp_v where ROLL_NO = '070067221660000'

For i IN 1..Rowcnt
LOOP
Select 'Date: 'Due_Date ||' '|| to_char(AMOUNT,'$9999.99') || crfl
Into History
WHERE Roll_N0 ='070067221660000'
and RowCNT = i;
end LOOP;
END;

drop table temp_v

 

[SantaMufasa]

A nice function that strings data from multiple rows onto/into one expression of output is 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) := chr(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;
/

The thing that makes the function nice is that you can use it in plain, old SQL, as I have done, below, using your data (plus additional rows) to illustrate proof of concept:

select * from temp_v;

RN    ROLL_NO     AMOUNT DT
-- ---------- ---------- -------
 1     123456          5 3/3/07
 2     123456         10 6/7/07
 3     123456         20 8/8/07
 4     123456         30 9/9/07
 5     234567         15 3/13/07
 6     234567        110 6/17/07
 7     234567        120 8/18/07

7 rows selected.

select roll_no
      ,matrix('select dt||chr(9)||
                       to_char(amount,''$9,999.99'')'||
                 'from temp_v where roll_no = '||roll_no) text
  from temp_v
 group by roll_no;

ROLL_NO TEXT
------- -----------------------
 123456 3/3/07            $5.00
        6/7/07           $10.00
        8/8/07           $20.00
        9/9/07           $30.00

 234567 3/13/07          $15.00
        6/17/07         $110.00
        8/18/07         $120.00

Is this what you wanted?



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

 

[fibonaccii]

Hi Mufasa,

Thanx for the help, It does solve half the equation.

The other is to be able to extract Dynamic Query Output and store in the Table (embedded in a stored procedure).

To make it simple I am running a stored procedure in which I am trying to call The select statement as below. But It brings back multiple rows as displayed in the Data Grid, but I would like to store this in a TEMP table OR in a local variable(the result(PREFRERED)) as a String so I can then return it back to the application.

ANY TAKES ON THIS...

SELECT rownum rn, Roll_NO, Amount, Due_date
FROM TABLE.DBLINK
where ROLL_NO = '123456'
and (extract(year from due_Date)) = EXTRACT(YEAR FROM sysdate)
and SUBSTR(ORIGIN,1,3) in ('FTX','ITX','STX')
order by due_date;

 

[SantaMufasa]

Fibonaccii,

If I understand what you are saying, you want to store the results of your SELECT statement (above) in a structure that allows you to process each row from the SELECT on an as-needed basis, right?

If that is the case, then the easiest method is with an "implicit CURSOR":

BEGIN
    ...
    FOR x IN (SELECT rownum rn, Roll_NO, Amount, Due_date
                FROM TABLE.DBLINK
               where ROLL_NO = '123456'
                 and (extract(year from due_Date))
                      = EXTRACT (YEAR FROM sysdate) 
                 and SUBSTR(ORIGIN,1,3) in ('FTX','ITX','STX')
                order by due_date
              ) LOOP
        ...<do row-by-row processing here, referring to
            each SELECTed expression, above, as:
            x.rn, x.roll_no, x.amount, and x.due_date>...
    END LOOP;
    ...
END;
/

Let us know if this helps to resolve your need.


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

 

[fibonaccii]

Mufasa,

My challenge is to run a Stored Procedure that would allow me to acess data which from the beginning has been

SELECT rownum rn, Roll_NO, Amount, Due_date
FROM TABLE.DBLINK
where ROLL_NO = '123456'
and (extract(year from due_Date)) = EXTRACT(YEAR FROM sysdate)
and SUBSTR(ORIGIN,1,3) in ('FTX','ITX','STX')
order by due_date;

Now this when run in the Query analyzer of Toad application results the Dynamic output in the grid. Which is not one row but multiple rows. What I am seeking is to be able to Access this Dynamic query and capture the Output into a temp table OR into a local Variable in the stored Procedure (Preferred).

Lets say Local Variable is History.

I have something as below, but I am unable to execute this for some reason.

sql_stmt := 'SELECT Amount, Due_date
FROM Table.DBlink
where ROLL_NO = '123456'
and (extract(year from due_Date)) = EXTRACT(YEAR FROM sysdate)
and SUBSTR(ORIGIN,1,3) in (''FTX'',''ITX'',''STX'')
order by due_date;';
EXECUTE IMMEDIATE sql_stmt INTO History USING '123456';

I want to avoid using a temp Table, well lets just say I am not having any luck with that either.

I have been reading of the following links: maybe u can have a better understanding then I can.

http://orafaq.com/faqplsql.htm#UTLFILE

http://www.devx.com/tips/Tip/14209

 

[SantaMufasa]

Another method that may give you what you want is to "bulk" load a memory table with the data you want, and you can access the data randomly to do with as you wish. Here is a simple example that you can use as a template to build your code.

First, here are some sample data:

select * from s_region;

   ID NAME
----- --------------------
    1 North America
    2 South America
    3 Africa / Middle East
    4 Asia
    5 Europe
    6 Australia

Here, then, is code that loads those data into a memory table and I contrive some simple code to illustrate random access of the data:

set serveroutput on
declare
    type y is table of s_region%rowtype;
    x y;
begin
    select * bulk collect into x from s_region;
    dbms_output.put_line(x(1).id||': '||x(1).name);
    dbms_output.put_line(x(4).id||': '||x(4).name);
end;
/
1: North America
4: Asia

PL/SQL procedure successfully completed.

If I knew more of what you wanted, I could build more detailed sample code, below, but following approximates what you can do to load your data with a single SELECT, then access your data from a memory table as you need it:

declare
    type y is table of Table.DBlink%rowtype;
    x y;
begin
    select * bulk collect into x
      from Table.DBlink
     where where ROLL_NO = '123456'
       and (extract(year from due_Date)) =
            EXTRACT(YEAR FROM sysdate) 
       and SUBSTR(ORIGIN,1,3) in (''FTX'',''ITX'',''STX'')
     order by due_date;
     ...<additional processing here>...
end;
/

Let us know if you have additional questions.


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

 

[fibonaccii]

Mufasa,

Thanks for all the help But I am not getting anywhere with this. What you suggested, I am unable to apply to my Stored Procedure.

You seem to have the knowledge but I am unable to implement the same. Lets try this one last time.

In the query below the Roll No is not unique.

SELECT rownum rn, Roll_NO, Amount, Due_date
FROM TABLE.DBLINK
where ROLL_NO = '123456'
and (extract(year from due_Date)) = EXTRACT(YEAR FROM sysdate)
and SUBSTR(ORIGIN,1,3) in ('FTX','ITX','STX')
order by due_date;

So when I run the statement in the analyzer it displays back dynamic output which is similar to this. It is basically a virtual display. The Select statement at this point has no place holder, it is on the fly.

RN Roll_NO Amount Date

1 123456 5.00 3/3/07
2 123456 10.00 6/7/07
3 123456 20.00 8/8/07
4 123456 30.00 9/9/07

I would like to capture this in a Variable (IMP: Embeded in a stored Procedure) that will just display as under.

Date Amount

3/3/07 5.00
6/7/07 10.00
8/8/07 20.00
9/9/07 30.00

Do you believe this can be done.

 

[SantaMufasa]

I'm sorry that I'm not seeing the problem, Fibonaccii. If your SELECT is already returning those four rows, then why can you not simply ask for just the DATE and AMOUNT columns?

Also,

The Select statement at this point has no place holder, it is on the fly.

You can embed into an EXECUTE IMMEDIATE statement the "BULK COLLECT" code to gather what you need into a memory table. You just must ensure that you have a TYPE structure that matches what you are SELECTing.


I'm sorry that I cannot be more specific about a solution unless I have more specifications on how the application is to behave.

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

 

[fibonaccii]

HI Mufasa,

I some how got the Data to fill in a temp table.

Then I used the matrix function to load the data into a local variable and displayed the info. But the ouput is as below in one single line through the variable:

01-MAR-07|$1,027.00|01-MAY-07|$1,026.68|03-JUL-07|$1,087.00|04-SEP-07|$1,086.12

In the GRID It comes out as above but the OUTPUT TAB it presents as.. WIERD.

01-MAR-07 $1,027.00
01-MAY-07 $1,026.68
03-JUL-07 $1,087.00
04-SEP-07 $1,086.12

But I have to thank you for all the help. i'll try to fix this one out as well. If you have any inputs , fill me in.

 

[fibonaccii]

Mufasa,

In the select statement below I am not selecting the T_ROLLNO in the select statement, reason being I am only looking for the Dates and Amounts.

When I do put in the T_ROLLNO in the select statement, I receive an error message.

PL/SQL: ORA-00947: not enough values
-------

BEGIN
select matrix('select T_DUE_DATE||chr(9)||
to_char(T_Amount,''$9,999.99'')'||
'from Temp_v where T_ROLLNO = '||Roll_no_pad)
INTO History
From Temp_v
group by T_ROLLNO;
END;

Is there a work around, so that I can call the function without the group by clause.

 

[fibonaccii]

Mufasa,

I am finally managed to make it happen

I had to change your Matrix function a little to display the output the way I wanted.

Cheers

 

[SantaMufasa]

Congratulations! Use it in good health and happinesss.

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