On Sunday I flew, with my five year old son, to Baltimore Washington Internation airport (BWI). We went to support my father and attend the funeral of his father. My son had gotten quite close with Grandpa Jim since we moved down to Tampa so I wanted him to go for obvious reasons.
My grandfather was to be buried at Quantico with his wife who passed away 12 years ago. He passed away on Father's Day.
Initially I wanted to drive. I haven't been on a plane in 6 years (OpenWorld 2002), and I'm a bit claustrophobic. My parents convinced me that flying was the best way to go since my wife's grandparents were in the hospital as well (94 and 95). I reluctantly agreed. I then decided it would be a good time for my son to take his first flight. I did not want him to be scared of flying, so I knew what I had to do.
I decided to rent a car so that my parents would not have to drive 2 hours to pick me up and take us back. We arrived and we got on the bus to the rental car facility (about 10 minutes away). I booked a car with Budget through USAA.
I quickly found out that Budget put a $500 hold on your debit card. Since we're still adjusting to the new payment schedule, we didn't have that much in the bank. I called my parents and they were all set to come get us. Then the woman from Budget suggested Hertz as they don't put as big a hold on your card.
So I walk over and the first thing I say is, "I don't have $500 in my account, what's your hold?" I don't remember exactly what it was, but it surely wasn't $500. OK, give me a car please, cheapest you got. I only need the car for a day (24 hours). Do you want any options? No. Do you want the Fuel Service Option? Sure, I think, I won't have to fill up on the way back since we'll be cutting it close anyway.
I get the car and drive down to Fort Belvoir with my parents. The following day, we go to the service and then the cemetary. It's 2 o'clock now, the flight leaves at 4:54 and the car has to be back by 3:30.
I figure I'll have to pay some sort of small fee for being past 3:30. No biggie, it happens.
I drop the car off, the guy scans the car, enters the mileage and gives me the receipt. $117. WTF?
Are you kidding me?
You bought the Fuel Service Option.
Yeah, and you'll deduct the amount that I didn't use right?
No. They should have explained that to you.
Ummm...no, they didn't.
Ultimately, it's my responsibility. I signed the contract. I don't expect to recoup any money.
But if this isn't disingenuous, I don't know what is. The guy knew I need the car for one day. Did he think I was going to drive 450 miles? He also knew that my reservation with Budget fell through because I didn't have $500 in the bank. Then I have a 5 year old running around the facility...
Again, I signed the contract. I accept full responsibility. I just think it sucks on Hertz part. Sadly, my father had the same thing happen to him. Bought the fuel service option and got screwed.
So, my advice to you (as I'm sure more than one of you have to rent a car every so often), don't get the fuel service option!
I will not rent from Hertz again. Budget, while way too much on the deposit, at least pointed me in the right direction. I appreciate that.
Tuesday, June 24, 2008
Wednesday, June 18, 2008
Long Live the Revolution!
Revolution Money that is...
I'm no longer afraid to at least say that, though if you had checked out my LinkedIn profile, you would have seen it.
Revolution Money is a startup company described as "PayPal meets MasterCard" without the fees. Among the board members are:
So far I am extremely happy, though I don't really know how to deal with the distress that is no longer there. I suppose it will wane with time...
Everyone I work with seems to be amongst the best and brightest which is pretty cool. My view (pictures coming soon) is pretty awesome. Relaxed environment (think casual Friday every day). The CEO brings his dog into work...
I should have the opportunity to learn quite a bit here.
I'm no longer afraid to at least say that, though if you had checked out my LinkedIn profile, you would have seen it.
Revolution Money is a startup company described as "PayPal meets MasterCard" without the fees. Among the board members are:
- Steve Case, founder of AOL
- Ted Leonsis (here and here), among other things, he is the current owner of the Washington Capitols
- Larry Summers, former Secretary of the Treasury
So far I am extremely happy, though I don't really know how to deal with the distress that is no longer there. I suppose it will wane with time...
Everyone I work with seems to be amongst the best and brightest which is pretty cool. My view (pictures coming soon) is pretty awesome. Relaxed environment (think casual Friday every day). The CEO brings his dog into work...
I should have the opportunity to learn quite a bit here.
Wednesday, June 11, 2008
SQL for Buying a New Car
Two weeks ago I purchased a new car. The main reason behind that was that I drove a 1996 Nissan P.O.S. (not point of sale either). I now had a 30+ mile commute each way and I needed a reliable car.
A big factor was gas mileage.
Two brands stood out, Honda and Toyota. Honda has the Civic Hybrid and Toyota has the Prius (even though I don't care much for the design). The Corolla and Civic were the top two cars.
So after visiting those two dealers to look at cars, I stopped by the Mazda dealer to see if they had anything with MPG greater than 30. I drove the Mazda3 and it was nice (everything would be nice compared to my POS). Salesman offered to run the numbers and I said OK.
The point of this rambling, is that I want to know what it will cost based on mileage. Of course I didn't have my laptop, so I couldn't run the numbers (and I hadn't previously). I did it in Excel, but wanted to try it out in SQL.
Here's the result:
COLUMN ppg FORMAT $999.00
COLUMN car_1_gpy FORMAT 99,999
COLUMN car_2_gpy FORMAT 99,999
COLUMN car_1_cpy FORMAT $999,999.00
COLUMN car_2_cpy FORMAT $999,999.00
COLUMN car_1_cpm FORMAT 999.00
COLUMN car_2_cpm FORMAT 999.00
SELECT
car_1,
car_2,
ppg,
mpy,
ROUND( ( mpy / car_1 ) ) car_1_gpy,
ROUND( ( mpy / car_2 ) ) car_2_gpy,
ROUND( ( ( mpy / car_1 ) * ppg ), 2 ) car_1_cpy,
ROUND( ( ( mpy / car_2 ) * ppg ), 2 ) car_2_cpy,
ROUND( ( ( mpy / car_1 ) * ppg ) / mpy, 2 ) car_1_cpm,
ROUND( ( ( mpy / car_2 ) * ppg ) / mpy, 2 ) car_2_cpm
FROM
(
SELECT
rownum car_1,
LAG( rownum, 5 ) OVER
( PARTITION BY NULL
ORDER BY rownum ) car_2
FROM dual
CONNECT BY LEVEL <= 50
) car_miles,
(
SELECT
3.99 ppg,
15000 mpy
FROM dual
) constants
/
CAR_1 CAR_2 CAR_1_GPY CAR_2_GPY CAR_1_CPY CAR_2_CPY CAR_1_CPM CAR_2_CPM
---------- ---------- --------- --------- ------------ ------------ --------- ---------
1 15,000 $59,850.00 3.99
2 7,500 $29,925.00 2.00
3 5,000 $19,950.00 1.33
4 3,750 $14,962.50 1.00
5 3,000 $11,970.00 .80
6 1 2,500 15,000 $9,975.00 $59,850.00 .67 3.99
7 2 2,143 7,500 $8,550.00 $29,925.00 .57 2.00
8 3 1,875 5,000 $7,481.25 $19,950.00 .50 1.33
9 4 1,667 3,750 $6,650.00 $14,962.50 .44 1.00
10 5 1,500 3,000 $5,985.00 $11,970.00 .40 .80
11 6 1,364 2,500 $5,440.91 $9,975.00 .36 .67
12 7 1,250 2,143 $4,987.50 $8,550.00 .33 .57
13 8 1,154 1,875 $4,603.85 $7,481.25 .31 .50
14 9 1,071 1,667 $4,275.00 $6,650.00 .28 .44
15 10 1,000 1,500 $3,990.00 $5,985.00 .27 .40
16 11 938 1,364 $3,740.63 $5,440.91 .25 .36
17 12 882 1,250 $3,520.59 $4,987.50 .23 .33
18 13 833 1,154 $3,325.00 $4,603.85 .22 .31
19 14 789 1,071 $3,150.00 $4,275.00 .21 .28
20 15 750 1,000 $2,992.50 $3,990.00 .20 .27
21 16 714 938 $2,850.00 $3,740.63 .19 .25
22 17 682 882 $2,720.45 $3,520.59 .18 .23
23 18 652 833 $2,602.17 $3,325.00 .17 .22
24 19 625 789 $2,493.75 $3,150.00 .17 .21
25 20 600 750 $2,394.00 $2,992.50 .16 .20
26 21 577 714 $2,301.92 $2,850.00 .15 .19
27 22 556 682 $2,216.67 $2,720.45 .15 .18
28 23 536 652 $2,137.50 $2,602.17 .14 .17
29 24 517 625 $2,063.79 $2,493.75 .14 .17
30 25 500 600 $1,995.00 $2,394.00 .13 .16
31 26 484 577 $1,930.65 $2,301.92 .13 .15
32 27 469 556 $1,870.31 $2,216.67 .12 .15
33 28 455 536 $1,813.64 $2,137.50 .12 .14
34 29 441 517 $1,760.29 $2,063.79 .12 .14
35 30 429 500 $1,710.00 $1,995.00 .11 .13
36 31 417 484 $1,662.50 $1,930.65 .11 .13
37 32 405 469 $1,617.57 $1,870.31 .11 .12
38 33 395 455 $1,575.00 $1,813.64 .11 .12
39 34 385 441 $1,534.62 $1,760.29 .10 .12
40 35 375 429 $1,496.25 $1,710.00 .10 .11
41 36 366 417 $1,459.76 $1,662.50 .10 .11
42 37 357 405 $1,425.00 $1,617.57 .09 .11
43 38 349 395 $1,391.86 $1,575.00 .09 .11
44 39 341 385 $1,360.23 $1,534.62 .09 .10
45 40 333 375 $1,330.00 $1,496.25 .09 .10
46 41 326 366 $1,301.09 $1,459.76 .09 .10
47 42 319 357 $1,273.40 $1,425.00 .08 .09
48 43 313 349 $1,246.88 $1,391.86 .08 .09
49 44 306 341 $1,221.43 $1,360.23 .08 .09
50 45 300 333 $1,197.00 $1,330.00 .08 .09Obviously this isn't terribly difficult. You can do lots with the results as well.
Three or four years ago I would have created a table and a function to do this. Now I can do it "virtually."
I love SQL...
*Key:
car_1 = miles for car 1
car_2 = miles for car 2
car_1_gpy = gallons per year car 1
car_2_gpy = gallons per year car 2
car_1_cpy = cost per year car 1 (gas)
car_2_cpy = cost per year car 2 (gas)
car_1_cpm = cost per mile car 1
car_2_cpm = cost per mile car 2
Thursday, June 5, 2008
BULK COLLECT and FORALL
I have a task to tune a little bit of SQL. It's very ETL like, but the target is not (yet) a star schema, it's pretty much a table flattened out for reporting purposes.
As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go.
Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible.
For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after.
Let's create some data:
So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective.
I updated my code to this:
BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing.
As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go.
Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible.
For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after.
Let's create some data:
OK, now let's create an anonymous block, BULK COLLECTing the data from T into a PL/SQL table and then populated another table with that data:
CREATE TABLE t( x NUMBER, y NUMBER );
INSERT INTO t( x, y )
SELECT
TRUNC( dbms_random.value( 1, 99999999 ) ),
TRUNC( dbms_random.value( 1, 100000 ) )
FROM dual
CONNECT BY level < 1001;
And then I run it and I get the following:
DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;
LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;
FORALL i IN 1..l_table.COUNT
INSERT INTO s ( x, y )
VALUES ( l_table(i).x, l_table(i).y );
EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
A quick google search and I end up here .
ERROR at line 18:
ORA-06550: line 18, column 16:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 30:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 16:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 17, column 7:
PL/SQL: SQL Statement ignored
So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective.
I updated my code to this:
I run it and it completes successfully. This is all on XE, so I wonder (hope) that 11g will allow me to do what I want (I'll be working on an 11g RAC system). I scroll down the list of google results and I find this one which then takes me to AskTom. The first post demonstrates that my first attempt will work on 11g.
DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;
LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;
FORALL i IN 1..l_table.COUNT
INSERT INTO s
VALUES l_table(i);
EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing.
Subscribe to:
Posts (Atom)