Skip to Content
author's profile photo Former Member
Former Member

What's wrong with this SQ Query?

When I run it it appears to hang the server... I am waiting the result since more than 2 hours 😔

Select top 10

T0.CardName as "Nombre Cliente",

T0.LicTradNum as "RUT",

T0.DocNum as "Numero Fact.",

T0.PaidToDate as "Anticipo",

T0.DocDate as "Fecha Emision",

T0.doctotal as "Monto Factura Original",

T3.DocNum as "No. Nota Credito",

T3.DocTotal as "Monto NC",

T0.PaidSum as "Monto Final Factura",

T4.SlpName as "Nombre Vendedor",

T5.firstName as "Nombre Originador",

T5.lastName as "Apellido Originador"

From OINV T0, OCRD T1, ORIN T3, OSLP T4, OHEM T5

Order by T0.DocDate desc

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Dec 15, 2014 at 08:12 AM

    Hi Jose,

    You are cross joining the 5 tables, this is a very bad practice.

    Let's say your OINV table have 1000 records, your OCRD table have 100 records,

    this alone will force the server to generate 100 * 1000 = 100,000 records.

    imagine having 5 tables of 100 records each, you will end up with 10000000000 records.

    You need to specify the join of each table .

    ie.

    FROM OINV T0 INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] INNER JOIN OHEM T3 ON T0.[OwnerCode] = T3.[empID] INNER JOIN INV1 T4 ON T0.[DocEntry] = T4.[DocEntry] LEFT JOIN RIN1 T5 ON T4.DocEntry = T5.BaseEntry AND T4.LineNum = T5.BaseLine AND T0.ObjType = T5.BaseType LEFT JOIN ORIN T6 ON T5.[DocEntry] = T6.[DocEntry]

    Regards

    Edy

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Excelente, little change on 'Select':

      Select

      T0.CardName as "Nombre Cliente",

      T0.LicTradNum as "RUT",

      T0.DocNum as "Numero Fact.",

      T0.PaidToDate as "Anticipo",

      T0.DocDate as "Fecha Emision",

      T0.doctotal as "Monto Factura Original",

      T6.DocNum as "No. Nota Credito",

      T6.DocTotal as "Monto NC",

      T0.PaidSum as "Monto Final Factura",

      T2.SlpName as "Nombre Vendedor",

      T3.firstName as "Nombre Originador",

      T3.lastName as "Apellido Originador"

      FROM OINV T0 INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]

      INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode]

      INNER JOIN OHEM T3 ON T0.[OwnerCode] = T3.[empID]

      INNER JOIN INV1 T4 ON T0.[DocEntry] = T4.[DocEntry]

      LEFT JOIN RIN1 T5 ON T4.DocEntry = T5.BaseEntry

      AND T4.LineNum = T5.BaseLine

      AND T0.ObjType = T5.BaseType

      LEFT JOIN ORIN T6 ON T5.[DocEntry] = T6.[DocEntry]

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.