Page 1 of 1

Can't Select Distinct Records When Memo Field Is Involved

PostPosted: Fri Feb 19, 2021 5:10 am
by 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

Re: Can't Select Distinct Records When Memo Field Is Involve

PostPosted: Fri Feb 19, 2021 10:33 am
by 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.

Re: Can't Select Distinct Records When Memo Field Is Involve

PostPosted: Sat Feb 20, 2021 1:31 am
by 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

Re: Can't Select Distinct Records When Memo Field Is Involve

PostPosted: Sat Feb 20, 2021 9:53 am
by 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.

Re: Can't Select Distinct Records When Memo Field Is Involve

PostPosted: Mon Feb 22, 2021 4:14 am
by 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