Consulta para 2005

Hola a todos, tengo el siguiente query, y necesito cambiar los operadores *=
por LEFT OUTER JOINS, ya lo intente pero la consulta original dura 30 seg. y
reemplazando los operadores *= por left joins, despues de 3 minutos tengo que
cancelarla.
Alguien me puede ayudar? Mil gracias.

SELECT A.BUSINESS_UNIT ,
A.ORDER_NO ,
G.ORDER_DATE ,
A.ORDER_INT_LINE_NO ,
A.SCHED_LINE_NBR ,
A.SHIP_TO_CUST_ID ,
E.NAME1 ,
C.PRODUCT_ID ,
A.QTY_SCHEDULED ,
C.UNIT_OF_MEASURE ,
B.PO_ID ,
B.LINE_NBR ,
B.QTY_PO ,
D.VOUCHER_ID ,
D.VOUCHER_LINE_NUM ,
D.QTY_VCHR ,
F.INVOICE ,
F.INVOICE_LINE ,
F.ADD_DTTM ,
F.QTY
FROM PS_ORD_SCHEDULE A WITH (NOLOCK) ,
PS_PO_LINE_SHIP B WITH (NOLOCK) ,
PS_ORD_LINE C WITH (NOLOCK) ,
PS_VOUCHER_LINE D WITH (NOLOCK) ,
PS_CUSTOMER E WITH (NOLOCK) ,
PS_BI_LINE F WITH (NOLOCK) ,
PS_ORD_HEADER G WITH (NOLOCK)
WHERE A.BUSINESS_UNIT LIKE 'CRS%' AND
A.BUSINESS_UNIT <> 'CRS80' AND
A.DROP_SHIP_FLAG = 'Y' AND
A.ORD_SCH_STATUS <> 'X' AND
A.DATETIME_ADDED >= '2005-06-01' AND
A.BUSINESS_UNIT_PO *= B.BUSINESS_UNIT AND
A.PO_LINE_NBR *= B.LINE_NBR AND
A.PO_ID *= B.PO_ID AND
A.PO_SCHED_NBR *= B.SCHED_NBR AND
A.ORDER_NO *= B.ORDER_NO AND
A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
A.ORDER_NO *= B.ORDER_NO AND
A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
A.BUSINESS_UNIT = C.BUSINESS_UNIT AND
A.ORDER_NO = C.ORDER_NO )AND
A.ORDER_INT_LINE_NO = C.ORDER_INT_LINE_NO AND
D.BUSINESS_UNIT = 'CRA01' AND D.PO_ID <> '' AND
D.BUSINESS_UNIT_PO <> '' AND A.PO_ID *=D.PO_ID AND
A.PO_LINE_NBR *= D.LINE_NBR AND
A.BUSINESS_UNIT_PO *= D.BUSINESS_UNIT_PO AND
E.CUST_ID = A.SHIP_TO_CUST_ID AND E.SETID = 'CRE01' AND
A.BUSINESS_UNIT *= F.BUSINESS_UNIT AND
A.ORDER_NO *= F.ORDER_NO AND
A.ORDER_INT_LINE_NO *= F.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= F.SCHED_LINE_NBR AND
F.BUSINESS_UNIT <> '' AND F.INVOICE <> '' AND
G.BUSINESS_UNIT = C.BUSINESS_UNIT AND
G.ORDER_NO = C.ORDER_NO

Hola.

¿Puedes pasarnos el plan de ejecución de la consulta?

Alberto López Grande

qwalgrande


"Carlos A." wrote:

> Hola a todos, tengo el siguiente query, y necesito cambiar los operadores *=
> por LEFT OUTER JOINS, ya lo intente pero la consulta original dura 30 seg. y
> reemplazando los operadores *= por left joins, despues de 3 minutos tengo que
> cancelarla.
> Alguien me puede ayudar? Mil gracias.
>
> SELECT A.BUSINESS_UNIT ,
> A.ORDER_NO ,
> G.ORDER_DATE ,
> A.ORDER_INT_LINE_NO ,
> A.SCHED_LINE_NBR ,
> A.SHIP_TO_CUST_ID ,
> E.NAME1 ,
> C.PRODUCT_ID ,
> A.QTY_SCHEDULED ,
> C.UNIT_OF_MEASURE ,
> B.PO_ID ,
> B.LINE_NBR ,
> B.QTY_PO ,
> D.VOUCHER_ID ,
> D.VOUCHER_LINE_NUM ,
> D.QTY_VCHR ,
> F.INVOICE ,
> F.INVOICE_LINE ,
> F.ADD_DTTM ,
> F.QTY
> FROM PS_ORD_SCHEDULE A WITH (NOLOCK) ,
> PS_PO_LINE_SHIP B WITH (NOLOCK) ,
> PS_ORD_LINE C WITH (NOLOCK) ,
> PS_VOUCHER_LINE D WITH (NOLOCK) ,
> PS_CUSTOMER E WITH (NOLOCK) ,
> PS_BI_LINE F WITH (NOLOCK) ,
> PS_ORD_HEADER G WITH (NOLOCK)
> WHERE A.BUSINESS_UNIT LIKE 'CRS%' AND
> A.BUSINESS_UNIT <> 'CRS80' AND
> A.DROP_SHIP_FLAG = 'Y' AND
> A.ORD_SCH_STATUS <> 'X' AND
> A.DATETIME_ADDED >= '2005-06-01' AND
> A.BUSINESS_UNIT_PO *= B.BUSINESS_UNIT AND
> A.PO_LINE_NBR *= B.LINE_NBR AND
> A.PO_ID *= B.PO_ID AND
> A.PO_SCHED_NBR *= B.SCHED_NBR AND
> A.ORDER_NO *= B.ORDER_NO AND
> A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
> A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
> A.ORDER_NO *= B.ORDER_NO AND
> A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
> A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
> A.BUSINESS_UNIT = C.BUSINESS_UNIT AND
> A.ORDER_NO = C.ORDER_NO )AND
> A.ORDER_INT_LINE_NO = C.ORDER_INT_LINE_NO AND
> D.BUSINESS_UNIT = 'CRA01' AND D.PO_ID <> '' AND
> D.BUSINESS_UNIT_PO <> '' AND A.PO_ID *=D.PO_ID AND
> A.PO_LINE_NBR *= D.LINE_NBR AND
> A.BUSINESS_UNIT_PO *= D.BUSINESS_UNIT_PO AND
> E.CUST_ID = A.SHIP_TO_CUST_ID AND E.SETID = 'CRE01' AND
> A.BUSINESS_UNIT *= F.BUSINESS_UNIT AND
> A.ORDER_NO *= F.ORDER_NO AND
> A.ORDER_INT_LINE_NO *= F.ORDER_INT_LINE_NO AND
> A.SCHED_LINE_NBR *= F.SCHED_LINE_NBR AND
> F.BUSINESS_UNIT <> '' AND F.INVOICE <> '' AND
> G.BUSINESS_UNIT = C.BUSINESS_UNIT AND
> G.ORDER_NO = C.ORDER_NO
>

Hola a todos, tengo el siguiente query, y necesito cambiar los operadores *=
por LEFT OUTER JOINS, ya lo intente pero la consulta original dura 30 seg. y
reemplazando los operadores *= por left joins, despues de 3 minutos tengo que
cancelarla.
Alguien me puede ayudar? Mil gracias.

SELECT A.BUSINESS_UNIT ,
A.ORDER_NO ,
G.ORDER_DATE ,
A.ORDER_INT_LINE_NO ,
A.SCHED_LINE_NBR ,
A.SHIP_TO_CUST_ID ,
E.NAME1 ,
C.PRODUCT_ID ,
A.QTY_SCHEDULED ,
C.UNIT_OF_MEASURE ,
B.PO_ID ,
B.LINE_NBR ,
B.QTY_PO ,
D.VOUCHER_ID ,
D.VOUCHER_LINE_NUM ,
D.QTY_VCHR ,
F.INVOICE ,
F.INVOICE_LINE ,
F.ADD_DTTM ,
F.QTY
FROM PS_ORD_SCHEDULE A WITH (NOLOCK) ,
PS_PO_LINE_SHIP B WITH (NOLOCK) ,
PS_ORD_LINE C WITH (NOLOCK) ,
PS_VOUCHER_LINE D WITH (NOLOCK) ,
PS_CUSTOMER E WITH (NOLOCK) ,
PS_BI_LINE F WITH (NOLOCK) ,
PS_ORD_HEADER G WITH (NOLOCK)
WHERE A.BUSINESS_UNIT LIKE 'CRS%' AND
A.BUSINESS_UNIT <> 'CRS80' AND
A.DROP_SHIP_FLAG = 'Y' AND
A.ORD_SCH_STATUS <> 'X' AND
A.DATETIME_ADDED >= '2005-06-01' AND
A.BUSINESS_UNIT_PO *= B.BUSINESS_UNIT AND
A.PO_LINE_NBR *= B.LINE_NBR AND
A.PO_ID *= B.PO_ID AND
A.PO_SCHED_NBR *= B.SCHED_NBR AND
A.ORDER_NO *= B.ORDER_NO AND
A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
A.ORDER_NO *= B.ORDER_NO AND
A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
A.BUSINESS_UNIT = C.BUSINESS_UNIT AND
A.ORDER_NO = C.ORDER_NO )AND
A.ORDER_INT_LINE_NO = C.ORDER_INT_LINE_NO AND
D.BUSINESS_UNIT = 'CRA01' AND D.PO_ID <> '' AND
D.BUSINESS_UNIT_PO <> '' AND A.PO_ID *=D.PO_ID AND
A.PO_LINE_NBR *= D.LINE_NBR AND
A.BUSINESS_UNIT_PO *= D.BUSINESS_UNIT_PO AND
E.CUST_ID = A.SHIP_TO_CUST_ID AND E.SETID = 'CRE01' AND
A.BUSINESS_UNIT *= F.BUSINESS_UNIT AND
A.ORDER_NO *= F.ORDER_NO AND
A.ORDER_INT_LINE_NO *= F.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR *= F.SCHED_LINE_NBR AND
F.BUSINESS_UNIT <> '' AND F.INVOICE <> '' AND
G.BUSINESS_UNIT = C.BUSINESS_UNIT AND
G.ORDER_NO = C.ORDER_NO

Carlos A.,

Trata:

...
FROM
PS_ORD_SCHEDULE A WITH (NOLOCK)
INNER JOIN
PS_ORD_LINE C WITH (NOLOCK)
ON A.BUSINESS_UNIT = C.BUSINESS_UNIT AND
A.ORDER_NO = C.ORDER_NO )AND
A.ORDER_INT_LINE_NO = C.ORDER_INT_LINE_NO
INNER JOIN
PS_CUSTOMER E WITH (NOLOCK)
ON E.CUST_ID = A.SHIP_TO_CUST_ID
INNER JOIN
PS_ORD_HEADER G WITH (NOLOCK)
ON G.BUSINESS_UNIT = C.BUSINESS_UNIT AND
G.ORDER_NO = C.ORDER_NO
LEFT JOIN
PS_PO_LINE_SHIP B WITH (NOLOCK)
ON A.BUSINESS_UNIT_PO = B.BUSINESS_UNIT AND
A.PO_LINE_NBR = B.LINE_NBR AND
A.PO_ID = B.PO_ID AND
A.PO_SCHED_NBR = B.SCHED_NBR AND
A.ORDER_NO = B.ORDER_NO AND
A.ORDER_INT_LINE_NO = B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR = B.SCHED_LINE_NBR AND
A.ORDER_NO = B.ORDER_NO AND
A.ORDER_INT_LINE_NO = B.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR = B.SCHED_LINE_NBR
LEFT JOIN
PS_VOUCHER_LINE D WITH (NOLOCK)
ON A.PO_ID =D.PO_ID AND
A.PO_LINE_NBR = D.LINE_NBR AND
A.BUSINESS_UNIT_PO = D.BUSINESS_UNIT_PO AND
LEFT JOIN
PS_BI_LINE F WITH (NOLOCK)
ON A.BUSINESS_UNIT = F.BUSINESS_UNIT AND
A.ORDER_NO = F.ORDER_NO AND
A.ORDER_INT_LINE_NO = F.ORDER_INT_LINE_NO AND
A.SCHED_LINE_NBR = F.SCHED_LINE_NBR
WHERE A.BUSINESS_UNIT LIKE 'CRS%' AND
A.BUSINESS_UNIT <> 'CRS80' AND
A.DROP_SHIP_FLAG = 'Y' AND
A.ORD_SCH_STATUS <> 'X' AND
A.DATETIME_ADDED >= '2005-06-01' AND
D.BUSINESS_UNIT = 'CRA01' AND
D.PO_ID <> '' AND
D.BUSINESS_UNIT_PO <> '' AND
E.SETID = 'CRE01' AND
F.BUSINESS_UNIT <> '' AND
F.INVOICE <> ''


Si algunas de las expresiones que deje en la clausula WHERE, estan supuestas
a participar en alguna de las operaciones LEFT JOIN, entonces muevela al
lugar que le corresponde.


AMB


"Carlos A." wrote:

> Hola a todos, tengo el siguiente query, y necesito cambiar los operadores *=
> por LEFT OUTER JOINS, ya lo intente pero la consulta original dura 30 seg. y
> reemplazando los operadores *= por left joins, despues de 3 minutos tengo que
> cancelarla.
> Alguien me puede ayudar? Mil gracias.
>
> SELECT A.BUSINESS_UNIT ,
> A.ORDER_NO ,
> G.ORDER_DATE ,
> A.ORDER_INT_LINE_NO ,
> A.SCHED_LINE_NBR ,
> A.SHIP_TO_CUST_ID ,
> E.NAME1 ,
> C.PRODUCT_ID ,
> A.QTY_SCHEDULED ,
> C.UNIT_OF_MEASURE ,
> B.PO_ID ,
> B.LINE_NBR ,
> B.QTY_PO ,
> D.VOUCHER_ID ,
> D.VOUCHER_LINE_NUM ,
> D.QTY_VCHR ,
> F.INVOICE ,
> F.INVOICE_LINE ,
> F.ADD_DTTM ,
> F.QTY
> FROM PS_ORD_SCHEDULE A WITH (NOLOCK) ,
> PS_PO_LINE_SHIP B WITH (NOLOCK) ,
> PS_ORD_LINE C WITH (NOLOCK) ,
> PS_VOUCHER_LINE D WITH (NOLOCK) ,
> PS_CUSTOMER E WITH (NOLOCK) ,
> PS_BI_LINE F WITH (NOLOCK) ,
> PS_ORD_HEADER G WITH (NOLOCK)
> WHERE A.BUSINESS_UNIT LIKE 'CRS%' AND
> A.BUSINESS_UNIT <> 'CRS80' AND
> A.DROP_SHIP_FLAG = 'Y' AND
> A.ORD_SCH_STATUS <> 'X' AND
> A.DATETIME_ADDED >= '2005-06-01' AND
> A.BUSINESS_UNIT_PO *= B.BUSINESS_UNIT AND
> A.PO_LINE_NBR *= B.LINE_NBR AND
> A.PO_ID *= B.PO_ID AND
> A.PO_SCHED_NBR *= B.SCHED_NBR AND
> A.ORDER_NO *= B.ORDER_NO AND
> A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
> A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
> A.ORDER_NO *= B.ORDER_NO AND
> A.ORDER_INT_LINE_NO *= B.ORDER_INT_LINE_NO AND
> A.SCHED_LINE_NBR *= B.SCHED_LINE_NBR AND
> A.BUSINESS_UNIT = C.BUSINESS_UNIT AND
> A.ORDER_NO = C.ORDER_NO )AND
> A.ORDER_INT_LINE_NO = C.ORDER_INT_LINE_NO AND
> D.BUSINESS_UNIT = 'CRA01' AND D.PO_ID <> '' AND
> D.BUSINESS_UNIT_PO <> '' AND A.PO_ID *=D.PO_ID AND
> A.PO_LINE_NBR *= D.LINE_NBR AND
> A.BUSINESS_UNIT_PO *= D.BUSINESS_UNIT_PO AND
> E.CUST_ID = A.SHIP_TO_CUST_ID AND E.SETID = 'CRE01' AND
> A.BUSINESS_UNIT *= F.BUSINESS_UNIT AND
> A.ORDER_NO *= F.ORDER_NO AND
> A.ORDER_INT_LINE_NO *= F.ORDER_INT_LINE_NO AND
> A.SCHED_LINE_NBR *= F.SCHED_LINE_NBR AND
> F.BUSINESS_UNIT <> '' AND F.INVOICE <> '' AND
> G.BUSINESS_UNIT = C.BUSINESS_UNIT AND
> G.ORDER_NO = C.ORDER_NO
>