I have been asked to knock up a report that prints out any duplicate records on table tdpur402 where the sales order, position number and sequence number are the same.

I have done the select statement to print out a report giving all the data in the table for the selection chosen in the form. I know just need to get it to only print duplicates.

I seem to have stumped myself and cannot seem to get my head around how to do this, I have tried using some nested If statements but have hit a dead end.

Can anyone point me in the right direction? Sorry if this is a stupid question.

You may use following SQL to get the same.

select tdpur402.??? , tdpur402,???, count(*)
from tdpur402
group by tdpur402.???, tdpur402.???
having count(*) > 1
selectdo

endselect

I have written questions becuase I dont know tdpur402 structure.

Hi ,

You can try this code : ofcourse it need lot of optimisations

temp.count = 1
select tdpur402.*
from tdpur402
where tdpur402._index2 inrange {:sono.f,:pono.f,:seqn.f} and
{:sono.t,:pono.t, :seqn.t}
and :temp.count > (select count(alstdpur402.*)
from tdpur402 alstdpur402
where alstdpur402._index2 = {:tdpur402.sorn,:tdpur402.spon,:tdpur402.sseq})
selectdo
|#print record
endselect

Cheers
Ayoobi

sorry . slight error in my previous code . modified code is as follows

temp.count = 1
select tdpur402.*
from tdpur402
where tdpur402._index2 inrange {:sono.f,:pono.f,:seqn.f} and
{:sono.t,:pono.t, :seqn.t}
and :temp.count < (select count(alstdpur402.*)
from tdpur402 alstdpur402
where alstdpur402._index2 = {:tdpur402.sorn,:tdpur402.spon,:tdpur402.sseq})
selectdo
|#print record
endselect

Hi

I have tried the option your gave me to use and everytime I run the report it gives me the "connection lost" error and shuts down Baan.

Is it our porting set or something like that or the code I am using that could cause this problem?

looks like a problem in the code . I will investigate and get back to you .

i think i have found the problem but i still could not test it out .

Revised Code
temp.count = 1
select tdpur402.*
from tdpur402
where tdpur402._index2 inrange {:sono.f,:pono.f,:seqn.f} and
{:sono.t,:pono.t, :seqn.t}
and :temp.count < (select count(alstdpur402.*)
from tdpur402 alstdpur402
where alstdpur402._index2 = {:tdpur402.sorn,:tdpur402.spon,:tdpur402.sseq}
group by alstdpur402.sorn,alstdpur402.spon,alstdpur402.sseq )
selectdo
|#print record
endselect

Explanation : The subquery was returning more than one rows whereas the main query was expecting one value . Bshell could not take this bouncer and u got this problem . i am sorry for it .

You can try this code too which i think is little bit improved code (combing hitesh's and my solution) :
select tdpur402.*
from tdpur402
where tdpur402._index2 inrange {:sono.f,:pono.f,:seqn.f} and
{:sono.t,:pono.t, :seqn.t}
and exists in (select count(*)
from tdpur402 alstdpur402
where alstdpur402._index2 = {:tdpur402.sorn,:tdpur402.spon,:tdpur402.sseq}
group by alstdpur402.sorn,alstdpur402.spon,alstdpur402.sseq
having count(*) > 1))
selectdo
|#print record
endselect

Good Luck!

Hi Ayoobi

Thanks for your solutions. I am having a problem though. I tried both your options and I get an error when I compile each time saying "Syntax error 'group' not expected" I need to have the group option in don't I otherwise it will crash.

It may be that the synatax in line before group may be incomplete e.g. the closing braces may be missing.

Further the : is not needed in the subquery for tdpur402 fields.

Did you try the proposal of Hitesh Shah:
This should work. You can extend it.
So it would be something like:


select tdpur402.orno, tdpur402,pono, tdpur402.seqn, count(*)
from tdpur402
where tdpur402._index2 inrange { :orno.f, :pono.f, :seqn.f }
and { :orno.t, :pono.t, :seqn.t }
group by tdpur402.orno, tdpur402.pono, tdpur482.seqn
having count(*) > 1
selectdo

endselect

There was a problem in the code again as i did not compile before posting . in the subquery , i had not selected the group field , hence the compilation error. but in a subquery , you cannot select more than one field .
I have changed the code completely and this time i did compile it :

domain tcorno sono.f,sono.t, als.sorn
domain tcpono pono.f,pono.t, seqn.f, seqn.t,als.spon,als.sseq


select alstdpur402.sorn:als.sorn,
alstdpur402.spon:als.spon,
alstdpur402.sseq:als.sseq
from tdpur402 alstdpur402
where alstdpur402._index2 inrange {:sono.f,:pono.f,:seqn.f} and
{:sono.t,:pono.t, :seqn.t}
group by alstdpur402.sorn,alstdpur402.spon,alstdpur402.sseq
having count(*) > 1
selectdo
select tdpur402.*
from tdpur402
where tdpur402._index2 = {:als.sorn,:als.spon,:als.sseq}
selectdo
|#print record
endselect
endselect


Note : As far as i understand correctly , you would like to print all the duplicate records not just sales order number , position and sequence of the duplicate records . otherwise , hitesh's code will work just fine .

Good Luck !!!!

Thanks Ayoobi and guys for helping me out with that problem I had. I managed to get the report working showing all the duplicate entries in the tdpur402 table that have suplicate purchase orders for a sales order.

The new problem is they want me to exclude cancelled orders off the tdpur401 table. So if there were initially 3 entries for a sales order it needs to check if there are any cancelled orders. If there were 2 cancelled then it should not show it on the report. ie, the count must be > 1 still. I am trying to use a nested select statement and I think I would need to run another count and loop it to check for cancelled orders? I hope this is the right idea? Or should I be trying to do this in the report script?

Hi

Sorry for all the questions. Using the code above I need to only include orders that are not cancelled in the tdpur401 table. Could I do the following.

function read.main.table()
{
select tdpur401.*
from tdpur401
where tdpur401._index1 inrange {:orno.f} and
{:orno.t}
and tdpur401.clyn <> tcyesno.yes

selectdo
select alstdpur402.sorn:als.sorn, alstdpur402.spon:als.spon, alstdpur402.sseq:als.sseq
from tdpur402 alstdpur402
where alstdpur402._index1 = {:tdpur401.orno}
group by alstdpur402.sorn,alstdpur402.spon,alstdpur402.sseq
having count(*) > 1
selectdo
select tdpur402.*
from tdpur402
where tdpur402._index2 = {:als.sorn,:als.spon,:als.sseq}
as set with 1 rows
selectdo
rprt_send()
endselect
endselect
endselect

}
My thinking is that by selecting only non cancelled orders initially that the rest of the query will only use that data to run its select statements with? Am I right?

Slight modification is required to the code . I have just added position and sequence in tdpur402 select . This is to ensure that cancelled order lines are not selected again in tdpur402. if you just give order range then those lines would be selected , thus nullifying your efforts in the outermost select.

Also i did not understand the reason for as set with 1 rows in the innermost select . Do u want to print only one of the duplicate records ?
function read.main.table()
{
select tdpur401.*
from tdpur401
where tdpur401._index1 inrange {:orno.f} and
{:orno.t}
and tdpur401.clyn <> tcyesno.yes

selectdo
select alstdpur402.sorn:als.sorn, alstdpur402.spon:als.spon, alstdpur402.sseq:als.sseq
from tdpur402 alstdpur402
where alstdpur402._index1 = {:tdpur401.orno,tdpur401.pono, tdpur401.sqnb}
group by alstdpur402.sorn,alstdpur402.spon,alstdpur402.sseq
having count(*) > 1
selectdo
select tdpur402.*
from tdpur402
where tdpur402._index2 = {:als.sorn,:als.spon,:als.sseq}
as set with 1 rows
selectdo
rprt_send()
endselect
endselect
endselect
}

Good Luck !