Output layout

[neskin]

Hello,
I have a table with data
date runname
01/01/05 09R
01/01/05 27L
02/01/05 09L
02/01/05 28R

My output need to be like
date runname
01/01/05 09R 27L
02/01/05 09L 28R

Thank you in advance
Nat

 

[SantaMufasa]

Neskin,

For output like yours, I particularly like to use a user-defined function such as this one:

create or replace function get_runnames (dt_in date) return varchar2 is
        hold_names varchar2(100);
    begin
        for x in (select runname from neskin where dt = dt_in) loop
            hold_names := hold_names||’ ‘||x.runname;
        end loop;
        return trim(hold_names);
    end;
/

Function created.

Once you have created the function, you can then access it to produce your results with this code:

col runname format a7
col dt heading "Date" format a8
select distinct to_char(dt,'mm/dd/yy') dt
      ,get_runnames(dt) runname
    from neskin
  order by dt;

Date     RUNNAME
-------- -------
01/01/05 09R 27L
02/01/05 09L 28R

By the way, "DATE" is a reserved word in Oracle, so you cannot use it as a column name as you did in your original post.

Let us know if this technique meets with your satisfaction.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[neskin]

thank you for your quick response
About the date i know it was just as example
I am new to Oracle .
My new question is I am creating a procedure in Oracle then I am using as a datasource for crystal report .
Can you advice me please what is the best way to do it
Do I still have to create a user _define function or I can use a script in side of the procedure
thanks
Nat

 

[SantaMufasa]

Nat,

Sorry, the only thing I know about Kristle Reprots is how to spell it. If you don't get any takers here to answer your question, then I recommend posting your follow-on question in one of the "Business Objects: Crystal..." fora.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[neskin]

don't worry about crystal can you tell me if it can be done in the procedure instead of user define function

 

[SantaMufasa]

Neskin,

The fundamental difference between a procedure and a function is that since a function is an expression, you treat it as data, whereas you cannot treat a procedure as a data expression. Therefore, you cannot refer to a procedure explicitly in a SQL SELECT statement, but you certainly can refer to both built-in and user-defined functions in a SELECT statement -- Oracle depends very heavily upon functions (e.g., SYSDATE, SUBSTR, TO_CHAR, TRIM, UPPER, LOWER, INITCAP, INSTR, MIN, MAX, SUM, COUNT, AVG, et cetera).

Did this adequately answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]