problem with query

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc

Poston Thu, 28 Jul 2005 2:58 pm

Good afternoon Guys!

I have the query below and the following problem: I'm having problems on mooring of query this returning duplicate line and I can't find the problem. Can someone help me?
[color = red]The query is this.[/color]
Code: Select all
select ri.operation_id         "Nº Rec"
      ,ri.invoice_num          "Nota Fiscal"
      ,ri.creation_date        "Emissão"
      ,ri.series               "Serie"
      ,ril.icms_amount         "Valor do ICMS"
      ,ril.ipi_base_amount     "Valor do IPI"
      ,reo.creation_date       "Dt Rec"
      ,reo.status              "Status Nota"
      ,pv.vendor_name          "Fornecedor"
      ,                 "Condição de Pagto"
      ,pha.segment1            "Numero OC"
      ,msib.segment1           "Cod Item"
      ,msit.description        "Descrição"
      ,rfo.cfo_code            "CFO"
      ,ril.uom                 "UN Medida"
      ,ril.quantity            "Qtde"
      ,ril.unit_price          "Preço UN"
      ,ril.total_amount        "Total Nota"
      ,ril.icms_tax            "Alicota ICMS"
from  apps.rec_invoices            ri
      ,apps.rec_invoice_lines       ril
      ,apps.rec_entry_operations    reo
      ,apps.po_vendors              pv
      ,apps.ap_terms                at
      ,apps.rec_fiscal_entities_all rfea
      ,apps.po_vendor_sites_all     pvsa
      ,apps.po_headers_all          pha
      ,apps.po_lines_all            pla
      ,apps.po_line_locations_all   plla
      ,apps.mtl_system_items_b      msib
      ,apps.mtl_system_items_tl     msit
      ,apps.rec_fiscal_operations   rfo
where ri.invoice_id         = ril.invoice_id
  and ri.organization_id    = reo.organization_id
  and ri.operation_id       = reo.operation_id
  and ri.entity_id          = rfea.entity_id
  and ri.terms_id           = at.term_id
  and pvsa.vendor_site_id   = rfea.vendor_site_id
  and pvsa.vendor_id        = pv.vendor_id
  and plla.line_location_id = ril.line_location_id
  and plla.po_header_id     = pha.po_header_id
  and pha.po_header_id      = pla.po_header_id
--  and ri.operation_id       = 100000
--  and ri.organization_id    = 56
  and msib.inventory_item_id = ril.item_id
  and msib.organization_id   = ri.organization_id
  and msit.organization_id   = msib.organization_id
  and msit.inventory_item_id = msib.inventory_item_id
  and msit.language          = 'PTB'
  and rfo.cfo_id             = ril.cfo_id
  and ri.creation_date between to_date('01/01/2005','dd/mm/yyyy')
  and                          to_date('10/01/2005','dd/mm/yyyy')
order by ri.operation_id;
: shock:
Location: sp

/(_)\ Cristiano Carlos - Suporte - TI
"Socialmente justo, economicamente viável e tecnologicamente

Poston Thu, 28 Jul 2005 3:26 pm

It is impossible to tell where the problem is, because we don't know the structure of the tables.

We need to know the existing relationships in each table involved (Primary key). What may be happening is the forgetfulness of a JOIN. And that, we have no way to guess ...:-(
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

Poston Thu, 28 Jul 2005 3:36 pm

you're right friend ... was a relationship stuck even
Location: sp

/(_)\ Cristiano Carlos - Suporte - TI
"Socialmente justo, economicamente viável e tecnologicamente

Poston Thu, 22 May 2008 11:31 am

Smeagoll bah, don't know if you still use the Forum but I have exactly this problem in EBS!! Only failed to mention what is the correct relationship that was going to be a gem!
Location: Canoas RS

Poston Mon, 26 May 2008 12:43 pm

You should note which table this bringing different data for each row, what causes this product ... or try putting a distinct in front of select ... to check a possible lack of relationship you can use \"explan-plain\" in the query, there is a topic explaining how to use it ... Another way to locate which table this entering this product is taking one by one from your last relationship (least significant) and leaving only the Foundation of your query (type tables that you call just to bring a customer name, for example) the way that this is really hard to tell you the exact point that you need to change because we don't have the structures and data from your tables ... abrçs
Location: Batatais - SP

Poston Tue, 27 May 2008 11:45 am

rodfbar these are Oracle Applications tables, in my case the E-Business Suite. For the tables of standard modules of Applications, Oracle provides the information of relationships in the metalink but for the REC module (which is exclusive for Brazil), there's no such thing.

For those who don't know, within Oracle Applications, all keys and constraints are not in the database (ex: user_constraints) but in Setup tables from Oracle (ex: fnd_tables, fnd_primary_keys).

My problem is that precisely because you cannot find the correct relationships among the hundreds of fields that each table has, I haven't been able to find a relationship that is 1 to 1.
Location: Canoas RS

Return to SQL

Who is online

Users browsing this forum: No registered users and 2 guests