Yes, that's right, you can't distinct on a clob. (Clobs are binary data, so Oracle doesn't want to try to compare them to see if they're the same.) You have a couple of options. You could group your cfoutput (probably the easiest, unless you need a count). Or, you could do a subquery to get the n_r_id of the relevant records and then select the clob and other fields based on that.
The reason it's pulling 4 records is because of the OR clause. In each case, the n.id is 3. On 1/26/07, daniel kessler <[EMAIL PROTECTED]> wrote: > >It's working the way it should. It's finding each relevant record. If > >you want just one version of it (and don't care about displaying who > >is sharing it) just add a distinct clause at the front. > > I received an error: > inconsistent datatypes: expected - got CLOB (so I guess distinct can't be > used on a CLOB?). > using this sql: > SELECT distinct n.meeting_name,n.note_date,n. n_r_id,n.id > FROM notes_to_the_record n LEFT OUTER JOIN notes_to_the_record_shared s ON > n.n_r_id = s.n_r_id > WHERE (n.ID = 3 OR s.id = 3 ) AND (1=1 ) > ORDER BY UPPER(n.meeting_name) ASC > > So, I removed the CLOB from the SELECT and it ran, still with 4 Notes to the > Record Demonstration entries when there is only 1. > Here are the results from the dump (not sure how this'll come through) > ommitting unnecessary info. This is without the CLOB, to make it run: > ROW ID MEETING_NAME NOTE_DATE N_R_ID (record id) > 1 3 Admin Council 2007-01-17 10:00:00.0 421 > 2 3 Audio in Lecture Hall 2006-11-29 14:00:00.0 281 > 3 3 Google Analytics stats tracking 2007-01-09 09:00:00.0 321 > 4 3 Notes to the Record Demonstration 2007-01-11 09:00:00.0 341 > 5 3 Notes to the Record Demonstration 2007-01-11 09:00:00.0 341 > 6 3 Notes to the Record Demonstration 2007-01-11 09:00:00.0 341 > 7 3 Notes to the Record Demonstration 2007-01-11 09:00:00.0 341 > 8 1402 shared 2007-01-19 09:00:00.0 441 > > I still don't get why there are 4 of them. I have one in the > notes_to_the_record db and it's shared to 4 people. But the s.id is not my > id (3) - it's of the people that it is shared to. And it works for people > that need to see the shared notes. > I do have two entries shared to me, but not this one (n_r_id=341) and I see > those fine. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267747 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
