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 »

huge sesh: Jun 9, 2008

Where is a good place to start reading about SQL? I'm skilled in general as a programmer but I have no experience with databases (making me functionally retarded in the job market).

# ? Aug 26, 2009 00:43

Adbot: ADBOT LOVES YOU

# ? May 16, 2024 00:51

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

huge sesh posted:

Where is a good place to start reading about SQL? I'm skilled in general as a programmer but I have no experience with databases (making me functionally retarded in the job market).

http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905/

I'm only halfway kidding, I looked at the preview and it looks like a decent intro to the relational model and SQL. Plus you could review it for us!

# ? Aug 26, 2009 01:27

Kekekela: Oct 28, 2004

quote:

Where is a good place to start reading about SQL? I'm skilled in general as a programmer but I have no experience with databases (making me functionally retarded in the job market).

I'd google each of these statements along with TSQL and Tutorial or something along those lines if you want free internet stuff:
SELECT, INSERT, UPDATE, and DELETE (ie "SELECT TSQL", "INSERT TSQL TUTORIAL", etc)

Those give you a nice foundation for manipulating the data, then you can get into the commands to actually build Tables, etc if so inclined. (ALTER, CREATE, etc)

Markoff Chaney posted:

http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905/

I'm only halfway kidding, I looked at the preview and it looks like a decent intro to the relational model and SQL. Plus you could review it for us!

Jesus, that's terrifying.

# ? Aug 26, 2009 01:33

Meganiuma: Aug 26, 2003

Markoff Chaney posted:

http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905/

I'm only halfway kidding, I looked at the preview and it looks like a decent intro to the relational model and SQL. Plus you could review it for us!

Actually that book has gotten a few recommendations from the Recommend me some database books thread (which huge sesh should probably take a look at.) I've been meaning to pick it up myself.

# ? Aug 26, 2009 04:01

Sub Par: Jul 18, 2001; Dinosaur Gum

huge sesh posted:

Where is a good place to start reading about SQL? I'm skilled in general as a programmer but I have no experience with databases (making me functionally retarded in the job market).

I've found http://techonthenet.com/ to be a good desktop-reference type place. The have a basic explanation of the syntax and examples, and if you end up somewhere that works with Oracle PL/SQL, this is a great place to find all the oddities of their implementation of SQL.

# ? Aug 26, 2009 14:41

MikeRabsitch: Aug 23, 2004; Show us what you got, what you got

Before I started a job involving 80% SQL I went to http://sqlcourse.com/ and http://sqlcourse2.com/ which did a decent job. Plus they have a test database you can play with.

# ? Aug 26, 2009 15:14

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

It's hard to see because of how far I scaled the image down, but what I have here is a list of part numbers. Each part has one of various reasons why it's been scrapped. This table keeps track of how many of each part have been scrapped for each reason.

How do I manage to do something like this?

I'm starting with a table of Scrap and there is a row for each item that was scrapped. It has infor such as part number (varchar), quantity (int), and reason scrapped (varchar)

I would like each part number on it's own row, with each reason listed in the columns that follow it. We might have a part where 5 parts were contaminated, but on 10 parts the thickness was wrong.

Here's what I am getting now:

Basically I want to have a Scrap_Reason heading, and a count of each reason.

Bob Morales fucked around with this message at 20:38 on Aug 31, 2009

# ? Aug 31, 2009 20:34

MikeRabsitch: Aug 23, 2004; Show us what you got, what you got

This should give you each unique part number on it's own row unless there's more than one scrap reason, then it'll give you the quantity and extended cost sum for each scrap reason:

code:

SELECT part_no, sum(quantity), scrap_reason, sum(extended cost)
FROM your_table
GROUP BY part_no, scrap_reason
ORDER BY part_no, scrap_reason

This should give you a Scrap_Reason and the quantity sum for each reason:

code:

SELECT scrap_reason, sum(quantity)
FROM your_table
GROUP BY scrap_reason
ORDER BY scrap_reason

If none of these are what you want let me know because I'm not sure I understand 100%.

# ? Aug 31, 2009 21:14

Sub Par: Jul 18, 2001; Dinosaur Gum

If I understand you right, you want something like this

code:

select part_no, 
scrap_reason,
count(part_no) instances,
sum(quantity) Quantity, 
sum(extended_cost) Extended_Cost
from yourtable
group by part_no, scrap_reason

This will give you something like this for part 142301 from your snip below:

code:

Part_no	  scrap_reason	        instances     quantity  extended_cost
142301	  Inventory Adjustment	3	      51.491	119.50186
142301	  Startup	        1	      0.808	1.87523
142301	  Flow Meter Problem	1	      153.62	356.5259

# ? Aug 31, 2009 21:23

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

What I was looking for was more like:

code:

Part #                       Scrap                   Cost
          Reason1 Reason2 Reason3 Reason4 Reason5
124        5           0       3       1       0    $25.00

A separate column for each reason, but all on one row per part #

# ? Sep 1, 2009 00:34

ether: May 20, 2001

Bob Morales posted:

What I was looking for was more like:
code:
Part #                       Scrap                   Cost
          Reason1 Reason2 Reason3 Reason4 Reason5
124        5           0       3       1       0    $25.00
A separate column for each reason, but all on one row per part #

If you are using SQLServer 2008 you could use the nifty PIVOT option, from the top of my head something along these lines.

code:

DECLARE @Cols varchar(8000)
DECLARE @SQL varchar(8000)
 
-- Get all column headers
SET @Cols = SUBSTRING((SELECT DISTINCT ',['+Scrap_Reason+']' FROM ScrapDataTable),2,8000)
SET @SQL = 'SELECT Part_No, '+@Cols+', SUM(Extended_Cost) FROM 
  (Select Part_No, Scrap_Reason, Quantity from ScrapDataTable) AS SourceData
PIVOT
  (SUM(Quantity) FOR Part_No IN ('+@Cols+')) AS PivotTable'
EXEC(@sql)
 
GO

# ? Sep 1, 2009 07:43

go play outside Skyler: Nov 7, 2005

Hello all!

I have small question that keeps bothering me.

I've got a table called "favoritables", which stores a bunch of items people can add to their "favorites". Each has a unique id and various other info.

I've also got a "favorites" table, which is just a table storing people's favorites, it has a couple of rows, the important ones being favoritable_id and user_id.

My question is this: How would I, with a single query, fetch all "favoritables" with an additional field which contains the amount of "favorites" attached to a "favoritable" id?

Example: 4 people have "a" in their favorites, and 2 have "b". I want something like this

code:

id   |    name   |    count_favorites
-------------------------------------------
1    | "a"       |  4
2    | "b"       |  2

assuming the data in "favorites goes something like this

code:

id   | favoritable_id  |  user_id
-------------------------------------------
1    | 1               | 1
2    | 1               | 3
3    | 1               | 87
4    | 2               | 1
5    | 2               | 87
6    | 1               | 23

Thank you for your help! I can't get my head around this, and if I don't get any replies soon I'll just fetch all favoritables, and for each of those I'll do a SELECT COUNT(*) FROM favorites WHERE favoritable_id=## which would be horribly unoptimized!

# ? Sep 1, 2009 14:34

Sub Par: Jul 18, 2001; Dinosaur Gum

Edit: ^^ for you - wait use Knightmare's, I forgot the group by.

code:

select favoritables.name, count(favorites.favoritable_id) count_favorites
from favoritables, favorites
where favoritables.favoritable_id = favorites.favoriteable_id

ether posted:

If you are using SQLServer 2008 you could use the nifty PIVOT option, from the top of my head something along these lines.

code:

DECLARE @Cols varchar(8000)
DECLARE @SQL varchar(8000)
 
-- Get all column headers
SET @Cols = SUBSTRING((SELECT DISTINCT ',['+Scrap_Reason+']' FROM ScrapDataTable),2,8000)
SET @SQL = 'SELECT Part_No, '+@Cols+', SUM(Extended_Cost) FROM 
  (Select Part_No, Scrap_Reason, Quantity from ScrapDataTable) AS SourceData
PIVOT
  (SUM(Quantity) FOR Part_No IN ('+@Cols+')) AS PivotTable'
EXEC(@sql)
 
GO

Along these lines, if you're using Oracle 11g you can do the same. I'm not familiar with the syntax, but you should be able to google it pretty easily.

Sub Par fucked around with this message at 15:16 on Sep 1, 2009

# ? Sep 1, 2009 15:10

MikeRabsitch: Aug 23, 2004; Show us what you got, what you got

Sir Davey posted:

Thank you for your help! I can't get my head around this, and if I don't get any replies soon I'll just fetch all favoritables, and for each of those I'll do a SELECT COUNT(*) FROM favorites WHERE favoritable_id=## which would be horribly unoptimized!

Your table names seem very confusing, I'm guessing you want something like this:

code:

SELECT favoritable.name, count(favorite.user_id)
FROM favoritable, favorite
WHERE favoritable.favoritable_id = favorite.favoritable_id
GROUP BY favoritable.name
ORDER BY favoritable.name

I'm guessing the favoritable table has the names/description, so you want to join the favoritable table with the favorites table on their id. You then want to group by that name/description and it should give you a count of all user id's with that as a favorite. If the name/description exists in the favorites table, you don't need to include the favoritable table in that query.

# ? Sep 1, 2009 15:15

go play outside Skyler: Nov 7, 2005

Knightmare posted:

Your table names seem very confusing, I'm guessing you want something like this:

Yeah when I made the first DB design I couldn't come up with anything else. Favoritables are elements that can be added to favorites. I don't know, it seemed logical at the time

Knightmare posted:

code:
SELECT favoritable.name, count(favorite.user_id)
FROM favoritable, favorite
WHERE favoritable.favoritable_id = favorite.favoritable_id
GROUP BY favoritable.name
ORDER BY favoritable.name
I'm guessing the favoritable table has the names/description, so you want to join the favoritable table with the favorites table on their id. You then want to group by that name/description and it should give you a count of all user id's with that as a favorite. If the name/description exists in the favorites table, you don't need to include the favoritable table in that query.

Thanks a bunch, this works wonders! Performance-wise it isn't great but it's much better than what I originally came up with

# ? Sep 1, 2009 15:27

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

I have a SQL database with tables derived from CSV flat files an I need a better way of doing what I am doing.

Here's how it currently works:

- Data from a partner is exported from a database and dropped as a flat file into a directory called mmddyyyy on a sftp server
- Each day, a script pulls any new data files down from the server to a file server with a matching directory structure

Note: These files are technically "daily" files but some days it gets skipped due to database errors on their side and other days the job is rerun to backfill the gaps. The end result is that I have to parse all of the daily directories all the time to see if any files have appeared "in the past."

- the tables on the local database are cleared
- a DOS batch file then parses all of the daily directories and runs a BULK INSERT against each of the flat files, importing them into the database

The problem with this is that the database is hammered every day as it processes the thousands of flat files and imports millions of records. The batch job takes about two hours to run these days and everything else dies while it happens.

I drop the table and do a bulk insert because if I don't drop the table, I get duplicate records.

Surely there is a better way of doing this, but you know, rookie.

# ? Sep 1, 2009 17:31

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Sounds... financial. Am I close?

I'm no DBA, but have you considered turning of indexing and logging for that particular table/database?

# ? Sep 1, 2009 18:05

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

Triple Tech posted:

Sounds... financial. Am I close?

I'm no DBA, but have you considered turning of indexing and logging for that particular table/database?

Bingo. I work for an online broker.

Indexing and logging are already turned off.

# ? Sep 1, 2009 19:20

Sub Par: Jul 18, 2001; Dinosaur Gum

Agrikk posted:

Bingo. I work for an online broker.

Indexing and logging are already turned off.

What is the nature of the duplicate records? Are the files you're importing cumulative (i.e. 9/1/2009 has all 8/31/2009 data plus all the stuff that happened in-between), or are they supposed to build on each other (and if so, why duplication)?

Can you do anything with a primary key and insert only unique rows rather than dropping the table every day and doing a bulk insert of millions of rows?

# ? Sep 1, 2009 19:34

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

Sub Par posted:

What is the nature of the duplicate records? Are the files you're importing cumulative (i.e. 9/1/2009 has all 8/31/2009 data plus all the stuff that happened in-between), or are they supposed to build on each other (and if so, why duplication)?

Can you do anything with a primary key and insert only unique rows rather than dropping the table every day and doing a bulk insert of millions of rows?

The file I am importing are cumulative in some cases and not in others. I have full control over my local database so I can do whatever I want to it to make it more efficient. Can you tell me more about how I'd use primary keys and how to insert only unique rows?

# ? Sep 1, 2009 21:16

MikeRabsitch: Aug 23, 2004; Show us what you got, what you got

Agrikk posted:

The file I am importing are cumulative in some cases and not in others. I have full control over my local database so I can do whatever I want to it to make it more efficient. Can you tell me more about how I'd use primary keys and how to insert only unique rows?

Does the flat file have unique identifiers or do you assign them a unique number when importing? If they come with a primary key and you use whatever they have it should be easier.

# ? Sep 1, 2009 21:51

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

Knightmare posted:

Does the flat file have unique identifiers or do you assign them a unique number when importing? If they come with a primary key and you use whatever they have it should be easier.

The flat file has a name with a date string in it. The contents of the files vary, but generally include a customer code or an account ID or a trasaction ID or some combination of all three. They do not come with a primary key since they are extracts from a report derived from multiple tables on the far end.

# ? Sep 1, 2009 21:56

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

I have written a stored procedure that, when passed the ID number of a user, calculates that user's rating (a number that's meant to reflect how good a player that user is, based on how many games he has played and how many he has won) and updates the user's row with the rating. So when I send MySQL the query "CALL CalculateRating(12345)" I am getting it to do something of the form "UPDATE User SET Rating = (result of some subquery) WHERE UserID = 12345".

What I would like to know is, can I write a single query that will do this for each of my users at once? At the moment I have a query "SELECT UserID FROM User", and I loop through the results in PHP and issue a bunch of queries that say "CALL CalculateRating($userid)". Is there SQL syntax to say, "for each UserID in such and such a results set, CALL CalculateRating(UserID)"?

Or could I go even better: could I write a single query that updates each of the rows of User with the value calculated for that user from a subquery involving a "GROUP BY UserID" clause?

# ? Sep 2, 2009 00:08

Carthag Tuek: Oct 15, 2005; Tider skal komme,
tider skal henrulle,
sl�gt skal f�lge sl�gters gang

Hammerite posted:

I have written a stored procedure that, when passed the ID number of a user, calculates that user's rating (a number that's meant to reflect how good a player that user is, based on how many games he has played and how many he has won) and updates the user's row with the rating. So when I send MySQL the query "CALL CalculateRating(12345)" I am getting it to do something of the form "UPDATE User SET Rating = (result of some subquery) WHERE UserID = 12345".

What I would like to know is, can I write a single query that will do this for each of my users at once? At the moment I have a query "SELECT UserID FROM User", and I loop through the results in PHP and issue a bunch of queries that say "CALL CalculateRating($userid)". Is there SQL syntax to say, "for each UserID in such and such a results set, CALL CalculateRating(UserID)"?

Or could I go even better: could I write a single query that updates each of the rows of User with the value calculated for that user from a subquery involving a "GROUP BY UserID" clause?

Something like:

UPDATE users u SET rating = (SELECT SUM(s.wins)/SUM(s.games_played) FROM stats s WHERE s.user_fk = u.id);

(change the select to how your actual formula works naturally)

# ? Sep 2, 2009 01:11

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

Carthag posted:

Something like:

UPDATE users u SET rating = (SELECT SUM(s.wins)/SUM(s.games_played) FROM stats s WHERE s.user_fk = u.id);

(change the select to how your actual formula works naturally)

Thanks for this. I didn't realise that it was legal to refer in a subquery to a table that is only part of the outside query.

One thing I wonder: Is MySQL doing this "sensibly" (meaning: it creates the result set you call s only once), or is it doing it the dumb way (creating s over again for each row in u)? I would hope it's doing it properly, as opposed to doing thousands* of redundant calculations, but I've no idea how I'd check.

*if not millions - there are close to a thousand rows in User, so if it creates s r times, where r is the number of rows in User, then I guess it is doing r*(r-1) too many calculations.

Hammerite fucked around with this message at 15:54 on Sep 2, 2009

# ? Sep 2, 2009 15:49

MoNsTeR: Jun 29, 2002

Hammerite posted:

Thanks for this. I didn't realise that it was legal to refer in a subquery to a table that is only part of the outside query.

One thing I wonder: Is MySQL doing this "sensibly" (meaning: it creates the result set you call s only once), or is it doing it the dumb way (creating s over again for each row in u)? I would hope it's doing it properly, as opposed to doing thousands* of redundant calculations, but I've no idea how I'd check.

*if not millions - there are close to a thousand rows in User, so if it creates s r times, where r is the number of rows in User, then I guess it is doing r*(r-1) too many calculations.

But that result set is different for every row in "u", on which the rows returned by the subquery depend. So it is doing as many calculations as you fear, but they are not redundant.

For the result set to be only calculated once, you would have to select all rows and group, then loop through those rows and update. But that's not what this kind of bulk update statement does.

# ? Sep 2, 2009 20:31

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

MoNsTeR posted:

But that result set is different for every row in "u"

It isn't the way I've written it. My s gets the necessary information to return each user's rating, and uses a GROUP BY clause. So s will be the same every time.

Full disclosure - my query looks like this:

code:

UPDATE User SET Rating = (
    SELECT CASE
               WHEN NumPlays IS NULL THEN 0
               WHEN NumPlays = 0 THEN 0
               ELSE ROUND(10000*POW((1-1/NumPlays), 2)*NumWins/NumPlays)
           END AS SubjectUserRating
    FROM (
        SELECT PlayerGameRcd.User,
               SUM(
                   CASE Game.EffectiveNumPlayers
                       WHEN 2 THEN 0.8
                       ELSE 1
                   END
               ) AS NumPlays,
               SUM(
                   CASE
                       WHEN PlayerGameRcd.GameResult = 'Finished 1st' AND
                            Game.EffectiveNumPlayers = 2 THEN 0.8
                       WHEN PlayerGameRcd.GameResult = 'Finished 1st' AND
                            Game.EffectiveNumPlayers > 2 THEN 1
                       WHEN PlayerGameRcd.GameResult = 'Finished 2nd' AND
                            Game.EffectiveNumPlayers = 3 THEN 0.44
                       WHEN PlayerGameRcd.GameResult = 'Finished 2nd' AND
                            Game.EffectiveNumPlayers = 4 THEN 0.63
                       WHEN PlayerGameRcd.GameResult = 'Finished 3rd' AND
                            Game.EffectiveNumPlayers = 4 THEN 0.26
                       ELSE 0
                   END
               ) AS NumWins
        FROM PlayerGameRcd LEFT JOIN Game ON PlayerGameRcd.Game = Game.GameID
        WHERE PlayerGameRcd.GameResult <> 'Game Aborted' AND
              PlayerGameRcd.GameCounts = 1 AND
              Game.Friendly = 0 AND
              TIMESTAMPADD(MONTH, 5, Game.LastMove) > UTC_TIMESTAMP()
        GROUP BY PlayerGameRcd.User
    ) AS SubqueryA
    WHERE UserID = User    *
)

* Table User has a column UserID and table PlayerGameRcd has a corresponding column User which is the column given under GROUP BY.

So my s (called SubqueryA) does not need to be recalculated every time - are you telling me that it will be?

# ? Sep 2, 2009 23:01

MoNsTeR: Jun 29, 2002

Yes it will be. When you issue a statement of the form

update <table>
set <column> = <select statement>

That select statement will be run for each row in <table>. So generally when you write this kind of statement, your subquery should be correlated to the rows in the table being updated. If it's significantly slower to do this than to calculate the results for all rows in one shot, then you should do that, and loop through the results issuing an update for each row, rather than trying to issue a single update.

# ? Sep 2, 2009 23:18

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

MoNsTeR posted:

Yes it will be. When you issue a statement of the form

update <table>
set <column> = <select statement>

That select statement will be run for each row in <table>. So generally when you write this kind of statement, your subquery should be correlated to the rows in the table being updated. If it's significantly slower to do this than to calculate the results for all rows in one shot, then you should do that, and loop through the results issuing an update for each row, rather than trying to issue a single update.

Thanks. Is there a way to do this using a stored procedure? As in: can I write a stored procedure that calculates my SubqueryA and then loops through it as you suggest - or do I need to go back to looping through a result set in PHP?

EDIT: Never mind, I have found out how to do this using MySQL cursors.

Hammerite fucked around with this message at 01:01 on Sep 3, 2009

# ? Sep 2, 2009 23:28

Tad Naff: Jul 8, 2004; _{I told you you'd be sorry buying an emoticon, but no, you were hung over. Well look at you now. It's not catching on at all!}

Using OCI8 and ADOdb. This is an odd one. The loop goes along happily until it hits December 2008, then the program just exits -- no happy message, no error message, nothing in the logs.

php:

<?
$days=365; // days to go back
$chunksize=10; // process this many days at a time
$pniq2= /* a stored query, details to follow */ ;

$datetime=new DateTime();
$today=$datetime->format('Ymd');
$datetime->modify('-'.$days.' days');
$dateuntil=$datetime->format('Ymd');

while($dateuntil!=$today){
        $extract_date=$dateuntil;
        $datetime->modify('+'.$chunksize.' days');
        $dateuntil=$datetime->format('Ymd');
        if($dateuntil>$today) $dateuntil=$today;
        $rs=$db->Execute($pniq2, array('extractdate'=>$extract_date,'untildate'=>$dateuntil));
echo('<p>I just made a query!</p>');
if(!$rs)die('<h1>Error: '.$db->ErrorMsg().'</h1>');
        while($item=$rs->FetchRow()){
             //stuff
        }
        $rs->Close();
}
?>

The output (with debug on) looks like this:

code:

(oci8): select to_char(BIB_MASTER.CREATE_DATE,'YYYY-MM-DD HH24:MI:SS') as 
CREATE_DATE, BIB_MASTER.BIB_ID, BIB_DATA.SEQNUM, BIB_DATA.RECORD_SEGMENT from 
BIB_MFHD, BIB_MASTER, BIB_DATA where BIB_MASTER.CREATE_DATE > 
to_date(:extractdate,'YYYYMMDD') and BIB_MASTER.CREATE_DATE <= 
to_date(:untildate,'YYYYMMDD') and BIB_MASTER.BIB_ID = BIB_MFHD.BIB_ID and 
BIB_DATA.BIB_ID = BIB_MASTER.BIB_ID group by BIB_MASTER.BIB_ID, BIB_DATA.SEQNUM, 
BIB_DATA.RECORD_SEGMENT, CREATE_DATE   [ (extractdate=>'20080902') 
(untildate=>'20080912') ]

I just made a query!

[... a lot of unrelated output from 'do stuff' ...]

about 10 times, then

code:

(oci8): select to_char(BIB_MASTER.CREATE_DATE,'YYYY-MM-DD HH24:MI:SS') as 
CREATE_DATE, BIB_MASTER.BIB_ID, BIB_DATA.SEQNUM, BIB_DATA.RECORD_SEGMENT from 
BIB_MFHD, BIB_MASTER, BIB_DATA where BIB_MASTER.CREATE_DATE > 
to_date(:extractdate,'YYYYMMDD') and BIB_MASTER.CREATE_DATE <= 
to_date(:untildate,'YYYYMMDD') and BIB_MASTER.BIB_ID = BIB_MFHD.BIB_ID and 
BIB_DATA.BIB_ID = BIB_MASTER.BIB_ID group by BIB_MASTER.BIB_ID, BIB_DATA.SEQNUM, 
BIB_DATA.RECORD_SEGMENT, CREATE_DATE   [ (extractdate=>'20081121') 
(untildate=>'20081201') ]

...and that's it. I've ruled out out-of-memory, and like I said there's nothing in the error logs. ADOdb isn't saying anything, so it's starting to smell like OCI8 is crashing silently. Anyone have any experience of this sort of thing?

Edit
Turns out it was out of memory after all, who knew somebody would dump in 40000 records in the space of a week. Now I have PHP's memory limit set to friggin' 256M, oh boy. Now, why doesn't it say out of memory?

Tad Naff fucked around with this message at 00:39 on Sep 3, 2009

# ? Sep 2, 2009 23:48

Boz0r: Sep 7, 2006; The Rocketship in action.

I'm trying to learn SQL with IBM DB2 but I'm stuck trying to make some crazy-rear end queries. I want to find out, if all the giraffes combined weigh more than all the elephants combined, but it's proving to be a real nutcracker for a novice like me.

I've drawn some diagrams of taking it step by step:
1. compiling a list of each animal and its weight.
2. summing each species combined weight.
3. removing the species that aren't giraffes or elephants.
4. Comparing which combined weight is larger.

I've been able to make queries to sum up a single species' weight but I can't figure out how to make a list of it.

Anyone have any good ideas?

code:

CREATE TABLE species (
	id INT NOT NULL PRIMARY KEY,
	type VARCHAR( 40 ) NOT NULL,
	class VARCHAR( 9 ) NOT NULL, 
	fodder INT NOT NULL,
	UNIQUE (type)
);

CREATE TABLE spec_animal (
	id	INT	NOT NULL PRIMARY KEY,
	type_id	INT	NOT NULL,		-- id of the species
	name	VARCHAR( 40 ),
	kg_weight	FLOAT	NOT NULL,
	food_pr_day	FLOAT 	NOT NULL,
	birthday	DATE	NOT NULL
);

CREATE TABLE fodder (
	id		INT	NOT NULL,
	name	VARCHAR( 40 )	NOT NULL,
	kg_price	FLOAT	NOT NULL
);

CREATE TABLE food_storage (
	id		INT NOT NULL,		-- id of the fodder
	kg		FLOAT	default 0
);

# ? Sep 3, 2009 13:21

Sub Par: Jul 18, 2001; Dinosaur Gum

To sum up the weight of all animals in each species:

code:

select species.type, sum(spec_animal.kg_weight)
from species, spec_animal
where species.id = spec_animal.type_id
group by species.type

There are lots of ways to determine which species has a greater total weight, I don't know a lot about DB2 but you could use subqueries to take care of it all at once, or used stored procedures or ... lots of ways, depending on why you need to get the information and how you want it displayed.

# ? Sep 3, 2009 16:04

MikeRabsitch: Aug 23, 2004; Show us what you got, what you got

I don't know if DB2 allows CASE, but here's what I would do:

code:

CASE WHEN (
(SELECT sum(spec_animal.kg_weight)
 FROM spec_animal, species
 WHERE spec_animal.id = species.id
 AND species.type = 'GIRAFFE')
 >
(SELECT sum(spec_animal.kg_weight)
 FROM spec_animal, species
 WHERE spec_animal.id = species.id
 AND species.type = 'ELEPHANT')
) THEN 'GIRAFFE' ELSE 'ELEPHANT' END;

Actually I do like Sub Par's better, that'll show each animal species and their combined total weight in a nice list. In his, you could also add AND species IN ('GIRAFFE','ELEPHANT') to only show those animals.

# ? Sep 3, 2009 20:00

MoNsTeR: Jun 29, 2002

This doesn't go to answering your question in the slightest but it is an important design tip that merits mentioning. "fodder" on "species" should be "fodder_id", "type_id" on "spec_animal" should be "species_id", and "id" on "food_storage" should be "fodder_id". This may seem trivial but when you're reading a query you wrote last year against tables someone else created the year before that, it makes orders-of-magnitude difference in the readability and intelligibility of a query.

(To take it one step further, "id" on "species" should also be "species_id", "id" on "spec_animal" should be "spec_animal_id", etc. This is my preference though and I can see the argument for always using "id".)

# ? Sep 3, 2009 20:58

TheSpook: Aug 21, 2007; Spooky!

Kind of a silly question . . .

I have a (fairly small, fairly simple) database set up using Postgres. Eight tables total.

Can anyone recommend DB visualization software? Something that visualizes schema/dependencies/et cetera . . .

# ? Sep 4, 2009 04:13

ether: May 20, 2001

Boz0r posted:

I'm trying to learn SQL with IBM DB2 but I'm stuck trying to make some crazy-rear end queries. I want to find out, if all the giraffes combined weigh more than all the elephants combined, but it's proving to be a real nutcracker for a novice like me.
...
Anyone have any good ideas?

Not at all familiar with DB2 but I would approach it something like this, although I must say that mixing conditional aggregates and group by's are very dependant on what order the database evaluates them.

code:

SELECT 
    (SUM(IF species.type = 'Elephant' THEN spec_animal.kg_weight ELSE NULL END IF) < 
     SUM(IF species.type = 'Giraffe' THEN spec_animal.kg_weight ELSE NULL END IF)) 
      AS giraffes_are_heavier
  FROM species
    INNER JOIN spec_animal ON (spec_animal.type_id = species.id)
  WHERE species.type IN ('Elephant', 'Giraffe')
  GROUP BY species.type;

Sub Par's query just gives you a nice table which is very usable for humans to interpret. I just solved the literal question

# ? Sep 4, 2009 08:47

Verve: Nov 19, 2005

I've been getting deadlocks issues on our MSSQL database for the past few days. I ran SQL profiler and extracted the deadlock graph and found out that the following query has a Shared intent update (SIU) lock mode.

code:

SELECT   *
FROM     products AS prd
WHERE    (prd.catalogid <> 1324)
         AND (prd.hide = 0
               OR prd.hide IS NULL)
         AND (prd.highercatalogid IS NULL)
ORDER BY Upper(prd.cname),
         prd.catalogid

Any idea what is causing the query above to gain an "Update" lock mode when there's no update query in it ?

# ? Sep 4, 2009 11:24

supster: Sep 26, 2003; I'M TOO FUCKING STUPID
TO READ A SIMPLE GRAPH

I'm using MySQL with InnoDB tables and am trying to avoid some potential concurrency issues and I'm not sure which of the two ways I've come up with is the best way to do it (or if another better solution exists).

Let's say I've got an auction database with two tables, auctions (items up for auctions), and bids (history of bids on auction items). I want to make sure that everything is ok if two bids are placed at the same time on the same auction. Let's say a bid of 30 is being placed on auction with id=1. I need to make sure the bid is valid (the auction price isn't already at 30 or above) and then place the bid.

Lines starting with # are pseudocode for application logic.

Approach 1:

code:

begin;
update auctions set price=30 where id=1 and price<30;
# if rows affected > 0:
    insert into bids (auction_id, price) values (1, 30);
commit;

Approach 2:

code:

begin;
select price from auctions where id=1 for update;
# if price < 30:
    update auctions set price=30 where id=1;
    insert into bids (auction_id, price) values (1, 30);
commit;

I understand that two very different things are going on here, but I don't fully understand the pros and cons of each method. I also am not fully confident that both methods are doing what I am trying to accomplish. Any input would be appreciated.

edit: I have doubts about Approach 1: is the read on the price column for the WHERE clause and the update to the price column one atomic instruction? Or can another connection update the value of the price after the price is read for the WHERE clause but before the update happens?

edit2: I'm now pretty convinced that Approach 2 is definitely what I should be doing - but I still want to make sure I'm not missing anything.

supster fucked around with this message at 20:16 on Sep 4, 2009

# ? Sep 4, 2009 20:11

MoNsTeR: Jun 29, 2002

Both are doing what you want, which is essentially
1. lock the auction row
2. check that the new price is valid
3. if so, then set the new price and insert the bid
4. commit, thereby releasing the lock

The where clause of the update is part of the same transaction so there is no chance you will get dirty data (unless your isolation level is set to Read Uncommitted or something crazy like that).

Personally I like #1 for its simplicity but #2 has additional clarity. It's a toss-up.

# ? Sep 4, 2009 22:05

Adbot: ADBOT LOVES YOU

# ? May 16, 2024 00:51

nbv4: Aug 21, 2002; by Duchess Gummybuns

nevermind...

nbv4 fucked around with this message at 02:49 on Sep 5, 2009

# ? Sep 4, 2009 22:21

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

«‹›303 »