What's wrong with this SQ Query?

[Former Member]

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

[edy_simon · Accepted solution]

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

[Former Member · Accepted solution]

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]