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
  • Deleted
    sábado, 12 de octubre de 2019 13:12