Sanmarie
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Sanna
ASKER
Sanna