Can't Select Distinct Records When Memo Field Is Involved

Forum for DBF Commander discussing.

Can't Select Distinct Records When Memo Field Is Involved

Postby btorrean » Fri Feb 19, 2021 5:10 am

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
btorrean
 
Posts: 8
Joined: Sun Jun 23, 2019 7:40 pm

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

Postby Admin » Fri Feb 19, 2021 10:33 am

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.
Best regards,
Oleg Zhechkov
User avatar
Admin
Site Admin
 
Posts: 124
Joined: Wed Apr 21, 2010 7:27 pm

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

Postby btorrean » Sat Feb 20, 2021 1:31 am

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
btorrean
 
Posts: 8
Joined: Sun Jun 23, 2019 7:40 pm

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

Postby Admin » Sat Feb 20, 2021 9:53 am

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.
Best regards,
Oleg Zhechkov
User avatar
Admin
Site Admin
 
Posts: 124
Joined: Wed Apr 21, 2010 7:27 pm

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

Postby btorrean » Mon Feb 22, 2021 4:14 am

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
btorrean
 
Posts: 8
Joined: Sun Jun 23, 2019 7:40 pm


Return to DBF Commander Professional

Who is online

Users browsing this forum: No registered users and 5 guests

cron