Thursday, April 14, 2016

Error When Using Distinct - ORA-01791: not a SELECTed expression

The Problem
I had a query that was working, but when I added a DISTINCT to it started throwing an error.  The error I was receiving was ORA-01791: not a SELECTed expression.  It didn't give me which field it didn't like, so I was confused.


The Solution
What I found was that once DISTINCT was in play all the fields in my ORDER BY had to be in the SELECT.  All of the ORDER BY fields had to be fields and not calculations.

Friday, March 18, 2016

How to insert an ampersand into an Oracle field


I was trying to insert a value into a varchar2 field.  The value contained an ampersand.  Something like "A&B Home Remodeling".  It kept giving me an error like &B was a variable.


What worked for me was storing it like this:
'A'||'&'||'B'


It like that much better.  I also saw suggestions to chr(38).  I did not try that, so I am not sure if it works, but I am guessing it would be very similar to what I did.  Something like:
'A'||chr(38)||'B'