Remove repition of records

[sandyas]

I have a query which should return records irrespective of the field SHIP_METHOD_ID. The query is

select
"OePo_Item"."ITEM_ID" from
"OePo_Item","OeShipMethod","ArClient","OePo_Header","ApVendor" where
("OePo_Item"."SHIP_METHOD_ID" = "OeShipMethod"."SHIP_METHOD_ID" or
"OePo_Item"."SHIP_METHOD_ID"='' and "OePo_Item"."RCVG_STATUS"='N') and
"OePo_Item"."ITEM_CLIENT_ID" = 'CHOP' and ("OePo_Item"."ITEM_ID" between 'NUR-1435' and
'NUR-1450') and "OePo_Item"."ITEM_CLIENT_ID" = "ArClient"."CLIENT_ID" and
"OePo_Item"."PO_ID" = "OePo_Header"."PO_ID" and "OePo_Item"."VENDOR_ID" =
"ApVendor"."VENDOR_ID" and "OePo_Item"."INVOICED_COUNT"='0' order by
"OePo_Item"."ITEM_ID"

It works fine except that if the SHIP_METHOD_ID field is space, it prints that record many times. How do I get it printed only once.

Thanks

 

[Linda84]

I'm guessing you might have a record with a blank SHIP_METHOD_ID that's joining with other blanks, and it's returning one row for each instance of such a join. You can do SELECT DISTINCT "OePo_Item.ITEM_ID from ...

That will get rid of all duplicates.

Linda
Pervasive Software

 

[sandyas]

I am using duplicate, What it does is removes the repeating records but in some cases it ignores certain records and I am not able to find which records it is missing as the results are huge. It affects the total amount which I am calculating.

How do I go about it.

Thanks