This is the coolest thing I’ve seen in a long time.
As I was trying to decide if a bigint column would hold enough values to store historical data in a database, and storage problems aside, this helped me put it into perspective, and decide that, yes, for now, bigint would be big enough for me.
eighteen quintillion, four hundred forty six quadrillion, seven hundred forty four trillion, seventy three billion, seven hundred nine million, five hundred fifty one thousand, six hundred fifteen
~ 1 trillion pennies (click for details) (Sear’s Tower, Empire State Building & others for scale)
BIGINT provides 18.446 quintillion unique values.
Recently I ran into a problem with a query. I was trying to add the sums of two columns together, one of the columns happened to be in a table that was LEFT OUTER JOINed and the result was NULL when it should not have been.
This is for a purchase ordering system and is the calculation to get the total of inventory that is in transit, taking into account partial receipts of inventory.
Here is my table structure
select SUM( po_item_qty ) - SUM( po_receipt_qty ) as on_order from po_item left outer join po_receipt on ( po_receipt_po_item_id = po_item_id ) left outer join po on ( po_item_po_id = po_id ) left outer join product on ( po_item_product_id = product_id ) where po_status = 'in_transit'
This produces a result of ‘NULL’ when I know perfectly good and well that I have in transit purchase orders with po_item records.
It turns out that subtracting the po_receipt_qty when there are no matching po_receipt records (LEFT OUTER JOIN records return null when missing ) turns into, for example, 100 – NULL which always returns NULL.
What I need is for this to turn into 100 – 0, returning 100.
The query that made it work:
select SUM( po_item_qty ) - COALESCE( SUM( po_receipt_qty ) ,0 ) as on_order from po_item left outer join po_receipt on ( po_receipt_po_item_id = po_item_id ) left outer join po on ( po_item_po_id = po_id ) left outer join product on ( po_item_product_id = product_id ) where po_status = 'in_transit'
I’m using <button> tags in my forms for submit buttons using jquery ui to get icons. They weren’t working in IE7. This explains why:
include type=”submit” inside your button and it’ll work.