problemas al realizar una consulta

05/09/2005 - 22:26 por EDUAR VILLA | Informe spam
muy buenas tardes
tengo el siguiente problema: tengo una vista y un usuario me pidio que le
adicionara un campo que necesita para ver unos usuarios y realizar un
informe y al correr el codigo de la vista en SQL me saca dos errores. El
codigo tiene 2 LEFT en comentarios por que quize enlazar con otras tablas
pero me saca error.

Los mensajes de error que me sacan son:

Servidor: mensaje 107, nivel 16, estado 3, línea 1
The column prefix 'dbo.PRODUCFD' does not match with a table name or alias
name used in the query.
Servidor: mensaje 107, nivel 16, estado 1, línea 1
The column prefix 'dbo.PRODUCFD' does not match with a table name or alias
name used in the query.
Servidor: mensaje 107, nivel 16, estado 1, línea 1
The column prefix 'dbo.PRODUCFD' does not match with a table name or alias
name used in the query.
Servidor: mensaje 107, nivel 16, estado 1, línea 1
The column prefix 'dbo.PRODUCFD' does not match with a table name or alias
name used in the query.

ALTER VIEW dbo.v_ProgPedDES
AS
SELECT
(CASE WHEN N.UFINIREA IS NOT NULL THEN '3'
WHEN PLTIPLIN = '03' AND I.UFINIREA IS NOT NULL THEN '2'
WHEN PLTIPLIN = '02' AND G.UFINIREA IS NOT NULL THEN '1'
WHEN PLTIPLIN = '01' AND I.UFINIREA IS NOT NULL THEN '2'
WHEN PLTIPLIN = '01' AND D.UFINIREA IS NOT NULL THEN '1'
WHEN PLTIPLIN = '03' AND D.UFINIREA IS NOT NULL AND G.UFINIREA IS NOT
NULL THEN '1'
WHEN PLTIPLIN = '01' AND D.UFINIREA IS NULL THEN '0' ELSE '0' END) AS
Estado,
(CASE WHEN UFOPRMAQ = '10' THEN EMNOMBRE END) AS Patronista, ESTE ES
EL CAMPO QUE ESTOY AGREGANDO Y DIGO QUE CUANDO UFOPRMAQ SEA IGUAL A 10
MUESTRE EL NOMBRE DEL PATRONISTA.
ARCLAVE1 AS Referencia,
ARFECING AS Fecha,
ARNOMBRE AS Descrefer,
TRNOMBRE AS DescCliente,
TRAGENTE AS Vendedor,
PLNOMBRE AS Nombre_Linea,
PHCANTID AS Total_Cantidad,
PHFECPED AS Fecha_Pedido,
YEAR(PHFECPED) AS Añofp,
MONTH(PHFECPED) AS Mesfp,
DAY(PHFECPED) AS Diafp,
PHFECFIN AS Fecha_Requerida,
YEAR(PHFECFIN) AS Añofr,
MONTH(PHFECFIN) AS Mesfr,
DAY(PHFECFIN) AS Diafr,
CONVERT (VARCHAR(10), B.UFFINTEO, 111) AS PatronajeFT,
CONVERT (VARCHAR(10), B.UFINIREA, 111) AS PatronajeFR,
CONVERT (VARCHAR(10), C.UFFINTEO, 111) AS FlujoFT,
CONVERT (VARCHAR(10), C.UFINIREA, 111) AS FlujoFR,
CONVERT (VARCHAR(10), D.UFFINTEO, 111) AS CotizacionFT,
CONVERT (VARCHAR(10), D.UFINIREA, 111) AS CotizacionFR,
CONVERT (VARCHAR(10), E.UFFINTEO, 111) AS ExternoFT,
CONVERT (VARCHAR(10), E.UFINIREA, 111) AS ExternoFR,
CONVERT (VARCHAR(10), F.UFFINTEO, 111) AS CorteFT,
CONVERT (VARCHAR(10), F.UFINIREA, 111) AS CorteFR,
CONVERT (VARCHAR(10), G.UFFINTEO, 111) AS ConfeccionFT,
CONVERT (VARCHAR(10), G.UFINIREA, 111) AS ConfeccionFR,
CONVERT (VARCHAR(10), H.UFFINTEO, 111) AS ModelacionFT,
CONVERT (VARCHAR(10), H.UFINIREA, 111) AS ModelacionFR,
CONVERT (VARCHAR(10), I.UFFINTEO, 111) AS AprobacionFT,
CONVERT (VARCHAR(10), I.UFINIREA, 111) AS AprobacionFR,
CONVERT (VARCHAR(10), J.UFFINTEO, 111) AS LiberacionFT,
CONVERT (VARCHAR(10), J.UFINIREA, 111) AS LiberacionFR,
CONVERT (VARCHAR(10), K.UFFINTEO, 111) AS CorteVerFT,
CONVERT (VARCHAR(10), K.UFINIREA, 111) AS CorteVerFR,
CONVERT (VARCHAR(10), L.UFFINTEO, 111) AS ConfeccionVerFT,
CONVERT (VARCHAR(10), L.UFINIREA, 111) AS ConfeccionVerFR,
CONVERT (VARCHAR(10), M.UFFINTEO, 111) AS ModelacionVerFT,
CONVERT (VARCHAR(10), M.UFINIREA, 111) AS ModelacionVerFR,
CONVERT (VARCHAR(10), N.UFFINTEO, 111) AS FichaFT,
CONVERT (VARCHAR(10), N.UFINIREA, 111) AS FichaFR
FROM produchd
LEFT OUTER JOIN ARTICULO A ON A.ARtippro = UHtippro AND A.ARclave1 =
UHclave1
LEFT OUTER JOIN pedidodt ON PDCODEMP = UHCODEMP AND PDTIPPRO = UHTIPPRO AND
PDCLAVE1 = UHCLAVE1
LEFT OUTER JOIN pedidohd ON PHCODEMP = PDCODEMP AND PHPEDIDO = PDPEDIDO
LEFT OUTER JOIN terceros ON TRCODEMP = PHCODEMP AND TRCODTER = PHCODCLI
LEFT OUTER JOIN tbtiplin ON PLCODEMP = PHCODEMP AND PLTIPPED = PHTIPPED AND
PLTIPLIN = PHTIPLIN
LEFT OUTER JOIN PRODUCFD B ON B.UFCODEMP = UHCODEMP AND B.UFNUMERO =
UHNUMERO AND B.UFOPRMAQ = '01'
LEFT OUTER JOIN PRODUCFD C ON C.UFCODEMP = UHCODEMP AND C.UFNUMERO =
UHNUMERO AND C.UFOPRMAQ = '10'
LEFT OUTER JOIN PRODUCFD D ON D.UFCODEMP = UHCODEMP AND D.UFNUMERO =
UHNUMERO AND D.UFOPRMAQ = '20'
LEFT OUTER JOIN PRODUCFD E ON E.UFCODEMP = UHCODEMP AND E.UFNUMERO =
UHNUMERO AND E.UFOPRMAQ = '30'
LEFT OUTER JOIN PRODUCFD F ON F.UFCODEMP = UHCODEMP AND F.UFNUMERO =
UHNUMERO AND F.UFOPRMAQ = '40'
LEFT OUTER JOIN PRODUCFD G ON G.UFCODEMP = UHCODEMP AND G.UFNUMERO =
UHNUMERO AND G.UFOPRMAQ = '50'
LEFT OUTER JOIN PRODUCFD H ON H.UFCODEMP = UHCODEMP AND H.UFNUMERO =
UHNUMERO AND H.UFOPRMAQ = '55'
LEFT OUTER JOIN PRODUCFD I ON I.UFCODEMP = UHCODEMP AND I.UFNUMERO =
UHNUMERO AND I.UFOPRMAQ = '60'
LEFT OUTER JOIN PRODUCFD J ON J.UFCODEMP = UHCODEMP AND J.UFNUMERO =
UHNUMERO AND J.UFOPRMAQ = '65'
LEFT OUTER JOIN PRODUCFD K ON K.UFCODEMP = UHCODEMP AND K.UFNUMERO =
UHNUMERO AND K.UFOPRMAQ = '70'
LEFT OUTER JOIN PRODUCFD L ON L.UFCODEMP = UHCODEMP AND L.UFNUMERO =
UHNUMERO AND L.UFOPRMAQ = '80'
LEFT OUTER JOIN PRODUCFD M ON M.UFCODEMP = UHCODEMP AND M.UFNUMERO =
UHNUMERO AND M.UFOPRMAQ = '85'
LEFT OUTER JOIN PRODUCFD N ON N.UFCODEMP = UHCODEMP AND N.UFNUMERO =
UHNUMERO AND N.UFOPRMAQ = '90'
LEFT OUTER JOIN dbo.PRODUCPR P ON
P.UPCODEMP = dbo.PRODUCFD.UFCODEMP AND
P.UPNRPASO = dbo.PRODUCFD.UFNRPASO AND
P.UPLINNUM = dbo.PRODUCFD.UFLINNUM AND
P.UPNUMERO = dbo.PRODUCFD.UFNUMERO
/*LEFT OUTER JOIN PRODUCPR ON
EMPLEADO.EMCODEMP = PRODUCPR.UPCODEMP AND
EMPLEADO.EMEMPCOD = PRODUCPR.UPCODOPE*/
/*LEFT OUTER JOIN EMPLEADO ON
dbo.PRODUCPR.UPCODEMP = dbo.EMPLEADO.EMCODEMP AND
dbo.PRODUCPR.UPCODOPE = dbo.EMPLEADO.EMEMPCOD */
WHERE uhtippro = 'D' AND PHESTADO <> 'AN' AND PDESTADO <> 'AN'
 

Leer las respuestas

#1 Alejandro Mesa
05/09/2005 - 22:51 | Informe spam
EDUARD,

Estas usando esa tabla multiples veces en la sentencia "select". Pero todas
las referencias tienen asignada un alias y por lo tanto debes indicar a cual
alias te refieres.

LEFT OUTER JOIN dbo.PRODUCPR P ON
P.UPCODEMP = dbo.PRODUCFD.UFCODEMP AND
P.UPNRPASO = dbo.PRODUCFD.UFNRPASO AND
P.UPLINNUM = dbo.PRODUCFD.UFLINNUM AND
P.UPNUMERO = dbo.PRODUCFD.UFNUMERO




AMB

"EDUAR VILLA" wrote:

muy buenas tardes
tengo el siguiente problema: tengo una vista y un usuario me pidio que le
adicionara un campo que necesita para ver unos usuarios y realizar un
informe y al correr el codigo de la vista en SQL me saca dos errores. El
codigo tiene 2 LEFT en comentarios por que quize enlazar con otras tablas
pero me saca error.

Los mensajes de error que me sacan son:

Servidor: mensaje 107, nivel 16, estado 3, línea 1
The column prefix 'dbo.PRODUCFD' does not match with a table name or alias
name used in the query.
Servidor: mensaje 107, nivel 16, estado 1, línea 1
The column prefix 'dbo.PRODUCFD' does not match with a table name or alias
name used in the query.
Servidor: mensaje 107, nivel 16, estado 1, línea 1
The column prefix 'dbo.PRODUCFD' does not match with a table name or alias
name used in the query.
Servidor: mensaje 107, nivel 16, estado 1, línea 1
The column prefix 'dbo.PRODUCFD' does not match with a table name or alias
name used in the query.

ALTER VIEW dbo.v_ProgPedDES
AS
SELECT
(CASE WHEN N.UFINIREA IS NOT NULL THEN '3'
WHEN PLTIPLIN = '03' AND I.UFINIREA IS NOT NULL THEN '2'
WHEN PLTIPLIN = '02' AND G.UFINIREA IS NOT NULL THEN '1'
WHEN PLTIPLIN = '01' AND I.UFINIREA IS NOT NULL THEN '2'
WHEN PLTIPLIN = '01' AND D.UFINIREA IS NOT NULL THEN '1'
WHEN PLTIPLIN = '03' AND D.UFINIREA IS NOT NULL AND G.UFINIREA IS NOT
NULL THEN '1'
WHEN PLTIPLIN = '01' AND D.UFINIREA IS NULL THEN '0' ELSE '0' END) AS
Estado,
(CASE WHEN UFOPRMAQ = '10' THEN EMNOMBRE END) AS Patronista, ESTE ES
EL CAMPO QUE ESTOY AGREGANDO Y DIGO QUE CUANDO UFOPRMAQ SEA IGUAL A 10
MUESTRE EL NOMBRE DEL PATRONISTA.
ARCLAVE1 AS Referencia,
ARFECING AS Fecha,
ARNOMBRE AS Descrefer,
TRNOMBRE AS DescCliente,
TRAGENTE AS Vendedor,
PLNOMBRE AS Nombre_Linea,
PHCANTID AS Total_Cantidad,
PHFECPED AS Fecha_Pedido,
YEAR(PHFECPED) AS Añofp,
MONTH(PHFECPED) AS Mesfp,
DAY(PHFECPED) AS Diafp,
PHFECFIN AS Fecha_Requerida,
YEAR(PHFECFIN) AS Añofr,
MONTH(PHFECFIN) AS Mesfr,
DAY(PHFECFIN) AS Diafr,
CONVERT (VARCHAR(10), B.UFFINTEO, 111) AS PatronajeFT,
CONVERT (VARCHAR(10), B.UFINIREA, 111) AS PatronajeFR,
CONVERT (VARCHAR(10), C.UFFINTEO, 111) AS FlujoFT,
CONVERT (VARCHAR(10), C.UFINIREA, 111) AS FlujoFR,
CONVERT (VARCHAR(10), D.UFFINTEO, 111) AS CotizacionFT,
CONVERT (VARCHAR(10), D.UFINIREA, 111) AS CotizacionFR,
CONVERT (VARCHAR(10), E.UFFINTEO, 111) AS ExternoFT,
CONVERT (VARCHAR(10), E.UFINIREA, 111) AS ExternoFR,
CONVERT (VARCHAR(10), F.UFFINTEO, 111) AS CorteFT,
CONVERT (VARCHAR(10), F.UFINIREA, 111) AS CorteFR,
CONVERT (VARCHAR(10), G.UFFINTEO, 111) AS ConfeccionFT,
CONVERT (VARCHAR(10), G.UFINIREA, 111) AS ConfeccionFR,
CONVERT (VARCHAR(10), H.UFFINTEO, 111) AS ModelacionFT,
CONVERT (VARCHAR(10), H.UFINIREA, 111) AS ModelacionFR,
CONVERT (VARCHAR(10), I.UFFINTEO, 111) AS AprobacionFT,
CONVERT (VARCHAR(10), I.UFINIREA, 111) AS AprobacionFR,
CONVERT (VARCHAR(10), J.UFFINTEO, 111) AS LiberacionFT,
CONVERT (VARCHAR(10), J.UFINIREA, 111) AS LiberacionFR,
CONVERT (VARCHAR(10), K.UFFINTEO, 111) AS CorteVerFT,
CONVERT (VARCHAR(10), K.UFINIREA, 111) AS CorteVerFR,
CONVERT (VARCHAR(10), L.UFFINTEO, 111) AS ConfeccionVerFT,
CONVERT (VARCHAR(10), L.UFINIREA, 111) AS ConfeccionVerFR,
CONVERT (VARCHAR(10), M.UFFINTEO, 111) AS ModelacionVerFT,
CONVERT (VARCHAR(10), M.UFINIREA, 111) AS ModelacionVerFR,
CONVERT (VARCHAR(10), N.UFFINTEO, 111) AS FichaFT,
CONVERT (VARCHAR(10), N.UFINIREA, 111) AS FichaFR
FROM produchd
LEFT OUTER JOIN ARTICULO A ON A.ARtippro = UHtippro AND A.ARclave1 =
UHclave1
LEFT OUTER JOIN pedidodt ON PDCODEMP = UHCODEMP AND PDTIPPRO = UHTIPPRO AND
PDCLAVE1 = UHCLAVE1
LEFT OUTER JOIN pedidohd ON PHCODEMP = PDCODEMP AND PHPEDIDO = PDPEDIDO
LEFT OUTER JOIN terceros ON TRCODEMP = PHCODEMP AND TRCODTER = PHCODCLI
LEFT OUTER JOIN tbtiplin ON PLCODEMP = PHCODEMP AND PLTIPPED = PHTIPPED AND
PLTIPLIN = PHTIPLIN
LEFT OUTER JOIN PRODUCFD B ON B.UFCODEMP = UHCODEMP AND B.UFNUMERO =
UHNUMERO AND B.UFOPRMAQ = '01'
LEFT OUTER JOIN PRODUCFD C ON C.UFCODEMP = UHCODEMP AND C.UFNUMERO =
UHNUMERO AND C.UFOPRMAQ = '10'
LEFT OUTER JOIN PRODUCFD D ON D.UFCODEMP = UHCODEMP AND D.UFNUMERO =
UHNUMERO AND D.UFOPRMAQ = '20'
LEFT OUTER JOIN PRODUCFD E ON E.UFCODEMP = UHCODEMP AND E.UFNUMERO =
UHNUMERO AND E.UFOPRMAQ = '30'
LEFT OUTER JOIN PRODUCFD F ON F.UFCODEMP = UHCODEMP AND F.UFNUMERO =
UHNUMERO AND F.UFOPRMAQ = '40'
LEFT OUTER JOIN PRODUCFD G ON G.UFCODEMP = UHCODEMP AND G.UFNUMERO =
UHNUMERO AND G.UFOPRMAQ = '50'
LEFT OUTER JOIN PRODUCFD H ON H.UFCODEMP = UHCODEMP AND H.UFNUMERO =
UHNUMERO AND H.UFOPRMAQ = '55'
LEFT OUTER JOIN PRODUCFD I ON I.UFCODEMP = UHCODEMP AND I.UFNUMERO =
UHNUMERO AND I.UFOPRMAQ = '60'
LEFT OUTER JOIN PRODUCFD J ON J.UFCODEMP = UHCODEMP AND J.UFNUMERO =
UHNUMERO AND J.UFOPRMAQ = '65'
LEFT OUTER JOIN PRODUCFD K ON K.UFCODEMP = UHCODEMP AND K.UFNUMERO =
UHNUMERO AND K.UFOPRMAQ = '70'
LEFT OUTER JOIN PRODUCFD L ON L.UFCODEMP = UHCODEMP AND L.UFNUMERO =
UHNUMERO AND L.UFOPRMAQ = '80'
LEFT OUTER JOIN PRODUCFD M ON M.UFCODEMP = UHCODEMP AND M.UFNUMERO =
UHNUMERO AND M.UFOPRMAQ = '85'
LEFT OUTER JOIN PRODUCFD N ON N.UFCODEMP = UHCODEMP AND N.UFNUMERO =
UHNUMERO AND N.UFOPRMAQ = '90'
LEFT OUTER JOIN dbo.PRODUCPR P ON
P.UPCODEMP = dbo.PRODUCFD.UFCODEMP AND
P.UPNRPASO = dbo.PRODUCFD.UFNRPASO AND
P.UPLINNUM = dbo.PRODUCFD.UFLINNUM AND
P.UPNUMERO = dbo.PRODUCFD.UFNUMERO
/*LEFT OUTER JOIN PRODUCPR ON
EMPLEADO.EMCODEMP = PRODUCPR.UPCODEMP AND
EMPLEADO.EMEMPCOD = PRODUCPR.UPCODOPE*/
/*LEFT OUTER JOIN EMPLEADO ON
dbo.PRODUCPR.UPCODEMP = dbo.EMPLEADO.EMCODEMP AND
dbo.PRODUCPR.UPCODOPE = dbo.EMPLEADO.EMEMPCOD */
WHERE uhtippro = 'D' AND PHESTADO <> 'AN' AND PDESTADO <> 'AN'






Preguntas similares