Home » SQL & PL/SQL » SQL & PL/SQL » Dates and Sorting Data
Dates and Sorting Data [message #189486] Thu, 24 August 2006 15:55 Go to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Howdy folks.

Since my last question got such helpful answers, I'll post again. In a PL/SQL procedure, I need to take different data from several different tables, and place it into a text file. I'm taking data of different shapes and sizes, and writing it all to the file in a single format.

For argument's sake, let's assume there's no way to write this all as 1 SQL statement so that I could do the sorting in a single cursor.

Each piece of data has a date stamp. I need to write the data to the file in order by date. However, the dates may be mixed between the 3 different tables.

For example, the data in the database might look like this:

Table A
Jun 15 datadatadata
Mar 10 datadatadata

Table B
Jun 10 datadatadata

Table C
Jan 15 datadatadata
July 27 datadatadata


In the output file, this needs to all get sorted by date, like this:

Data
Jan 15 datadatadata
Mar 10 datadatadata
Jun 10 datadatadata
Jun 15 datadatadata
July 27 datadatadata


I've never worked with PL/SQL tables or such before. Is there any way (that won't be absurdly complex for a n00b) that I can write all of the data to some kind of temporary table, and then as the final step in the procedure, reselect the data in order by date, and then write it to the file?

I've looked a into "create global temporary table," but it's a bit above my head. I could possibly create a temp table prior to starting, but I'd prefer not to, if I can handle it all within the program.

Ideas welcomed! Smile

Thanks!

Steve
Re: Dates and Sorting Data [message #189488 is a reply to message #189486] Thu, 24 August 2006 15:58 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select col_date,col_data
from tablea
union all
select col_date,col_data
from tableb
union all
select col_date,col_data
from tablec
order by 1;
Re: Dates and Sorting Data [message #189706 is a reply to message #189486] Fri, 25 August 2006 14:12 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
OK, that didn't quite work, although I appreciate the response! It's kind of a mess on the data side, so it wasn't all that easy. Smile

Meanwhile, I gave up and simply created a temporary table in the database. This has worked out fine, as I simply write the disparate data into the temporary table. Then, when I'm ready to write the file, I just select it out ordered appropriately.

Perhaps not the most efficient setup, but luckily this is simply a one-time extraction program, so it doesn't need to be terribly slick, it just needs to function!

Thanks again for the help. (Posting another question in a minute, if you're bored. Smile )

Steve
Re: Dates and Sorting Data [message #189717 is a reply to message #189706] Fri, 25 August 2006 17:33 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Steve,

If you think you might like to continue receiving prompt responses here, it's probably not a good idea to respond with
Quote:

OK, that didn't quite work, although I appreciate the response! It's kind of a mess on the data side, so it wasn't all that easy.
The solution will definitely work for the problem you described, so just saying it didn't work may be perceived as slightly rude. What about it didn't work? There is little that can be added by going through a temporary table. So what are you doing to add data to the temporary table that you are not doing with the union?

Just a suggestion.
Re: Dates and Sorting Data [message #189724 is a reply to message #189486] Fri, 25 August 2006 20:19 Go to previous message
sbattisti
Messages: 39
Registered: June 2005
Member
My apologies, it wasn't my intent to be rude. Sad

I should say, rather, that it isn't that there was anything wrong with the suggestion. Rather, the work I need to do to manipulate the data into a format that can be inserted into the text file is such that I can't grab the data via one query, even with union all (or so I felt after trying it).

For example, I need to select some data from one of the tables, insert those values into variables, and construct a single long text entry with some constant text.

I apologize if it would have been easier to post the actual thing, but I didn't see any way to reasonably do so, with 20+ tables involved, so I did try to distill it to just the aspect that was eluding me. In doing so I may have over-simplified.

Thanks very much...

Steve
Previous Topic: losing NATIONAL CHARACTERS(blob->clob->table). unistr?
Next Topic: How to ignore Header in the data file while loading through SQL Loader
Goto Forum:
  


Current Time: Fri Dec 09 09:47:15 CST 2016

Total time taken to generate the page: 0.08266 seconds