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 »

Flamadiddle: May 9, 2004

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

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 06:58

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!

Nurbs posted:

I need help understanding a particularly long running query

I'm importing data from another database into a table
code:
ID | EmployeeNumber | Field_Name | Value
Yeah..someone loved their entity attribute value stuff.

I have a View on this table like so
code:
SELECT EmployeeNumber, Value FROM StupidTable WHERE Field_Name='SomeValue'
StupidTable has about 186,000 records in it and this query returns 1800. There are no indexes on this view because the data is not distinct or otherwise constrained.

Whenever I try a LEFT JOIN on this view on EmployeeNumber the query time is ridiculous. If I limit the results to the top 1000 I'm waiting for nearly a minute. What I don't get is why, and I have no idea how to improve the performance - it seems as simple as can be. I should add that there are a LOT of null values here

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

Nurbs: Aug 31, 2001; Three fries short of a happy meal...Whacko!

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

guch: Mar 10, 2003; fuck quake. lets do art.

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

epswing: Nov 4, 2003; Soiled Meat

Are the names in invoice.BillingCountry the same as in Countries?

# ? Feb 24, 2011 06:02

guch: Mar 10, 2003; fuck quake. lets do art.

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

epswing: Nov 4, 2003; Soiled Meat

code:

SELECT c.*, COUNT(il.Quantity) AS quantity
FROM country c
LEFT JOIN invoice i ON i.BillingCountry = c.CountryName
LEFT JOIN invoiceline il ON il.InvoiceId = i.InvoiceId
GROUP BY c.CountryName

Seems to do what you're asking.

Edit: vvv I...I just did your homework for you didn't I... :smith:

epswing fucked around with this message at 06:59 on Feb 24, 2011

# ? Feb 24, 2011 06:11

guch: Mar 10, 2003; fuck quake. lets do art.

epswing posted:

code:

SELECT c.*, COUNT(il.Quantity) AS quantity
FROM country c
LEFT JOIN invoice i ON i.BillingCountry = c.CountryName
LEFT JOIN invoiceline il ON il.InvoiceId = i.InvoiceId
GROUP BY c.CountryName

Seems to do what you're asking.

Oh man. That's PERFECT. Thank you so much!!

# ? Feb 24, 2011 06:33

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

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

brc64: Mar 21, 2008; I wear my sunglasses at night.

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.

your SQL is then

INsert into <DBtable> (<column1>,<column2>,<...>)
SELECt <column1 in restored table>, <coulumn2 in testored table>, <...>
FROM <reatached table>
WHERE <onditions that identify the records you want>

Run the query with the insert commented out first to make sure you haev the correct data

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

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

To insert without specifying columns just leave off the parentheses, i.e.:

code:

INSERT INTO production.dbo.appt_log
SELECT *
FROM restored.dbo.appt_log
WHERE notes IS NOT NULL

But you should never actually do this and you should always specify columns. If you're 100% sure that absolutely no changes have been made to the table structure between when the backup was made and now then it will work fine, but if anything added, removed, or re-arranged columns then it either won't work or it will put the data into the wrong columns.

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

brc64: Mar 21, 2008; I wear my sunglasses at night.

Jethro posted:

To insert without specifying columns just leave off the parentheses, i.e.:
code:
INSERT INTO production.dbo.appt_log
SELECT * IN restored.dbo.appt_log
FROM restored.dbo.appt_log
WHERE notes IS NOT NULL
But you should never actually do this and you should always specify columns. If you're 100% sure that absolutely no changes have been made to the table structure between when the backup was made and now then it will work fine, but if anything added, removed, or re-arranged columns then it either won't work or it will put the data into the wrong columns.

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: Mar 21, 2008; I wear my sunglasses at night.

brc64 posted:

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.

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

DholmbladRU: May 4, 2006

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:

select custid, name  from 
((select custid from abc.salesorders
minus
select distinct custid from (
	select * from 
		(select distinct custid from ABC.SalesOrders)
			cross join
		(select distinct partno from ABC.soDetail) where rownum <= 10000
		minus
		select distinct custid, partno from
		(ABC.SalesOrders
			natural inner join
		ABC.SODetail)
		)) T1
		inner join 
		ABC.Customers on ABC.Customers.ID=T1.custid);

DholmbladRU fucked around with this message at 04:49 on Mar 1, 2011

# ? Mar 1, 2011 04:46

Goat Bastard: Oct 20, 2004

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.

Any ideas on why this is?

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 :what:

). 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:

SELECT so.custid
      ,c.name
FROM   abc.salesorders so
JOIN   abc.customers   c
       ON (c.id = so.custid)
WHERE  (so.custid
       ,(SELECT count(DISTINCT partno) FROM sodetail) --the total number of distinct parts that exist
       ) IN (SELECT custid
                   ,count(DISTINCT partno) --the total number of distinct parts per customer
             FROM               abc.salesorders
             NATURAL INNER JOIN abc.sodetail
             GROUP BY custid
            )
;

Note this is untested, may not return the info you are actually looking for, and is not necessarily even close to optimal. There may be a better way involving analytic functions or something (there usually is...).

You should also be careful about your assumption that every part has at least one row in sodetail.

# ? Mar 1, 2011 06:39

DholmbladRU: May 4, 2006

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:


		(select distinct custid from ABC.SalesOrders)
			cross join
		(select distinct partno from ABC.soDetail) where rownum <= 10000


		minus

		select distinct custid, partno from
		(ABC.SalesOrders
			natural inner join
		ABC.SODetail)

If I add select distinct to your query it returns the same as what I originally posted.

DholmbladRU fucked around with this message at 07:06 on Mar 1, 2011

# ? Mar 1, 2011 07:01

Goat Bastard: Oct 20, 2004

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

DholmbladRU: May 4, 2006

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.

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.

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

Goat Bastard: Oct 20, 2004

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

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

DholmbladRU posted:

[SQL/Relational Algebra Stuff]

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) ) ).

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:

select custid, name
  from ABC.Customers c
 where not exists (select distinct partno from ABC.soDetail
                   minus
                   select distinct partno from ABC.soDetail d
                    where d.custid = c.custid)

# ? Mar 1, 2011 18:10

Schnozzberry: Apr 13, 2005

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

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

Schnozzberry posted:

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

This is a bad idea, since I can't see any kind of data integrity emerging but couldn't you do:

code:

SELECT t1.Location,MAX(t2.Location_ID) FROM Table1 t1 
LEFT OUTER JOIN Table2 t2 ON t1.Location_ID=t2.Location_ID

Which theoretically would get you the highest Location_ID; you could wrap that in another select to get the names, or could do MIN to get the lowest id. But it seems a pretty arbitrary way of getting a contact. You could also use subquery (why???):

code:

SELECT t1.Location,(Select TOP 1 Name from Table2 where t1.Location_ID=Location_ID order by Location_ID ASC)
FROM Table1 t1

There's probably better ways but I'm having trouble wrapping my head around why you'd want to do it this way.

# ? Mar 1, 2011 22:58

Schnozzberry: Apr 13, 2005

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

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

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 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?

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

DholmbladRU: May 4, 2006

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) ) ).

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:
select custid, name
  from ABC.Customers c
 where not exists (select distinct partno from ABC.soDetail
                   minus
                   select distinct partno from ABC.soDetail d
                    where d.custid = c.custid)

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

bone app the teeth: May 14, 2008

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:

CREATE OR REPLACE TRIGGER gb_qty_change
  AFTER UPDATE OR INSERT OR DELETE ON f_item_store
  FOR EACH ROW
DECLARE
  v_qty V_AD_ON_HAND%rowtype;
  v_isbn TD_ITEM_DESCRIPTION.TD_IDENTIFIER%type;
BEGIN
    delete from gb_transaction where gb_tide = :new.ITST_ITEM_TIDE_CODE;
    select TD_IDENTIFIER INTO v_isbn from TD_ITEM_DESCRIPTION where TD_TIDE = :new.ITST_ITEM_TIDE_CODE;
    select * INTO v_qty from V_AD_ON_HAND where ITST_ITEM_TIDE_CODE = :new.ITST_ITEM_TIDE_CODE;
    insert into gb_transaction(gb_tide, gb_isbn, gb_used_on_hand, gb_new_on_hand)
      values(:new.ITST_ITEM_TIDE_CODE, v_isbn, v_qty.USED_ON_HAND, v_qty.NEW_ON_HAND);
END;
/

Any suggestions?

# ? Mar 2, 2011 18:15

Aredna: Mar 17, 2007; Nap Ghost

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:

select 1 as custid, 'a' as name, 1 as partno
into #cust
union all select 1,'a',2
union all select 1,'a',3
union all select 2,'b',2
union all select 2,'b',3
union all select 3,'c',2
union all select 4,'d',2
union all select 5,'e',1
union all select 5,'e',2
union all select 5,'e',3

select custid, name, count(distinct partno) as numparts
from #cust
group by custid, name
having count(distinct partno) in (select count(distinct partno) from #cust)

# ? Mar 2, 2011 18:46

var1ety: Jul 26, 2004

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.
code:
CREATE OR REPLACE TRIGGER gb_qty_change
  AFTER UPDATE OR INSERT OR DELETE ON f_item_store
  FOR EACH ROW
DECLARE
  v_qty V_AD_ON_HAND%rowtype;
  v_isbn TD_ITEM_DESCRIPTION.TD_IDENTIFIER%type;
BEGIN
    delete from gb_transaction where gb_tide = :new.ITST_ITEM_TIDE_CODE;
    select TD_IDENTIFIER INTO v_isbn from TD_ITEM_DESCRIPTION where TD_TIDE = :new.ITST_ITEM_TIDE_CODE;
    select * INTO v_qty from V_AD_ON_HAND where ITST_ITEM_TIDE_CODE = :new.ITST_ITEM_TIDE_CODE;
    insert into gb_transaction(gb_tide, gb_isbn, gb_used_on_hand, gb_new_on_hand)
      values(:new.ITST_ITEM_TIDE_CODE, v_isbn, v_qty.USED_ON_HAND, v_qty.NEW_ON_HAND);
END;
/
Any suggestions?

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

DholmbladRU: May 4, 2006

into #cust

Will create a temporary table correct? I get a "ORA-00911: invalid character" when I run that.

# ? Mar 2, 2011 23:24

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Schnozzberry posted:

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

The answer to this sort of question is always analytical functions.

code:

select Location, Name
  from Loc_tbl
       left outer join (
select Location_ID
      ,Name
      ,row_number() over (partition by Location_ID
                          order by 1 --or whatever column you want to use to determine "first"
                          ) loc_rnk
  from Contact_tbl) c on c.Location_ID = l.Location_ID and loc_rnk = 1

# ? Mar 2, 2011 23:39

Goat Bastard: Oct 20, 2004

DholmbladRU posted:

into #cust

Will create a temporary table correct? I get a "ORA-00911: invalid character" when I run that.

Not in Oracle. For the purporses of a single query you can do the same thing using the WITH clause.

code:

with cust as (
  select 1 as custid, 'a' as name, 1 as partno from dual
  union all select 1,'a',2 from dual
  union all select 1,'a',3 from dual
  union all select 2,'b',2 from dual
  union all select 2,'b',3 from dual
  union all select 3,'c',2 from dual
  union all select 4,'d',2 from dual
  union all select 5,'e',1 from dual
  union all select 5,'e',2 from dual
  union all select 5,'e',3 from dual
)
select custid, name, count(distinct partno) as numparts
from cust
group by custid, name
having count(distinct partno) in (select count(distinct partno) from cust);

# ? Mar 3, 2011 11:56

bone app the teeth: May 14, 2008

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

var1ety: Jul 26, 2004

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?

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.

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

bone app the teeth: May 14, 2008

var1ety posted:

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.

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

bone app the teeth: May 14, 2008

Ok I got that trigger doing what I wanted it to do. Now I got my SQL query working partially:

code:

    select ISBN13, USED, NEW from 
      (SELECT td_code TIDE, td_identifier isbn13, used_on_hand used, new_on_hand new
        FROM f_title_description 
        INNER JOIN v_ad_on_hand ON td_code = itst_item_tide_code)
    inner join gb_transactions on TIDE = gb_tide

This returns only the items in the gb_transactions table (my logging table) with the proper inventory counts. Now I want to add price to this BUT the way the prices are stored in the DB is a little odd. There is one record per price and old records are saved. Each record is: ID, FLAG (new or used), and PRICE. The best way to query this I could come up with was:

code:

    select FLAG, PRICE from 
      ( SELECT pctx_itst_item_ittp_new_used_f FLAG, pctx_new_price PRICE
          FROM f_price_change_txn 
    	  WHERE pctx_itst_item_tide_code = '5443832'
          ORDER BY pctx_date_created DESC )
      WHERE rownum <= 2

(Using Oracle 9i so there is no LIMIT.)
This returns:

code:

    F      PRICE
    - ----------
    N     129.65
    U      97.25

I can't seem to figure out how to get that in my previous query so it can all be done in a single query.

I'm still a SQL novice

(thanks Ruby on Rails for doing all of it for me...)

# ? Mar 4, 2011 15:18

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

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

McGlockenshire: Dec 16, 2005; GOLLOCKS!

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

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

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.

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.

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. v :shobon:

# ? Mar 4, 2011 21:59

Goat Bastard: Oct 20, 2004

flare posted:

I can't seem to figure out how to get that in my previous query so it can all be done in a single query.

I'm still a SQL novice (thanks Ruby on Rails for doing all of it for me...)

Use it as an inline view:

code:

    select ISBN13, USED, NEW from 
      (SELECT td_code TIDE, td_identifier isbn13, used_on_hand used, new_on_hand new
        FROM f_title_description 
        INNER JOIN v_ad_on_hand ON td_code = itst_item_tide_code)
    inner join gb_transactions on TIDE = gb_tide

    inner join (
        select FLAG, PRICE from 
          ( SELECT pctx_itst_item_ittp_new_used_f FLAG, pctx_new_price PRICE
              FROM f_price_change_txn 
              WHERE pctx_itst_item_tide_code = '5443832'
              ORDER BY pctx_date_created DESC )
          WHERE rownum <= 2
    ) as price on (whatever = price.whatever)

You'll probably need to select the id column along with price and flag and then use it to join. If it's possible for some results to have no price then change the INNER JOIN to a LEFT OUTER JOIN

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

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 06:58

bone app the teeth: May 14, 2008

Goat Bastard posted:

Use it as an inline view:
code:
    select ISBN13, USED, NEW from 
      (SELECT td_code TIDE, td_identifier isbn13, used_on_hand used, new_on_hand new
        FROM f_title_description 
        INNER JOIN v_ad_on_hand ON td_code = itst_item_tide_code)
    inner join gb_transactions on TIDE = gb_tide

    inner join (
        select FLAG, PRICE from 
          ( SELECT pctx_itst_item_ittp_new_used_f FLAG, pctx_new_price PRICE
              FROM f_price_change_txn 
              WHERE pctx_itst_item_tide_code = '5443832'
              ORDER BY pctx_date_created DESC )
          WHERE rownum <= 2
    ) as price on (whatever = price.whatever)
You'll probably need to select the id column along with price and flag and then use it to join. If it's possible for some results to have no price then change the INNER JOIN to a LEFT OUTER JOIN

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.

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

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

«‹›303 »