none
se duplican valores de la consulta RRS feed

  • Pregunta

  • SELECT * FROM (
    SELECT  
    'Pesos' AS MonedaImp,
    A.[PONUMBER] AS OCNum,
            CASE A.[POSTATUS]
               WHEN 1 THEN 'Nuevo'
               WHEN 2 THEN 'Publicado'
               WHEN 3 THEN 'Cambiar Orden'
               WHEN 4 THEN 'Recibido'
               WHEN 5 THEN 'Cerrado'
               WHEN 6 THEN 'Cancelado'
             END AS EstatusOC ,
    POA40003.POA_PO_Approval_Status AS EstadoAprob,
            CASE A.[POTYPE]
               WHEN 1 THEN 'Estandar'
               WHEN 2 THEN 'Dropshipping'
               WHEN 3 THEN 'BLANKET'
               WHEN 4 THEN 'BLANKET DROP-SHIP '
            END AS TipoOC ,
            A.[DOCDATE] AS FechaOC,
            A.[VENDORID] AS IdProveedor,
            A.[VENDNAME] AS NombreProveedor,
    A.PYMTRMID AS TermPago,
            A.[CURNCYID] AS IdMoneda,
    A.XCHGRATE AS TasaCambio,
    A.REQDATE AS FechaRequerida, 
    A.CMPNYNAM AS Ent_NombComp, 
    A.CONTACT AS Ent_Contacto, 
    A.ADDRESS1 AS Ent_DireccLin1, 
    A.ADDRESS2 AS Ent_DireccLin2, 
    A.ADDRESS3 AS Ent_DireccLin3, 
    A.CITY AS Ent_Ciudad, 
    A.STATE AS Ent_Departamento, 
    A.COUNTRY AS Ent_Pais, 
    SUBSTRING(A.PHONE1, 4, 7) AS Ent_Telefono1, 
    A.PHONE2 AS Ent_Telefono2, 
    A.PHONE3 AS Ent_Telefono3, 
    A.PURCHCMPNYNAM AS Prov_Nombre, 
    A.PURCHCONTACT AS Prov_Contacto, 
    A.PURCHADDRESS1 AS Prov_DireccLin1, 
    A.PURCHADDRESS2 AS Prov_DireccLin2, 
    A.PURCHADDRESS3 AS Prov_DireccLin3, 
    A.PURCHCITY AS Prov_Ciudad, 
    A.PURCHSTATE AS Prov_Departamento, 
    A.PURCHCOUNTRY AS Prov_Pais, 
    SUBSTRING(A.PURCHPHONE1, 4, 7) AS Prov_Telefono1, 
    SUBSTRING(A.PURCHPHONE2, 4, 7) AS Prov_Telefono2, 
    SUBSTRING(A.PURCHPHONE3, 4, 7) AS Prov_Telefono3, 
    B.LineNumber AS LineaNum, 
    B.VNDITNUM AS IdArticuloProvd, 
    B.VNDITDSC AS NombArticuloProv, 
    B.UOFM AS UdeM, 
    B.QTYORDER AS Cantidad, 
    B.UNITCOST AS CostoUnit, 
    B.EXTDCOST AS CostoTot,         
    POP10150.CMMTTEXT AS Coment_OC, 
    A.COMMNTID AS ComentLinea, 
    A.SUBTOTAL AS SubTotal, 
    A.TRDISAMT AS Descuento, 
    A.TAXAMNT AS Impuestos, 
    A.FRTAMNT AS Flete, 
    A.MSCCHAMT AS Micelaneos, 
    POP00101.DSCRIPTN AS DesComprador, 
    POA40003.POA_Created_By AS ID_Creador_OC, 
    POA40003.POA_Approved_By AS ID_Aprobado_OC, 
    IV40700.LOCNCODE AS ID_Bodega, 
    IV40700.LOCNDSCR AS Desc_Bodega, 
    B.PRMSHPDTE AS fecent,
    B.ITEMDESC AS Desc_LineaPO


            FROM dbo.POP10100 AS A
            INNER JOIN dbo.POP10110 AS B ON A.[PONUMBER] = B.[PONUMBER]
            LEFT OUTER JOIN dbo.POP10500 AS C ON B.[PONUMBER] = C.[PONUMBER] AND B.[ORD] = C.[POLNENUM]
            LEFT OUTER JOIN dbo.POP30310 AS D ON C.[PONUMBER] = D.[PONUMBER] AND C.[RCPTLNNM] = D.[RCPTLNNM] AND C.POPRCTNM = D.POPRCTNM
            LEFT OUTER JOIN dbo.POP30300 AS E ON D.[POPRCTNM] = E.[POPRCTNM]
    LEFT OUTER JOIN dbo.POA40003 ON B.PONUMBER = POA40003.PONUMBER 
    LEFT OUTER JOIN POP10150 ON B.PONUMBER = POP10150.POPNUMBE
    INNER JOIN POP00101 ON A.BUYERID = POP00101.BUYERID 
    INNER JOIN IV40700 ON B.LOCNCODE = IV40700.LOCNCODE 
    ) PESO
            WHERE PESO.OCNum IN (@OCompra) AND PESO.EstadoAprob='2' AND MonedaImp='Pesos'
    lunes, 7 de octubre de 2019 17:04

Todas las respuestas

  • En que podemos ayudarte?


    SERGIO SANCHEZ ARIAS
    facebook twitter blogger google

    lunes, 7 de octubre de 2019 17:31
  • Hola Luis Rodriguez Nieto:

    SELECT * FROM ( 
    SELECT  
    'Pesos' AS MonedaImp,
    A.[PONUMBER] AS OCNum,
            CASE A.[POSTATUS]
               WHEN 1 THEN 'Nuevo'
               WHEN 2 THEN 'Publicado'
               WHEN 3 THEN 'Cambiar Orden'
               WHEN 4 THEN 'Recibido'
               WHEN 5 THEN 'Cerrado'
               WHEN 6 THEN 'Cancelado'
             END AS EstatusOC ,
    POA40003.POA_PO_Approval_Status AS EstadoAprob,
            CASE A.[POTYPE]
               WHEN 1 THEN 'Estandar'
               WHEN 2 THEN 'Dropshipping'
               WHEN 3 THEN 'BLANKET'
               WHEN 4 THEN 'BLANKET DROP-SHIP '
            END AS TipoOC ,
            A.[DOCDATE] AS FechaOC,
            A.[VENDORID] AS IdProveedor,
            A.[VENDNAME] AS NombreProveedor,
    A.PYMTRMID AS TermPago,
            A.[CURNCYID] AS IdMoneda,
    A.XCHGRATE AS TasaCambio,
    A.REQDATE AS FechaRequerida, 
    A.CMPNYNAM AS Ent_NombComp, 
    A.CONTACT AS Ent_Contacto, 
    A.ADDRESS1 AS Ent_DireccLin1, 
    A.ADDRESS2 AS Ent_DireccLin2, 
    A.ADDRESS3 AS Ent_DireccLin3, 
    A.CITY AS Ent_Ciudad, 
    A.STATE AS Ent_Departamento, 
    A.COUNTRY AS Ent_Pais, 
    SUBSTRING(A.PHONE1, 4, 7) AS Ent_Telefono1, 
    A.PHONE2 AS Ent_Telefono2, 
    A.PHONE3 AS Ent_Telefono3, 
    A.PURCHCMPNYNAM AS Prov_Nombre, 
    A.PURCHCONTACT AS Prov_Contacto, 
    A.PURCHADDRESS1 AS Prov_DireccLin1, 
    A.PURCHADDRESS2 AS Prov_DireccLin2, 
    A.PURCHADDRESS3 AS Prov_DireccLin3, 
    A.PURCHCITY AS Prov_Ciudad, 
    A.PURCHSTATE AS Prov_Departamento, 
    A.PURCHCOUNTRY AS Prov_Pais, 
    SUBSTRING(A.PURCHPHONE1, 4, 7) AS Prov_Telefono1, 
    SUBSTRING(A.PURCHPHONE2, 4, 7) AS Prov_Telefono2, 
    SUBSTRING(A.PURCHPHONE3, 4, 7) AS Prov_Telefono3, 
    B.LineNumber AS LineaNum, 
    B.VNDITNUM AS IdArticuloProvd, 
    B.VNDITDSC AS NombArticuloProv, 
    B.UOFM AS UdeM, 
    B.QTYORDER AS Cantidad, 
    B.UNITCOST AS CostoUnit, 
    B.EXTDCOST AS CostoTot,         
    POP10150.CMMTTEXT AS Coment_OC, 
    A.COMMNTID AS ComentLinea, 
    A.SUBTOTAL AS SubTotal, 
    A.TRDISAMT AS Descuento, 
    A.TAXAMNT AS Impuestos, 
    A.FRTAMNT AS Flete, 
    A.MSCCHAMT AS Micelaneos, 
    POP00101.DSCRIPTN AS DesComprador, 
    POA40003.POA_Created_By AS ID_Creador_OC, 
    POA40003.POA_Approved_By AS ID_Aprobado_OC, 
    IV40700.LOCNCODE AS ID_Bodega, 
    IV40700.LOCNDSCR AS Desc_Bodega, 
    B.PRMSHPDTE AS fecent,
    B.ITEMDESC AS Desc_LineaPO
    
    
            FROM dbo.POP10100 AS A
            INNER JOIN dbo.POP10110 AS B ON A.[PONUMBER] = B.[PONUMBER]
    	   INNER JOIN POP00101 ON A.BUYERID = POP00101.BUYERID 
    	   INNER JOIN IV40700 ON B.LOCNCODE = IV40700.LOCNCODE 
            LEFT OUTER JOIN dbo.POP10500 AS C ON B.[PONUMBER] = C.[PONUMBER] AND B.[ORD] = C.[POLNENUM]
            LEFT OUTER JOIN dbo.POP30310 AS D ON C.[PONUMBER] = D.[PONUMBER] AND C.[RCPTLNNM] = D.[RCPTLNNM] AND C.POPRCTNM = D.POPRCTNM
            LEFT OUTER JOIN dbo.POP30300 AS E ON D.[POPRCTNM] = E.[POPRCTNM]
    	   LEFT OUTER JOIN dbo.POA40003 ON B.PONUMBER = POA40003.PONUMBER 
    	   LEFT OUTER JOIN POP10150 ON B.PONUMBER = POP10150.POPNUMBE
    	  
    	   
    ) PESO
            WHERE PESO.OCNum IN (@OCompra) AND PESO.EstadoAprob='2' AND MonedaImp='Pesos'

    Cambia el orden de los conjuntos porque te pueden dar una sorpresa.

    Si se duplican los registros es porque alguna de las tablas que has marcado tiene una relación de 1 a n.

    Echále un vistazo a este artículo y luego comentas.

    https://javifer2.wordpress.com/2019/09/01/left-join-entender-la-combinacion-de-izquierda-y-no-perder-registros/

    No obstante sin tener las relaciones entre las tablas, o una pequeña muestra de los datos, me parece bastante difícil poder ayudarte.

    lunes, 7 de octubre de 2019 18:03
  • ¿Usted puede añadir una muestra del resultado que contenga las filas repetidas?

    Cuando hay repeticiones de filas, entre las varias posibilidades se encuentra que las uniones no son correctas, faltando añadir columna en algunos ON. Otra posibilidad es que se haya utilizado una tabla que no es necesaria.

     

      

    Si esta respuesta te ayudó a resolver tu problema, recuerda marcarla.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sábado, 12 de octubre de 2019 13:14
    sábado, 12 de octubre de 2019 13:12