Can't Select Distinct Records When Memo Field Is Involved

[btorrean]

Dear Forum,

I have the following code, which works perfectly, UNLESS I include the t0.comments field in the first select statement, which is a Memo field.

Code: Select all

select distinct t0.rec_no, t1.new_id as hnew_id, t0.amount, t0.dolacre, t0.dolsf, t0.dolunit, t0.datesale, t0.dateenter, t0.docnumber, t0.vp, t0.taxkey, t0.internalno, t0.party_one, t0.party_two, t0.tag, t0.rectype, t0.internhist
from hist_new_01 as t0
left join paw_test_02 as t1
on t0.internalno=t1.internalno and t0.taxkey=t1.taxkey
into table hist_test_40

If I include the 'comments' field, I get the following error:

Code: Select all

-> Operation is invalid for a Memo, Blob, General or Picture field

Am I going about this wrong? I am trying to include all fields from the 'hist_new_01' table, and any associated 'new_id' records from the 'paw_test_02' table, matching on 'internalno' and 'taxkey'.

Also, I need only the exact number of records from the 'hist_new_01' table. I tried a query using an EXISTS statement and that didn't work.

Maybe there's a better way to do this? I HAVE to have the 'comments' field in the resultant table. Any help would be appreciated!

Brian

[Admin]

Hello Brian!
The DISTINCT clause is not compatible with Memo fields. Remove the DISTINCT in order to run the SQL with 't0.comments' field included.

[btorrean]

Oleg,

Thanks for your reply! I tried running the code as you suggested, but the query doesn't do exactly what I want it to do in that fashion. Without the 'distinct' keyword, some duplicate records are created in the output. That is the situation I was trying to avoid.

Again, I need the exact records from 'hist_new_01', and any associated 'new_id's from 'paw_test_02'. In Excel, I am able to verify that such an operation would not cause invalid data to be created in the output. I just need it to work in DBF Commander, as I am working with DBF files that I need to update without moving them to Excel and then back to the DBF format.

Do you have any other ideas?

Brian

[Admin]

OK then, there are a couple of solutions:
1. Change type of 'comments' field from Memo to Character(254). But in this case, the data would be truncated to 254 symbols.
2. Wrap your initial SQL (with DISTINCT, but without 'comments') with another one query that selects 'comments' field based upon an identification field, e.g:

Code: Select all

select t0.*, t1.comments from (
select distinct t0.rec_no, t1.new_id as hnew_id, t0.amount, t0.dolacre, t0.dolsf, t0.dolunit, t0.datesale, t0.dateenter, t0.docnumber, t0.vp, t0.taxkey, t0.internalno, t0.party_one, t0.party_two, t0.tag, t0.rectype, t0.internhist
from hist_new_01 as t0
left join paw_test_02 as t1
on t0.internalno=t1.internalno and t0.taxkey=t1.taxkey
) as t0 left join hist_new_01 as t1 on t0.internalno=t1.internalno
into table hist_test_40


The 'internalno' field must have unique values to be used as an identification field here.

[btorrean]

Oleg,

I wasn't able to get the code you sent to work, but I was able to modify it and make the modified code work for me. I believe the reason that your code didn't work was that internalno is not unique. However, rec_no is.

Here's the code that I did get to work:

Code: Select all

select t2.*, t3.comments from (
select distinct t0.rec_no, t1.new_id as hnew_id, t0.amount, t0.dolacre, t0.dolsf, t0.dolunit, t0.datesale, t0.dateenter, t0.docnumber, t0.vp, t0.taxkey, t0.internalno, t0.party_one, t0.party_two, t0.tag, t0.rectype, t0.internhist
from hist_new_01 as t0
left join paw_test_02 as t1
on t0.internalno=t1.internalno and t0.taxkey=t1.taxkey
) as t2 left join hist_new_01 as t3 on t2.rec_no=t3.rec_no
into table hist_test_50

Thanks so much for your help! I really appreciate it!

Brian