|
I'm not sure I fully understand, but you're saying you haven't put an index on the view because data isn't unique? Can't you just create a non-unique index on EmployeeNumber? This ought to bring performance benefits on your joins as the index will be well-ordered. I'm just speculating. I never really use indexes because the database tables I work with aren't really mine to change, but this is how I understand it.
|
# ? Feb 23, 2011 10:38 |
|
|
# ? Jun 8, 2024 06:55 |
|
Nurbs posted:I need help understanding a particularly long running query Yea just put a normal index on the field your joining by on both tables and it should help immensely.
|
# ? Feb 23, 2011 20:06 |
|
Sprawl posted:Yea just put a normal index on the field your joining by on both tables and it should help immensely. This did it. I was kind of avoiding it because it's a table that's going to get a lot of imports, but just means I'll have to figure how to make SSIS only insert rows that don't yet exist.
|
# ? Feb 24, 2011 04:14 |
|
Hey guys. I have two tables, Invoice, and InvoiceLine. They are linked by InvoiceID. First, I linked the two because I wanted to find out how many items (a column in invoiceline), were sold to each country (billingcountry in Invoice) as follows: SELECT DISTINCT invoice.BillingCountry, count( invoiceline.item ) AS 'Quantity Ordered' FROM invoice LEFT OUTER JOIN invoiceline ON invoice.invoiceid = invoiceline.invoiceid GROUP BY invoice.billingcountry However, Now, I have a THIRD table, Countries, that has no primary key. It's just one column full of countries. I want to list all the countries from Invoice, with their corresponding number of items sold from InvoiceLine, but ALSO the countries from Countries that didn't have any items sold to them, with a corresponding 0 next to each country. How can I do this, if Countries has nothing in common with the other two tables? Any help would be appreciated!
|
# ? Feb 24, 2011 04:39 |
|
Are the names in invoice.BillingCountry the same as in Countries?
|
# ? Feb 24, 2011 06:02 |
|
epswing posted:Are the names in invoice.BillingCountry the same as in Countries? Countries has more countries than BillingCountry, but also includes all the countries in BillingCountry. I really want it to list the total items for all the countries that did get items (all the countries in BillingCountry), then show 0 for the rest of the countries listed in Countries. Does that make any sense?
|
# ? Feb 24, 2011 06:07 |
|
code:
Edit: vvv I...I just did your homework for you didn't I... epswing fucked around with this message at 06:59 on Feb 24, 2011 |
# ? Feb 24, 2011 06:11 |
|
epswing posted:
Oh man. That's PERFECT. Thank you so much!!
|
# ? Feb 24, 2011 06:33 |
|
Seems like you should have a primary key on table countries, something like a column containing two-letter country codes or something.
|
# ? Feb 24, 2011 16:37 |
|
Hey look, a SQL help thread. My SQL experience is pretty minimal. I know how to attach, detach, backup, restore and run fairly simple queries, but that's about it. The situation I'm currently faced with is that some data was deleted from a particular table, but I have another copy of the same database with the data I need. I need to figure out how to get the missing data from the restored database and stuff it back where it belongs in the production database. Here's where I am now, with the help of KennyTheFish from a previous thread: KennyTheFish posted:You want to detatch the DB from the restore server and attach it to the production. I'm finally getting a chance to try this, but I'm still having a little trouble. I've restored a copy of the current production database on my test server so that I don't gently caress anything up while testing these statements. So, I've got: Database name: production Table name: appt_log Database name: restored Table name: appt_log The restored database has the data I need to dump into the production database. Both databases are now attached to the same SQL server (internal test server at the moment). Do I have to define every single column that I want to insert, or can I use the * wildcard? I've tried a few variations of the following: INSERT INTO production.dbo.appt_log (*) SELECT * IN restored.dbo.appt_log FROM restored.dbo.appt_log WHERE notes IS NOT NULL ...but when I parse the query to check the syntax, it gives me errors. I'm not even sure if I'm on the right track, since all of the examples I can find are for inserting data from two completely different tables rather than simply restore data from an earlier copy of the exact same table. Microsoft SQL Server 2008, if it matters.
|
# ? Feb 28, 2011 18:16 |
|
To insert without specifying columns just leave off the parentheses, i.e.:code:
EDIT: Sorry, I didn't read your query very carefully. All you need on that line is SELECT * Jethro fucked around with this message at 18:36 on Feb 28, 2011 |
# ? Feb 28, 2011 18:30 |
|
Jethro posted:To insert without specifying columns just leave off the parentheses, i.e.: I am absolutely sure that the table structures are identical. When I try your statement, I get "Incorrect syntax near the keyboard 'IN'", referring to line 2.
|
# ? Feb 28, 2011 18:34 |
|
brc64 posted:I am absolutely sure that the table structures are identical. I figured it out, or at least a variation that worked. I couldn't get it to work with wildcards, so I tried manually typing out every column name (which was a pain in the rear end since there are like 30 columns in this drat table), but I also had to add: SET IDENTITY_INSERT production.dbo.appt_log ON ...to the beginning of the statement. But that seems to have done the trick. So now I just have to copy the restored database to the production server, restore it to a new db, then run through this process again there. It's been a wild ride.
|
# ? Feb 28, 2011 21:10 |
|
I am doing query in orracle that utilizes 3 tables. I am attempting to minus two subquerries each will return ~ 8000 rows. If I do not limit the one of these sub queries with the "where rownum <= 10000" it will not run. However if I do limit the results of one of the subqueries I will get the correct result. Any ideas on why this is? code:
DholmbladRU fucked around with this message at 04:49 on Mar 1, 2011 |
# ? Mar 1, 2011 04:46 |
|
DholmbladRU posted:I am doing query in orracle that utilizes 3 tables. I am attempting to minus two subquerries each will return ~ 8000 rows. If I do not limit the one of these sub queries with the "where rownum <= 10000" it will not run. However if I do limit the results of one of the subqueries I will get the correct result. What exactly are you trying to select here? Whatever it is I feel very confident when I say that this is not the right way to do it (all existing customer/part rows subtracted from all possible customer/part combinations ). When you say the query "will not run" without that rownum limitation I assume you mean that it times out, or you give up on waiting for it to return. That CROSS JOIN is creating a result set containing a number of rows equal to the number of distinct custids ^ the number of distinct part numbers, which is probably a very big number. The double negatives and subtraction from a universal set are hurting my head a bit, but it looks like you're trying to select all customers who have ordered every part in the sodetail table? You shouldn't need to use MINUS or CROSS JOIN to do this. You want something like code:
You should also be careful about your assumption that every part has at least one row in sodetail.
|
# ? Mar 1, 2011 06:39 |
|
this portion will not run without the rowlimit. Here is the assignment(that I already turned in) Tables------ Customers SalesOrders SODetail Get a list of the Customer ID and Customer Name for all customers that have purchased all products (partno). Do not hard code your answer, i.e., your query should work with any data. the first statements before the minus return every possible custID and part combo. Where as the second returns every distinct custid/part combo present in the sales table. The reason I am doing this is in our course we studies relational algebra, and my professor showed us a way to do divide in oracle by doing 5(complected) steps... code:
DholmbladRU fucked around with this message at 07:06 on Mar 1, 2011 |
# ? Mar 1, 2011 07:01 |
|
There is never a need (that I can think of at least) to limit your results by selecting all possible combinations and then removing things from that set. If your professor is teaching you to do this as good practice in writing a query (as opposed to teaching you some sort of abstract set theory concept) then he is teaching you wrong. If you wanted to make a list of students in your class would you first make list describing every student at your college enrolled in every course that your college offers and then cross off all the ones that don't apply? Because that's what your professor's method does. Goat Bastard fucked around with this message at 07:55 on Mar 1, 2011 |
# ? Mar 1, 2011 07:50 |
|
Goat Bastard posted:There is never a need (that I can think of at least) to limit your results by selecting all possible combinations and then removing things from that set. If your professor is teaching you to do this as good practice in writing a query (as opposed to teaching you some sort of abstract set theory concept) then he is teaching you wrong. Yeah it doesn't really make any sense to do it the way I posted. I guess he was just show how to turn relational algebra into sql.
|
# ? Mar 1, 2011 08:05 |
|
Yea it's been a while since I learned any set theory but it looks like the sort of thing that might make sense in a pure mathematical sense. When dealing with SQL though the answer to "When would you be most likely to see a cartesian join" is "When someone has made a mistake" There are legitimate uses for them, but unless you're 100% sure you should look twice whenever you see one.
|
# ? Mar 1, 2011 08:22 |
|
DholmbladRU posted:[SQL/Relational Algebra Stuff] Perhaps something like all customers where (the set of (the set of all items) minus (the set of items for this customer) ) is the empty set. code:
|
# ? Mar 1, 2011 18:10 |
|
I have two tables and I need to left join. The first table has locations, like so: Location_ID Location 1 Georgia 2 Virginia 3 New York The second has contact information for locations, such as: Location_ID Name 1 Bob 1 Tom 2 Dave 2 Mike What's the best way to grab all of the locations with the first matching contact? Like below: Location Name Georgia Bob Virginia Dave New York NULL I'm using sql server and there's no aggregate first() function Schnozzberry fucked around with this message at 22:36 on Mar 1, 2011 |
# ? Mar 1, 2011 22:33 |
|
Schnozzberry posted:I have two tables and I need to left join. This is a bad idea, since I can't see any kind of data integrity emerging but couldn't you do: code:
code:
|
# ? Mar 1, 2011 22:58 |
|
Because the list of locations is huge, as well as the list of contacts, so I don't want an output of 50,000 records (rather have 5,000). The min thing would work if the contacts were as simple as I said, unfortunately the contacts table has stuff like first name, last name, phone, etc, so a min would screw up the output and phone wouldn't match name etc. I think that second one will work. What's the point of the order by in the subquery? Schnozzberry fucked around with this message at 23:14 on Mar 1, 2011 |
# ? Mar 1, 2011 23:07 |
|
Schnozzberry posted:Because the list of locations is huge, as well as the list of contacts, so I don't want an output of 50,000 records (rather have 5,000). The TOP will pick the first record using the ORDER BY criteria. So if it's ordered by Location_ID ASC, it'll pick the lowest ID. If it's ordered by Location_ID DESC then it'll pick the highest ID; basically it'll always pick row 0 based on how the data is sorted (not sure how it'll respond to cases where there are no location_id matches, probably NULL). You can use other criteria to get the record you want in the subquery (e.g. a boolean flag 'MainContact' per location) but if you had that you wouldn't need to do this in the first place. EDIT-This is a bad idea mainly because it seems like you can't guarantee that the contact you'll get will be the 'right' one with any regularity; you'll get 'a' contact but never be sure if it's 'the' contact. The better bet would obviously have a lookup table in between, but it sounds like you can't really enforce this logic on the existing application. Scaramouche fucked around with this message at 00:18 on Mar 2, 2011 |
# ? Mar 2, 2011 00:08 |
|
Jethro posted:So, if I understand things correctly, your original query returns all customers that are not in (the set of customers that exist in the (set of (the set of all possible customer-item combinations) minus (the set of actual customer-item combinations) ) ). The query was to return all customers who have bought all products. I was just instructed to use some backwards way that made no sense.
|
# ? Mar 2, 2011 06:42 |
|
I'm working with an existing DB and existing program (inventory) and I need to create a trigger that will add a record to another table with selected information from that table. Currently to get a quantity count I have to access a view that is stored in the DB. Individually each of these commands would do what I want but I can't seem to get Oracle to play nice.code:
|
# ? Mar 2, 2011 18:15 |
|
DholmbladRU posted:The query was to return all customers who have bought all products. I was just instructed to use some backwards way that made no sense. This should work in that case, temp table just for sample data code:
|
# ? Mar 2, 2011 18:46 |
|
flare posted:I'm working with an existing DB and existing program (inventory) and I need to create a trigger that will add a record to another table with selected information from that table. Currently to get a quantity count I have to access a view that is stored in the DB. Individually each of these commands would do what I want but I can't seem to get Oracle to play nice. What problem are you having? Is Oracle issuing an "ORA-04091: table XXXX is mutating, trigger/function may not see it" when the trigger fires?
|
# ? Mar 2, 2011 22:18 |
|
into #cust Will create a temporary table correct? I get a "ORA-00911: invalid character" when I run that.
|
# ? Mar 2, 2011 23:24 |
|
Schnozzberry posted:I have two tables and I need to left join. code:
|
# ? Mar 2, 2011 23:39 |
|
DholmbladRU posted:into #cust Not in Oracle. For the purporses of a single query you can do the same thing using the WITH clause. code:
|
# ? Mar 3, 2011 11:56 |
|
var1ety posted:What problem are you having? Is Oracle issuing an "ORA-04091: table XXXX is mutating, trigger/function may not see it" when the trigger fires? It wasn't running and I couldn't find the debug information. I finally found it but yeah, now I'm getting mutating errors. Is there a resolution to this? As the code shows, I need to create a table entry that just shows: ISBN, UID, Used_Qty, New_Qty whenever that table is updated. I WOULD just add this to my code but it isn't my codebase or something I even have the source code for so I'm trying to hack together an integration. There also are no timestamps saved in the DB so I don't know when a record was added. bone app the teeth fucked around with this message at 15:47 on Mar 3, 2011 |
# ? Mar 3, 2011 15:34 |
|
flare posted:It wasn't running and I couldn't find the debug information. I finally found it but yeah, now I'm getting mutating errors. Is there a resolution to this? Here are links to both the Oracle documentation and a Tom Kyte article talking about mutating tables - Oracle Database Application Developer's Guide - Fundamentals Tom Kyte - Avoiding Mutating Tables Basically, you can't SELECT from a table you are modifying using a FOR EACH ROW trigger because the trigger will see an inconsistent set of data. Oracle documentation advises that "you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable", and Kyte's article shows actual implementation of this concept using a package variable and storing updated rowids for use in a statement level trigger at the end of execution. If you Google around you'll also find references to using "pragma autonomous_transaction" to override the error message, but the error message is there for your protection and shouldn't be ignored.
|
# ? Mar 3, 2011 20:09 |
|
var1ety posted:Here are links to both the Oracle documentation and a Tom Kyte article talking about mutating tables - Thanks for this tip. I went through and looked at one of the views I was using and it was running a select on the table I was triggering. Doh. Thanks for helping.
|
# ? Mar 3, 2011 22:26 |
|
Ok I got that trigger doing what I wanted it to do. Now I got my SQL query working partially:code:
code:
This returns: code:
I'm still a SQL novice (thanks Ruby on Rails for doing all of it for me...)
|
# ? Mar 4, 2011 15:18 |
|
Hammerite posted:What's the reasoning behind that? AFAIK there are only a few special usage scenarios for which MyISAM is more appropriate than InnoDB. For something like what you are describing I really don't know why you would use MyISAM. Coming back to this four months later after I tried again, it's mainly because all of our systems, such as backup, are based around MyISAM.
|
# ? Mar 4, 2011 21:37 |
|
With Percona's XtraBackup providing full support for both InnoDB and MyISAM, there's really no excuse to have a MyISAM-specific backup process in place. To be frank, this sounds more like a "we're afraid of change" excuse than anything else. Your code shouldn't give a drat about the underlying storage engine (and unless you're using MySQLisms, your code shouldn't even care that it's running on MySQL to begin with...) Really, if "the backups will break" is all they've got, it's time for either some hardcore re-education or time to find another job. InnoDB has been superior to MyISAM in almost every way since 5.0, and with the sole exception of full text search, there is zero reason to keep it in use. McGlockenshire fucked around with this message at 21:46 on Mar 4, 2011 |
# ? Mar 4, 2011 21:41 |
|
McGlockenshire posted:With Percona's XtraBackup providing full support for both InnoDB and MyISAM, there's really no excuse to have a MyISAM-specific backup process in place. Institutional inertia, yes. And lack of resources to make big changes. The one sysadmin is terribly overworked, so compiling a version of PHP from the last five years or rejiggering the entire data management system are very much backburner. Someday, they'll move to a transactional DB, but til then we just hope poo poo don't break. vv
|
# ? Mar 4, 2011 21:59 |
|
flare posted:
Use it as an inline view: code:
Also if the goal is to get the active price rather than the n most recent ones then you can replace the ORDER BY with AND pctx_itst_item_ittp_new_used_f = 'N' and ditch the ROWNUM.
|
# ? Mar 4, 2011 23:00 |
|
|
# ? Jun 8, 2024 06:55 |
|
Goat Bastard posted:Use it as an inline view: This would work except the TIDE CODE (the global ID) needs to be for each record in the GB_TRANSACITON table (right now its selecting a single ID). I have the ROWNUM clause because there are 2 records per ID that contain a new and used price. Essentially trying to end up with this result on the GB_TRANSACTION table that looks like: ISBN13, NEW_PRICE, USED_PRICE, NEW_QTY, USED_QTY bone app the teeth fucked around with this message at 17:43 on Mar 6, 2011 |
# ? Mar 6, 2011 17:17 |