Oracle String Concatenation from Sub Query
Posted by Ben in Tech Notes on June 4th, 2010
Table Structure:
Desired output:
| ITEMNAME | COLORSOFFERED |
| Shirt | Brown, Blue, Green |
| Pants | Green, Brown |
| Dress | Brown, Blue, Pink, Green |
| Shorts | Green, Brown, Blue |
Have you ran into a situation where you want to combine several rows of data from a sub query in a concatenated string while querying another table? It seems to come up for me a lot, but I’ve historically addressed it in code while using Oracle. I found a way using Oracle functions to do this fairly easily and thought I would share. If you’re running Oracle 11g release 2 I’ve been told there is a LISTAGG function that would replace what I have done here, but this will work for the rest of us.
First, you have to create a function for the sub query:
CREATE OR REPLACE FUNCTION "GET_COLORS" (ITEM_ID VARCHAR2)
RETURN VARCHAR2
IS
color_string VARCHAR2(2500) := NULL;
BEGIN
FOR cur_rec IN (SELECT C.COLORNAME FROM COLOR C, ITEMCOLOR IC WHERE C.COLORID = IC.COLORID AND IC.ITEMID = ITEM_ID) LOOP
color_string := color_string || ', ' || cur_rec.COLORNAME;
END LOOP;
RETURN LTRIM(color_string, ',');
END;
Once the function is created, compliled, and execute permissions granted, etc. you’ll be able to call the function like so :
SELECT ITEMNAME, GET_COLORS(ITEMNAME.ITEMID) AS COLORSOFFERED FROM ITEM
That’s it… Granted this could be tedious if you needed to do this often, but it is a fast solution
GVSU seeks Web Developer
Posted by Ben in Uncategorized on May 26th, 2010
Grand Valley State University is looking for a talented Web developer to join a great team that works on a range of projects. The GVSU Web Team primarily uses Adobe’s ColdFusion paired with an Oracle database environment to create cutting-edge Web applications. If you’re looking for a position, or know someone that would be interested, I’d love to hear from you.
here is a quick snapshot of what we’re looking for:
To apply, or to find more information visit: http://bit.ly/9Qsp5R
Really Good Soft Pretzels
Posted by Ben in Uncategorized on May 13th, 2010
I was craving soft pretzels for some reason today so I made a batch after getting home from work. In case you ever get the craving here is our recipe (aside from not looking like your standard pretzel shape they are excellent):
Really Good Soft Pretzels
Ingredients:
- 1 tablespoon yeast
- 1 table spoon sugar
- 1 teaspoon salt
- 3 tablespoons butter
- 1 cup warm water
- 2 3/4 cups flour
- 4 cups water, boiling (for water bath)
- 4 teaspoons baking soda (for water bath)
Directions:
- Preheat oven to 475 degrees
- Mix butter, yeast, sugar, salt, hot water, and 1 cup of flour until smooth & bubbles form
- Gradually mix in remaining flour until dough is no longer sticky, add additional flour if necessary.
- Cover dough and allow to rise until it doubles in size (and hour or 2)
- Once dough has risen knead for 1-2 minutes, adding a bit of flour to make dough less sticky if need be
- Divide dough and roll out pieces of dough until strips are 1/2″ in diameter and long enough to form your pretzel shapes
- Let shaped pretzels sit for 2-3 minutes
- Drop pretzels into boiling water bath (4 cups of water and 4 teaspoons of baking soda, boiling) and let float for 1 minutes on each side
- Let pretzels drip dry on a wire rack and then transfer to a greased cookie sheet
- Cook for 13-15 minutes, or until pretzels reach desired brown-ness.
Once you start cutting the dough and shaping the pretzels it is helpful to have two people to keep the process going. The pretzels will start to accumulate on greased cookie sheets after the water bath and then you’ll just have to wait for everything to bake. To speed things up I usually skip the shaping step and just cut into 2 inch chunks which are great for dipping in something like a spicy or honey mustard sauce. Enjoy!
Would you like your own “mini-farm?”
Posted by Ben in Uncategorized on May 13th, 2010
For sale: One well used old farm house on 4.3 acres with a stocked pond, a dozen or so fruit trees, blueberry bushes, horse pastures, a little barn complete with a couple horse stalls and a big garden ready for planting. Inside the house there are 5 bedrooms, two bathrooms, and two basements (the Michigan basement consists of fieldstone and logs with the bark still on them supporting the house!) The second basement is the foundation for the addition that was added when I was in Junior High (I think) that doubled the size of the original house. The original house is very old and the neighbor’s house was one of the original school houses in the area. Down the road is the old Holloway railroad station…
How could you pass this up:
My mom is finally selling the house that me and my siblings grew up in. She hasn’t lived there in over a year as she has been busy with travel nursing assignments, and the place wasn’t getting the attention an old house needs to stay intact. I spent the past couple days in Tecumseh trying to get everything organized, meeting with Realtors, carpet installers, and doing all sorts of general remodeling. I was shocked how much better the house looked after updating all the light fixtures, updating one bathroom and replacing a lot of wall paper with more modern paint colors. I hope a new family enjoys all the hard work, and I hope it means the house sells a lot faster.
And then there was the barn… My dad was a collector of everything it seems; after he passed away a couple years ago we went through the barn and cleaned and organized the best we could. This time around I had to have a dumpster dropped off to finally purge a lot of the stuff that wouldn’t be wanted/needed by anyone else. The items he accumulated over the years ranged from flat out junk to the nicest antiques, including set after set of wrenches and ratchets, more hand saws than any one person could ever use, box after box of mismatched nuts and bolts, and random pieces of scrap metal that doesn’t seem to have any real purpose. After cleaning and purging I think I finally have the barn ready for the county’s biggest yard sale/flea market.
If you live in southeast Michigan and are looking for a great place to live let me know and I’ll gladly give you more info!
SQL’s LEFT, RIGHT, CHARIndex – Oracle Equivalents
Posted by Ben in Tech Notes on March 29th, 2010
If for no one else, I wanted to post this somewhere for my own reference. Having used SQL server during my “formative years” I find myself writing queries in Oracle and cursing aloud when my old ways of doing things don’t work. Here is one that I can’t seem to commit to memory.
If I wanted to grab the left portion of some data in SQL Server I would do the following:
select LEFT(theField,10) from theTable
If I wanted to grab the left portion of the data that preceded some character like the ‘@’ in an email address I would do something like the following:
select LEFT(theField,charIndex(theField,’@')-1)
For reference, here are the Oracle Equivalents:
To grab the left 10 characters:
select SUBSTR(theField,1,10)
To grab the right 10 characters:
select SUBSTR(theField,-10)
To grab the characters to the left of the ‘@’ sign like above:
select SUBSTR(theField,1,instr(theField,’@')-1)