Archive for category Tech Notes
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
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)
My Quest to Sync Google, Blackberry, & Entourage Calendars
Posted by Ben in Tech Notes on May 6th, 2009
I’ve had my mac now for a couple months and I’ve been absolutely pleased with it. Having been a big fan of Outlook on my previous pc laptop I transitioned to Entourage on the mac – Entourage leaves much to be desired but I stuck with it anyways. My co-workers in the office use Google calendar to share our schedules and since we all rely on that I wanted to find a way to sync the Entourage calendar with my Google calendar like I had done while using Outlook (using goosync.com).
I quickly found out there isn’t a straight-forward way to do this, and to complicate the matter I switched from a phone running Windows mobile to a Blackberry Curve mid-stream. While on the Windows mobile phone I was able to sync Entourage with my phone’s calendar using PocketMac for Windows Mobile ($30, running on the mac) and GooSync (free, on the phone), which in turn updated Google calendar – this is how I managed the process with Outlook as well. GooSync isn’t freely/easily available for the Blackberry so I set out to find another option.
First I tried the Google Sync app for Blackberry to sync the phone/Google calendar and switching to the PocketMac for blackberry to sync the phone with Entourage. The PocketMac app worked like a charm, all my contacts and calendar entries synced just fine, but Google’s Sync app won’t sync events from other calendar back to Google Calendar. This meant that the process only worked if new events were added to the Google calendar first as events entered in Entourage would never make it to Google.
After a week of trying, wiping out my calendar several times, creating wild duplicate events with all my testing I finally narrowed in on a solution. Here is what I found to sync my Google, Blackberry, and Entourage calendars, allowing me to enter an event in any of the three and trust that the others would be updated.
1) In Entourage go to the Prefernces and select the Sync Services item under General Preferences. In Sync Services you will need to check the second box for “Sync Events and Tasks with iCal…” This all happens behind the scenes and you don’t ever have to do anything with iCal – we’re just using iCal as a conduit to CalGoo in this case.
2) Download and install CalGoo Connect (Free) on your mac. Once installed you will need to setup a new connector in CalGoo – choose the first option to “Snyc an Apple iCal Calendar with a Google Calendar.” You will next be promped to select the calendar in iCal to be used as the sync source – select your Entourage Calendar. You’ll also need to select your Google Calendar which will be sync’d in case you have more than one. In the settings for CalGoo you can choose to automatically start the application at startup and automate the sync process, and even better, there is a feature to remove duplicate events from your calendar.
3) Download Google’s Sync app for Blackberry (http://m.google.com) and install that on your phone. In the settings for the Sync App you will need to enter your Google account info, and optionally, you can automate the process and select any of your Google Calendars to sync with.
By doing these three steps I was able to automate the entire process and I never need to connect my phone to my mac to sync calendars. I haven’t tried yet, but I suspect I can also automate my contact syncing using the Google Sync app as well.
I searched and searched for a way to do this without find a great soltution. Hopefully if you’ve stumbled across this it helped save you a bit of time and frustration.