• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

oracle stored procedure for vb.net application

Below is what my table looks like:

Appointment Time|  05/22/2005  |   05/23/2006 |  05/29/2005 | 05/30/2005
8:00                    |        5           |       2            |         4        |       3                      
9:00                    |        6           |       3            |         8        |       2
10:00                  |        0           |       0            |         1        |        4

I want to write a stored procedure in oracle 8 that will return the columns above to a DataReader object in vb.net that I will place in a DataTAble and then bind to a datagrid. Since oracle stores the date and time together, I would first have to determine all the unique Date values and all the unique time values that I have. Is there a sql statement that does this? Could I then store all the date values in a kind of date-array and the time values in a time-array to use in my logic below:

ForEach Date in (Select Date from Date-array order by Date ascending)

  Select values from <Table_Above>
  where Time is (Select Time from Time-array order by Time ascending)

End ForEach

How would I write this in oracle as I don't know oracle that well?
  • 2
2 Solutions
Bob LearnedCommented:
Cross-tab queries:


Crosstab or Pivot Queries

A crosstab query, sometimes known as a pivot query, groups your data in a slightly different way from those we have seen hitherto. A crosstab query can be used to get a result with three rows (one for each project), with each row having three columns (the first listing the projects and then one column for each year) -- like this:

Project        2001        2002
     ID         CHF         CHF
    100      123.00      234.50
    200      543.00      230.00
    300      238.00      120.50


Let's say you want to show the top 3 salary earners in each department as columns. The query needs to return exactly 1 row per department and the row would have 4 columns. The DEPTNO, the name of the highest paid employee in the department, the name of the next highest paid, and so on. Using analytic functions this almost easy, without analytic functions this was virtually impossible.

SELECT deptno,
  MAX(DECODE(seq,1,ename,null)) first,
  MAX(DECODE(seq,2,ename,null)) second,
  MAX(DECODE(seq,3,ename,null)) third
FROM (SELECT deptno, ename,
       OVER (PARTITION BY deptno
             ORDER BY sal desc NULLS LAST) seq
       FROM emp)
WHERE seq <= 3
GROUP BY deptno

Hi,this is a nice but very complex analytic function.I'm not sure it will work .May be more simple here to use a PLAIN PLSQL block with one record for all arrays rec_time_table
type datearrt is table of date;
datearr datearrt ;
timearr datearrt ;
CURSOR  recCUR  IS (Select * from rec_time_table order by Date ascending);
 recCURt   recCUR%ROWTYPE;  
fetch recCUR.date bulk collect into datearr;
fetch recCUR.time bulk collect into timearr;
for i in recCUR  
IF i.time = timearr.time
select i.* into recCUR%ROWTYPE;
end if;
End loop;
SanmarieAuthor Commented:
Thanks all. Let me try the solutions and I will get back to you.

SanmarieAuthor Commented:

Thank you TheLearnedOne, I will use the select statement.

Oleggold, could you explain CURSORs. I'm not sure what the cursor is doing. Is it a temporary storage area for the array? This is what I plan to do:

1)Find out the columns given a date range and place in array
2)Find out the unique times for the given date range and place in array
3)Finally, I can use TheLearnedOne's select to return the cross-tab results


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now