Home » SQL & PL/SQL » Client Tools » Advice on Best way to create this Report (Oracle SQL Developer)
Advice on Best way to create this Report [message #524450] Fri, 23 September 2011 15:37 Go to next message
lisaO
Messages: 2
Registered: September 2011
Junior Member
I have a client that is requesting a report for specific data. they want this in datasheet view. The data will be coming from at least 7 tables. Should I just link all the tables and pull the data that we have (some we don't capture and I would like to put a place holder field there) Orrrr should I create a temporary table that names all the fields required then do a Select and insert into this table that will fill all the data in the format they requested. I have done this with Regular Sql Server but not Oracle and it doesnt seem to want to work correctly. Can someone give some direction?? Im not even sure if you can do this with Oracle. I tried using the same code I did for SQL but I get errors (I know it is somewhat different syntax but I can probably look up those differences)

thanks!
Re: Advice on Best way to create this Report [message #524480 is a reply to message #524450] Sat, 24 September 2011 01:14 Go to previous messageGo to next message
John Watson
Messages: 4508
Registered: January 2010
Location: Global Village
Senior Member
Hi, Lisa - I don't know what you mean by "datasheet view", but apart from that:
Many times when people move from a SQL Server (or Sybase) environment to Oracle I see the same thing. They try to use "temporary" tables. Don't do it. Oracle has a superior capability for ensuring consistency, without row locking, for long running queries across many tables. It is best to do everything in one, horribly complicated, SELECT statetment. Do not hesitate to use inline views, subqueries, the WITH clause, and so on.


Re: Advice on Best way to create this Report [message #524744 is a reply to message #524480] Mon, 26 September 2011 08:07 Go to previous messageGo to next message
lisaO
Messages: 2
Registered: September 2011
Junior Member
Thank you I was thinking that is how I should go as well. I have a report that is requesting 77 data objects all the information comes from various tables. A long select then is what I will do. Thanks again!
Re: Advice on Best way to create this Report [message #524749 is a reply to message #524744] Mon, 26 September 2011 08:25 Go to previous message
Roachcoach
Messages: 1198
Registered: May 2010
Location: UK
Senior Member
Keep in mind SQL developer defaults to returning the first N rows (usually 50).

Those 50 can come back pretty quick, but if it is a long query/lot of rows then the full export can take a REALLY long time.

[Updated on: Mon, 26 September 2011 08:26]

Report message to a moderator

Previous Topic: SQL Developer Shortcut
Next Topic: SQL Developer Data Wizard - Row terminator question
Goto Forum:
  


Current Time: Wed Aug 27 22:13:19 CDT 2014

Total time taken to generate the page: 6.87899 seconds