> READS SQL DATA > BEGIN > DECLARE total DOUBLE DEFAULT 0.0; > > SELECT SUM( value ) INTO total FROM (SELECT (IFNULL( SUM( > items.basePrice * lost.quantity * IF( items.techLevel >1, 10, 1 ) ) , 0 > ) + ships.basePrice * IF( ships.techLevel >1, 10, 1 )) / count(DISTINCT > inv.player_id) AS value FROM kb__killmails AS mails INNER JOIN > kb__involved AS inv ON (mails.id = inv.mail_id) LEFT JOIN kb__items_lost > AS lost ON ( lost.mail_id = mails.id ) LEFT JOIN kb__items AS items ON ( > lost.item_id = items.typeID ) INNER JOIN kb__items AS ships ON ( > mails.ship_id = ships.typeID ) WHERE mails.id IN (SELECT inv.mail_id AS > involved FROM kb__involved AS inv INNER JOIN kb__killmails AS kills ON > (inv.mail_id=kills.id) WHERE inv.player_id=check_id GROUP BY > inv.mail_id) GROUP BY mails.id) AS a; > > RETURN total; > END
If I was the DBA I wouldn't let that near the system. It is great that it works but that is a maintenance nightmare. Add some whitespace and indentation. Make it readable. I'm not talking about how complex it is - the complexity is fine. The readability leaves much to be desired. How many sub selects are there? Can you tell just by glancing at it? Indent every sub select. Gutter your SQL key words. Line up your conditions. You'll be happy you did later. Paul /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
