SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Juanito: Jan 20, 2004; I wasn't paying attention
to what you just said.

Can you repeat yourself
in a more interesting way?; Hell Gem

My query knowledge is very basic.

Right now I've got two tables, one has guestbook information, the other has guestbook entries. What query could I run to delete orphaned guestbook entries? How could I find how which they are to remove them?

Both of these tables use member_id.

select count (*) from guestbook_entry
161337

SELECT COUNT( * )
FROM guestbook_entry, guestbook_member
WHERE guestbook_entry.member_id = guestbook_member.member_id
155275

Thanks!

# ? Mar 10, 2010 02:53

Adbot: ADBOT LOVES YOU

# ? May 31, 2024 14:53

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Let's say you have a table that stores records for sales of sporting goods:

Sale
--------
Id VARCHAR(20)
Product VARCHAR(30)
Purpose VARCHAR(50)
PRIMARY KEY (Id)
INDEX (Product)
INDEX (Purpose)

If I want to see how many of each product was sold for Professional sports:

code:

SELECT Product, COUNT(*) FROM Sale WHERE Purpose = 'Professional' GROUP BY Product

With 50 million rows in Sale, 3 million of which are for Purpose 'Professional', this query takes about 10 seconds. An EXPLAIN indicates that it is using the index on the field Purpose along with "Using where; Using temporary; Using filesort"

Can I get this query to run any faster or is 10 seconds about right with that much data?

# ? Mar 10, 2010 02:54

DTMTCM: Feb 18, 2005; I misclicked post icon so I have this account now.

Juanito posted:

My query knowledge is very basic.

Right now I've got two tables, one has guestbook information, the other has guestbook entries. What query could I run to delete orphaned guestbook entries? How could I find how which they are to remove them?

Both of these tables use member_id.

select count (*) from guestbook_entry
161337

SELECT COUNT( * )
FROM guestbook_entry, guestbook_member
WHERE guestbook_entry.member_id = guestbook_member.member_id
155275

Thanks!

When I run into this situation, I usually do a LEFT JOIN and see which records have NULL values. (The SQL query below may be dependent on your db.)

code:

SELECT COUNT( * ) 
FROM guestbook_entry LEFT JOIN guestbook_member ON guestbook_entry.member_id = guestbook_member.member_id
WHERE guestbook_member.member_id IS NULL

# ? Mar 10, 2010 03:00

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

fletcher posted:

Let's say you have a table that stores records for sales of sporting goods:

Sale
--------
Id VARCHAR(20)
Product VARCHAR(30)
Purpose VARCHAR(50)
PRIMARY KEY (Id)
INDEX (Product)
INDEX (Purpose)

If I want to see how many of each product was sold for Professional sports:
code:
SELECT Product, COUNT(*) FROM Sale WHERE Purpose = 'Professional' GROUP BY Product
With 50 million rows in Sale, 3 million of which are for Purpose 'Professional', this query takes about 10 seconds. An EXPLAIN indicates that it is using the index on the field Purpose along with "Using where; Using temporary; Using filesort"

Can I get this query to run any faster or is 10 seconds about right with that much data?

You could change the index on Purpose so that it is an index on (Purpose, Product). Your table design looks kind of odd, though. Is there a reason you are using VARCHAR columns for this, instead of integer type columns that refer to other tables?

# ? Mar 10, 2010 03:09

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Hammerite posted:

You could change the index on Purpose so that it is an index on (Purpose, Product).

Actually, maybe not, since those a literally the only columns in the table. I still find the VARCHAR thing odd.

# ? Mar 10, 2010 03:11

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Hammerite posted:

Actually, maybe not, since those a literally the only columns in the table. I still find the VARCHAR thing odd.

Yeah, ideally they would be integers that refer to a ProductId or PurposeId, but this data is provided by a third party. Assume that we don't even have a "Product" table, just the information in the Sale table.

# ? Mar 10, 2010 03:20

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

fletcher posted:

Let's say you have a table that stores records for sales of sporting goods:

Sale
--------
Id VARCHAR(20)
Product VARCHAR(30)
Purpose VARCHAR(50)
PRIMARY KEY (Id)
INDEX (Product)
INDEX (Purpose)

If I want to see how many of each product was sold for Professional sports:
code:
SELECT Product, COUNT(*) FROM Sale WHERE Purpose = 'Professional' GROUP BY Product
With 50 million rows in Sale, 3 million of which are for Purpose 'Professional', this query takes about 10 seconds. An EXPLAIN indicates that it is using the index on the field Purpose along with "Using where; Using temporary; Using filesort"

Can I get this query to run any faster or is 10 seconds about right with that much data?

Do you have "packed" keys on or off for this table?

I assumbe those are btree index's?

Is this an InnoDB table type?

What is your Row Format Type?

Have you run an Check, Optimize or Repair Recently?

Have you tried to make a View doing the same thing?

code:

Sale
--------
Id VARCHAR(20)
Product VARCHAR(30)
Purpose VARCHAR(50)
PRIMARY KEY (Id)
INDEX (Purpose,Product)

SELECT Product, COUNT(Id) FROM Sale WHERE Purpose = 'Professional' GROUP BY Product

may run a little faster

Sprawl fucked around with this message at 19:02 on Mar 10, 2010

# ? Mar 10, 2010 18:56

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Sprawl posted:

Do you have "packed" keys on or off for this table?

I assumbe those are btree index's?

Is this an InnoDB table type?

What is your Row Format Type?

Have you run an Check, Optimize or Repair Recently?

Have you tried to make a View doing the same thing?

Hadn't heard of PACK_KEYS until you mentioned it. I don't specify, so I believe it is using the default behavior of packing strings and not numbers, which sounds fine. This table will never be updated, so I only care about SELECT performance.

This is a MyISAM table.

Dynamic row format type.

No, No, No, and No.

# ? Mar 10, 2010 20:46

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

fletcher posted:

Hadn't heard of PACK_KEYS until you mentioned it. I don't specify, so I believe it is using the default behavior of packing strings and not numbers, which sounds fine. This table will never be updated, so I only care about SELECT performance.

This is a MyISAM table.

Dynamic row format type.

No, No, No, and No.

You could try setting Pack_Keys = 1 see if that makes a difference ( it does on varchar searches sometimes )

Try changing the Row format to Static ( this can take a long time depending on the size of the table i think yours will be a while )

And then run a Check if it found something it doesn't like run a Repair and then An Optimize at the end.

Also try the InnoDB table format that can sometimes make a difference when tables get that big.

Sprawl fucked around with this message at 20:55 on Mar 10, 2010

# ? Mar 10, 2010 20:52

MoNsTeR: Jun 29, 2002

10 seconds to read 3M rows is pretty solid. Any improvement you can get is likely going to come from hardware.

# ? Mar 10, 2010 21:01

Squashy Nipples: Aug 18, 2007

Good lord, don't write any queries for a few months, and your SQL gets rusty. The other day I got "DISTINCT" confused with "UNIQUE".

Anyway, is it OK to put stuff in your HAVING that you are not SELECTing?
Any pitfalls with this?

Basically, I'm trying to get a list of distinct funds, and the description field for the [FeeDescription] (text field, these arrangements get complicated) from a billing summary table that lists [Fee Paid] on each line. Sadly, there are many ways to express "no fee" in the [FeeDescription] (this is NOT a DB that I designed), so it would be easier to only pick the consolidated rows that have a SUM of the [Fee Paid] fields that is greater then zero.

# ? Mar 10, 2010 22:53

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Squashy Nipples posted:

Good lord, don't write any queries for a few months, and your SQL gets rusty. The other day I got "DISTINCT" confused with "UNIQUE".

Anyway, is it OK to put stuff in your HAVING that you are not SELECTing?
Any pitfalls with this?

Basically, I'm trying to get a list of distinct funds, and the description field for the [FeeDescription] (text field, these arrangements get complicated) from a billing summary table that lists [Fee Paid] on each line. Sadly, there are many ways to express "no fee" in the [FeeDescription] (this is NOT a DB that I designed), so it would be easier to only pick the consolidated rows that have a SUM of the [Fee Paid] fields that is greater then zero.

Well since Having is basically a secondary where clause on the complete dataset after where group and order the Sum()'s fire before having so i dunno if that would do what you want.

# ? Mar 10, 2010 23:04

Squashy Nipples: Aug 18, 2007

Sprawl posted:

Well since Having is basically a secondary where clause on the complete dataset after where group and order the Sum()'s fire before having so i dunno if that would do what you want.

Oh yeah.

Well, I've back to the drawing board, probably going to use a NOT IN list in the WHERE clause to try and strain out all the no fee conditions.

Now I just have to make that list.

# ? Mar 10, 2010 23:19

Juanito: Jan 20, 2004; I wasn't paying attention
to what you just said.

Can you repeat yourself
in a more interesting way?; Hell Gem

I'm trying to create a query which would let me pull up only guestbooks where there have not been any entries since a specific date in time.

SELECT count(*)
FROM guestbook_member
LEFT JOIN guestbook_entry ON guestbook_member.member_id = guestbook_entry.member_id
WHERE guestbook_entry.post_date < {ts '2006-12-31'}

I've got this query that pulls up all old posts.. but what would I have to add to it to make sure that I wouldn't pull up any guestbooks where there are also newer posts?

# ? Mar 11, 2010 19:56

Kekekela: Oct 28, 2004

Juanito posted:

I'm trying to create a query which would let me pull up only guestbooks where there have not been any entries since a specific date in time.

SELECT count(*)
FROM guestbook_member
LEFT JOIN guestbook_entry ON guestbook_member.member_id = guestbook_entry.member_id
WHERE guestbook_entry.post_date < {ts '2006-12-31'}

I've got this query that pulls up all old posts.. but what would I have to add to it to make sure that I wouldn't pull up any guestbooks where there are also newer posts?

It sounds like this is what you're looking for if I'm understanding your question correctly:
SELECT count(*)
FROM guestbook_member
LEFT JOIN guestbook_entry ON guestbook_member.member_id = guestbook_entry.member_id
WHERE MAX(guestbook_entry.post_date) < {ts '2006-12-31'}

# ? Mar 12, 2010 00:26

Juanito: Jan 20, 2004; I wasn't paying attention
to what you just said.

Can you repeat yourself
in a more interesting way?; Hell Gem

Kekekela posted:

It sounds like this is what you're looking for if I'm understanding your question correctly:
SELECT count(*)
FROM guestbook_member
LEFT JOIN guestbook_entry ON guestbook_member.member_id = guestbook_entry.member_id
WHERE MAX(guestbook_entry.post_date) < {ts '2006-12-31'}

I see, I hadn't used MAX before, but it looks like it will do what I want. Find guestbooks where there are only posts from before 2006-12-31.

When I first ran this query, I got this error:
#1111 - Invalid use of group function

I googled, and changed WHERE MAX to HAVING MAX. Then the query would run. Is this the same thing or am I running something different?

Thanks!

# ? Mar 12, 2010 00:47

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Juanito posted:

I'm trying to create a query which would let me pull up only guestbooks where there have not been any entries since a specific date in time.

SELECT count(*)
FROM guestbook_member
LEFT JOIN guestbook_entry ON guestbook_member.member_id = guestbook_entry.member_id
WHERE guestbook_entry.post_date < {ts '2006-12-31'}

I've got this query that pulls up all old posts.. but what would I have to add to it to make sure that I wouldn't pull up any guestbooks where there are also newer posts?

I'm not sure why you did it this way but couldn't you just do a group by and a have "HAVING (count(*) > 0)" or it's equivalent at the end? Because you'll probably want the entry id anyway so you can harvest/deactivate the guestbook right? Maybe something like:

code:

SELECT guestbook_member.member_id,count(*)
FROM guestbook_member
LEFT JOIN guestbook_entry ON 
guestbook_member.member_id = guestbook_entry.member_id
WHERE guestbook_entry.post_date < {ts '2006-12-31'}
GROUP BY guestbook_member.member_id
HAVING (count(*) > 0)

EDIT-oops someone already got it for you ignore

Scaramouche fucked around with this message at 01:19 on Mar 12, 2010

# ? Mar 12, 2010 00:49

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Juanito posted:

I see, I hadn't used MAX before, but it looks like it will do what I want. Find guestbooks where there are only posts from before 2006-12-31.

When I first ran this query, I got this error:
#1111 - Invalid use of group function

I googled, and changed WHERE MAX to HAVING MAX. Then the query would run. Is this the same thing or am I running something different?

Thanks!

Well Max() is an aggregate function and because your joining tables together you would have needed a GROUP BY to use it in the where clause.

# ? Mar 12, 2010 01:11

DTMTCM: Feb 18, 2005; I misclicked post icon so I have this account now.

Edit: ignore this, already answered and better.

# ? Mar 12, 2010 01:22

Kekekela: Oct 28, 2004

Juanito posted:

I see, I hadn't used MAX before, but it looks like it will do what I want. Find guestbooks where there are only posts from before 2006-12-31.

When I first ran this query, I got this error:
#1111 - Invalid use of group function

I googled, and changed WHERE MAX to HAVING MAX. Then the query would run. Is this the same thing or am I running something different?

Thanks!

Yeah, HAVING is the WHERE equivalent for aggregated data, I just f'd up because I was freehanding it and not actually running the query.

# ? Mar 12, 2010 01:46

dangerous.hotdog: Feb 29, 2008

I'm trying to create a procedure on an Oracle 10g database. When I attempt to create this procedure, I get an error:

code:

CREATE OR REPLACE PROCEDURE some_procedure IS
	CURSOR some_cursor IS
		SELECT *
		FROM location
		WHERE active = 'Y'
			AND rownum < 10;

	some_rec some_cursor%ROWTYPE;

BEGIN
	OPEN some_cursor;
	LOOP
		FETCH some_cursor INTO some_rec;
		EXIT WHEN some_cursor%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(some_rec.name);
	END LOOP;
	CLOSE some_cursor;
END;

But, when I replace the CREATE OR REPLACE PROCEDURE with just a DECLARE, everything executes correctly:

code:

DECLARE
	CURSOR some_cursor IS
		SELECT *
		FROM location
		WHERE active = 'Y'
			AND rownum < 10;

	some_rec some_cursor%ROWTYPE;

BEGIN
	OPEN some_cursor;
	LOOP
		FETCH some_cursor INTO some_rec;
		EXIT WHEN some_cursor%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(some_rec.name);
	END LOOP;
	CLOSE some_cursor;
END;

Am I incorrectly declaring the cursor in the procedure?

# ? Mar 12, 2010 02:28

mik: Oct 16, 2003; oh

I have a table with three columns. Each row is a unique combination of two names, plus a number:

name1,name2,number
-----------------
Tom, James, 10
Sally, Fred, 9
James, Fred, 15
Igor, Petunia, 64
Sally, James, 105

...and so forth. As you can see the names themselves are not unique.

I want to be able to pull out the data in two ways. First just shows all rows sorted by the Number, which is obviously simple.

The second way I want to pull out the data is for each unique name (in column 1 or column 2), only pull out the row with the highest number. So the above small sample would be output as such:

name1,name2,number
-----------------
Sally, James 105
Igor, Petunia, 64

Even though Tom is a unique name in itself, its partner James already is combined with someone else (Sally) which has a higher number so I want to pull just that record out. Igor and Petunia aren't in any other rows so I want to pull that row out. As you can see the order of the names (col1 or col2) doesn't matter. A distinct combination of col1 and col2 doesn't work because I don't want to pull out (Sally, James, 105) AND (Sally, Fred, 9), just the former as it's number is higher, and they're distinct when they're put in the database anyway.

I'm an SQL kindergarten student, and would appreciate any help with this query.

Thanks.

mik fucked around with this message at 03:03 on Mar 12, 2010

# ? Mar 12, 2010 02:57

Vanadium Dame: May 29, 2002; HELLO I WOULD LIKE TO TALK TO YOU ABOUT MY VERY STRONG OPINIONS

Something is just *wrong* with the query driving your cursor, I just tested it out on my work machine (also Oracle 10g), and it compiles if I replace that query with something using one of our tables and alias a column in it to "name".

edit: I was responding to dangerous.hotdog
edit 2: Do you maybe have a both a table and a procedure named location under different user schemas? If so try specifying the schema name in your query.

Vanadium Dame fucked around with this message at 03:01 on Mar 12, 2010

# ? Mar 12, 2010 02:57

dangerous.hotdog: Feb 29, 2008

Markoff Chaney posted:

Something is just *wrong* with the query driving your cursor, I just tested it out on my work machine (also Oracle 10g), and it compiles if I replace that query with something using one of our tables and alias a column in it to "name".

edit: I was responding to dangerous.hotdog
edit 2: Do you maybe have a both a table and a procedure named location under different user schemas? If so try specifying the schema name in your query.

Thanks for testing it out. Yes, I usually explicitly write out the schema name (the procedure is in a different schema than the table referenced in the cursor), but that produces the same error. SQL Developer throws an error stating that the cursor was not defined correctly, but from all the examples I've seen, the CREATE PROCEDURE code block should work.

# ? Mar 12, 2010 03:06

Vanadium Dame: May 29, 2002; HELLO I WOULD LIKE TO TALK TO YOU ABOUT MY VERY STRONG OPINIONS

dangerous.hotdog posted:

Thanks for testing it out. Yes, I usually explicitly write out the schema name (the procedure is in a different schema than the table referenced in the cursor), but that produces the same error. SQL Developer throws an error stating that the cursor was not defined correctly, but from all the examples I've seen, the CREATE PROCEDURE code block should work.

I guess I don't really have a solution for you without access to your oracle instance to play around, but try re-writing that query in ways that might seems like something a crazy person would do. Change alias names. Plug a different table in to test things. Oracle is crazy so sometimes you have to do dumb poo poo to make it work.

As an aside you should get your employer to spring for a (retardedly expensive) TOAD for Oracle license, it is soooooo much better than SQL Developer.

# ? Mar 12, 2010 03:19

var1ety: Jul 26, 2004

dangerous.hotdog posted:

Thanks for testing it out. Yes, I usually explicitly write out the schema name (the procedure is in a different schema than the table referenced in the cursor), but that produces the same error. SQL Developer throws an error stating that the cursor was not defined correctly, but from all the examples I've seen, the CREATE PROCEDURE code block should work.

You are probably running into a gotcha with grants and roles that is technically documented, but is buried. Here's the text.

Application Developer's Guide - Fundamentals posted:

Privileges to Create Procedures and Functions

To create a standalone procedure or function, or package specification or body, you must meet the following prerequisites:

* You must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user's schema.

Note:
To create without errors (to compile the procedure or package successfully) requires the following additional privileges:
o The owner of the procedure or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.
o The owner cannot obtain required privileges through roles.

source

# ? Mar 12, 2010 05:07

dangerous.hotdog: Feb 29, 2008

Thanks for that, var1ety. I'll have our DBA look into it.

Markoff Chaney posted:

As an aside you should get your employer to spring for a (retardedly expensive) TOAD for Oracle license, it is soooooo much better than SQL Developer.

I started using Aqua Data Studio about a year ago and I feel like it's lightyears ahead of TOAD. Edit: And it has a practically unlimited evaluation period.

dangerous.hotdog fucked around with this message at 05:47 on Mar 12, 2010

# ? Mar 12, 2010 05:44

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

mik posted:

I have a table with three columns. Each row is a unique combination of two names, plus a number:

name1,name2,number
-----------------
Tom, James, 10
Sally, Fred, 9
James, Fred, 15
Igor, Petunia, 64
Sally, James, 105

...and so forth. As you can see the names themselves are not unique.

I want to be able to pull out the data in two ways. First just shows all rows sorted by the Number, which is obviously simple.

The second way I want to pull out the data is for each unique name (in column 1 or column 2), only pull out the row with the highest number. So the above small sample would be output as such:

name1,name2,number
-----------------
Sally, James 105
Igor, Petunia, 64

Even though Tom is a unique name in itself, its partner James already is combined with someone else (Sally) which has a higher number so I want to pull just that record out. Igor and Petunia aren't in any other rows so I want to pull that row out. As you can see the order of the names (col1 or col2) doesn't matter. A distinct combination of col1 and col2 doesn't work because I don't want to pull out (Sally, James, 105) AND (Sally, Fred, 9), just the former as it's number is higher, and they're distinct when they're put in the database anyway.

I'm an SQL kindergarten student, and would appreciate any help with this query.

Thanks.

The easiest way would just to use Max() on the number field and group by name1,name2

code:

select name1, name2, max(number)
from table1 
group by name1,name2

# ? Mar 12, 2010 08:38

Vanadium Dame: May 29, 2002; HELLO I WOULD LIKE TO TALK TO YOU ABOUT MY VERY STRONG OPINIONS

dangerous.hotdog posted:

Thanks for that, var1ety. I'll have our DBA look into it.

I started using Aqua Data Studio about a year ago and I feel like it's lightyears ahead of TOAD. Edit: And it has a practically unlimited evaluation period.

Played around with ADS for the last half hour, I like the visual look of it and there are some interface quirks that are annoying but I could get over them. Here's the big question: Is there a ADS equivalent of the F4 hotkey in TOAD? Placing my cursor on an object name and bringing up an interactive window of information about that object is crucial in my opinion.

# ? Mar 12, 2010 18:26

MoNsTeR: Jun 29, 2002

dangerous.hotdog posted:

Thanks for that, var1ety. I'll have our DBA look into it.

I started using Aqua Data Studio about a year ago and I feel like it's lightyears ahead of TOAD. Edit: And it has a practically unlimited evaluation period.

The key to understanding TOAD's superiority over every other tool on the market is the Describe window. I know what you're thinking, "Describe? What? Everyone knows what that does how is that special?" I will tell you. Describe a table in TOAD. First note that this is a single key, F4, which is easy to remember and hit. Now note the multiplicity of tabs. The Columns tab shows you what everyone expects Describe to be, and yes this is unexciting. Now look at the other tabs. Indexes. Constraints. Triggers. Data. Grants. Synonyms. Partitions. Subpartitions. Stats/Size. Referential. Used By. Policies. Auditing. Now look at the tabs available on an Index, Function, Procedure, Package, Tablespace, User, Trigger, Sequence. Consider the ULTIMATE POWER represented by the Used By and Uses tabs, particularly in enterprise environments where you have thousands of tables and hundreds of procedures and packages.

Yes, I am a total TOAD fanboy. The Describe window is why. Until a competitor shamelessly copies it, they will all be nothing more than also-rans in the SQL IDE market.

# ? Mar 12, 2010 18:31

var1ety: Jul 26, 2004

Our company uses PL/SQL Developer (http://www.allroundautomations.com/bodyplsqldev.html) as a TOAD alternative and I like it pretty well.

A single user copy is $180, but a 10 user copy is only $90/seat, with a $30/seat (optional) annual service contract.

The "Edit Object" window for tables shows all of your related objects in one place (constraints, indexes, etc), as well as letting you pull out the creation SQL or modify the physical properties of the table right there.

For me two of the cooler features, though, are the SQL Test window and the ODBC Import tool.

The SQL Test window lets you generate a testing stub for a statement or procedure call with type correct bind variables with a single click (including complex types like VARRAYs), and after execution lets you drill into any CURSORs your function returned to the caller with a single click.

The ODBC Import tool will connect to an Excel/Access worksheet and let you easily import into an Oracle database. It will pull column type/sizings from Access and generate a "create table" stub with a single click, as well.

# ? Mar 12, 2010 18:50

dangerous.hotdog: Feb 29, 2008

Markoff Chaney posted:

Played around with ADS for the last half hour, I like the visual look of it and there are some interface quirks that are annoying but I could get over them. Here's the big question: Is there a ADS equivalent of the F4 hotkey in TOAD? Placing my cursor on an object name and bringing up an interactive window of information about that object is crucial in my opinion.

The Describe hotkey is Ctrl+D. However, from what MoNsTeR wrote, it sounds like ADS's Describe function is nowhere as powerful as TOAD's.

We have a TOAD license which I haven't used in forever; I'll check it out again, MoNsTeR. Thanks for the heads up.

var1ety, I think you found the issue with procedure. I created a table in the same schema as the procedure and insert some temp data into that table. I created the procedure with the cursor referencing the table in the same schema and the procedure compiled and ran correctly.

# ? Mar 12, 2010 19:26

Xae: Jan 19, 2005

Toad is targeted more towards DBAs, PL/SQL Developer is geared towards... developers.

I bought a personal license of PL/SQL Developer because I hate using the constantly crashing bug ridden save file corrupting POS that is Toad. I have used Toad at 3-4 different companies and it is always a flaming piece of poo poo. I can't fathom why people spend any money, let alone the $500+ licensing cost for it.

Edit: I'm sounding a bit too harsh, Toad has a lot of power, but it has a lot of bloat and a lot of problems. As a developer the bloat and crashing outweigh the more DBA-Oriented features, plus it is pretty expensive.

Xae fucked around with this message at 00:46 on Mar 13, 2010

# ? Mar 13, 2010 00:39

Vanadium Dame: May 29, 2002; HELLO I WOULD LIKE TO TALK TO YOU ABOUT MY VERY STRONG OPINIONS

Xae posted:

Toad is targeted more towards DBAs, PL/SQL Developer is geared towards... developers.

I bought a personal license of PL/SQL Developer because I hate using the constantly crashing bug ridden save file corrupting POS that is Toad. I have used Toad at 3-4 different companies and it is always a flaming piece of poo poo. I can't fathom why people spend any money, let alone the $500+ licensing cost for it.

Edit: I'm sounding a bit too harsh, Toad has a lot of power, but it has a lot of bloat and a lot of problems. As a developer the bloat and crashing outweigh the more DBA-Oriented features, plus it is pretty expensive.

I agree that it is buggy and crashy, but it's actually gotten a lot better with that over the years. Toad 9 and up is pretty good about that. I would eat a bullet if I was still using Toad 7.

That said, while I do use it for some minor DBA poo poo like adding datafiles and killing connections to resolve locks etc., most of my work is writing PL/SQL and I think it works great for that too. The Power of the F4 Key cannot be disputed. :colbert:

# ? Mar 13, 2010 00:55

Belgarath: Feb 21, 2003

Can anyone point me in the direction of a decent way to debug Oracle Java Stored Procedures? There is now quite a bit of business logic in Java on the database, and I've never been able to debug it satisfactorily.

SQL Developer is poo poo. In fact, the latest version refuses to even show the stored Java, it just stops responding and has to be killed via task manager. I've never been able to get Eclipse, JDeveloper (also poo poo) or Netbeans to debug using JDWP.

Anyone?

# ? Mar 13, 2010 10:28

Kekekela: Oct 28, 2004

Belgarath posted:

Can anyone point me in the direction of a decent way to debug Oracle Java Stored Procedures? There is now quite a bit of business logic in Java on the database, and I've never been able to debug it satisfactorily.

SQL Developer is poo poo. In fact, the latest version refuses to even show the stored Java, it just stops responding and has to be killed via task manager. I've never been able to get Eclipse, JDeveloper (also poo poo) or Netbeans to debug using JDWP.

Anyone?

I've been using something from Quest that I think is called Sql Optimizer which may work for you. Can check on Monday to verify the name, can't get to my work computer from here. I just downloaded it last week, but one of the guys I work with has been using the trial forever, apparently you can just set your system date to some past date to keep it going indefinitely. (not that I condone this behavior of course :colbert:

)

# ? Mar 13, 2010 21:10

yatagan: Aug 31, 2009; by Ozma

OK, so I have a solution to an academic problem, but I'm wondering how I should really be doing this query (or if I somehow have it as good as it gets). Basically, I need to select the rows with a maximal record count for a specified value.

code:

SELECT 
  USERNAME, 
  COUNT(*) AS TOTAL 
FROM RENTAL 
WHERE RENTAL_DATE BETWEEN p_start_date AND p_end_date
HAVING COUNT(*) IN 
  (SELECT 
     MAX(COUNT(*)) 
   FROM RENTAL 
   WHERE RENTAL_DATE BETWEEN p_start_date AND p_end_date 
   GROUP BY USERNAME)
GROUP BY USERNAME ORDER BY COUNT(*) DESC;

# ? Mar 14, 2010 18:49

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

yatagan posted:

OK, so I have a solution to an academic problem, but I'm wondering how I should really be doing this query (or if I somehow have it as good as it gets). Basically, I need to select the rows with a maximal record count for a specified value.
code:
SELECT 
  USERNAME, 
  COUNT(*) AS TOTAL 
FROM RENTAL 
WHERE RENTAL_DATE BETWEEN p_start_date AND p_end_date
HAVING COUNT(*) IN 
  (SELECT 
     MAX(COUNT(*)) 
   FROM RENTAL 
   WHERE RENTAL_DATE BETWEEN p_start_date AND p_end_date 
   GROUP BY USERNAME)
GROUP BY USERNAME ORDER BY COUNT(*) DESC;

This query confuses me for two reasons: 1) The HAVING COUNT IN () could surely be HAVING COUNT = (), since the subquery is selecting a scalar? 2) The ORDER BY COUNT(*) at the end seems redundant, since only one value is possible for COUNT(*).

Would the following not do the same thing?

code:

SELECT
  USERNAME,
  COUNT(*) AS TOTAL
FROM RENTAL
WHERE RENTAL_DATE BETWEEN p_start_date AND p_end_date
GROUP BY USERNAME
ORDER BY TOTAL DESC
LIMIT 1

(fix as necessary for appropriate syntax for your RDBMS)

# ? Mar 14, 2010 22:15

yatagan: Aug 31, 2009; by Ozma

Hammerite posted:

This query confuses me for two reasons: 1) The HAVING COUNT IN () could surely be HAVING COUNT = (), since the subquery is selecting a scalar? 2) The ORDER BY COUNT(*) at the end seems redundant, since only one value is possible for COUNT(*).

Would the following not do the same thing?
code:
SELECT
  USERNAME,
  COUNT(*) AS TOTAL
FROM RENTAL
WHERE RENTAL_DATE BETWEEN p_start_date AND p_end_date
GROUP BY USERNAME
ORDER BY TOTAL DESC
LIMIT 1

You're right on the IN/= and the ORDER BY, those were left in from dicking around with it earlier.

Your query doesn't quite do the same thing though - there can be multiple USERNAME entries tied for the top count, plus I'm using Oracle so the LIMIT doesn't work.

# ? Mar 15, 2010 15:37

Adbot: ADBOT LOVES YOU

# ? May 31, 2024 14:53

var1ety: Jul 26, 2004

yatagan posted:

You're right on the IN/= and the ORDER BY, those were left in from dicking around with it earlier.

Your query doesn't quite do the same thing though - there can be multiple USERNAME entries tied for the top count, plus I'm using Oracle so the LIMIT doesn't work.

If you are on 10g you should solve TOP-N problems using the analytic functions RANK, DENSE_RANK, and ROW_NUMBER.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i81407

# ? Mar 15, 2010 16:18

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »