Consulta SQL

[Former Member]

Hola Buen día compañeros

Tengo un problema en una consulta que he tratado de moverle y corregirla de uno u otro modo pero me sigue marcando el mismo error, espero me puedan apoyar.

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Acuerdo global' (OOAT) (s) could not be prepared.

He probado con substituir la linea 62 con lo siguiente:

WHERE  T2.DocDate '2015/07/20'

y funciona correctamente, lo he metido en store procedure pero me marca que no se puede mandar a llamar por el problema de las subquerys, espero tengan alguna sugerencia.

Saludos Cordiales

SELECT DISTINCT T2.DocNum as '# Entrega',T2.[U_Reposicion],T9.[Block],
        T2.[U_EntregaF] AS 'Entrega Fisica',

                  CASE T2.[DocStatus] 
         WHEN 'O' THEN 'ABIERTO'
         WHEN 'C' THEN 'CERRADO'
        ELSE 'SIN INFORMACION'
      END as 'Status' ,

                 CASE T2.[TrnspCode]
         WHEN '1' THEN 'ESTAFETA'
                               WHEN '2' THEN 'PITIC'
                               WHEN '3' THEN 'MENSAJERIA LOCAL NORTE'
                               WHEN '4' THEN 'NA'
                               WHEN '5' THEN 'MENSAJERIA LOCAL SUR'
                               WHEN '6' THEN 'ALMACEN'
         WHEN '7' THEN 'ESTAFETA DISTRIBUIDOR'
        ELSE 'Sin informacion'
      END ,T4.DocNum as '# Factura',
      T4.DocTotal - T4.VatSum AS 'Monto Factura',
      T7.DocNum as '# NC',
      T7.DocTotal - T7.VatSum AS 'Monto NC',
      T4.PaidSum AS 'Monto Pagado $', 
T4.DocTotal - T4.PaidSum AS 'Monto Pendiente de Pago $',
T7.PaidToDate AS 'Monto Pagado NC y $', 
T7.DocTotal - T7.PaidToDate AS 'Monto Pendiente de Pago NC y $',
T4.DocDate, 
T4.DocDueDate, 
T4.LicTradNum, 
T4.CardName,


 CASE T4.[GroupNum]
         WHEN '1' THEN '30 dias'
                               WHEN '2' THEN 'Pago Inmediato'
                               WHEN '3' THEN '15 Días'
                               WHEN '4' THEN '8 Días'
                               WHEN '5' THEN '75 Días'
                               WHEN '6' THEN '150 Días'
         WHEN '7' THEN '60 DIAS'
                               WHEN '7' THEN 'Pago en parcialidades'
                               WHEN '7' THEN '21 dias'
        ELSE 'SIN INFORMACION'
      END ,
      CASE T4.[DocStatus] 
         WHEN 'O' THEN 'ABIERTO'
         WHEN 'C' THEN 'CERRADO'
        ELSE 'SIN INFORMACION'
      END as 'Status'
                  ,T6.[CardCode]

                  FROM ORDR T0 
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN ODLN T2 ON T2.DocEntry = T1.TrgetEntry
INNER JOIN DLN1 T3 on T3.DocEntry = T2.Docentry
INNER JOIN OINV T4 ON T4.DocEntry = T3.TrgetEntry
INNER JOIN INV1 T5 ON T5.DocEntry = T4.DocEntry
INNER JOIN OCRD T6 ON T6.CardCode = T4.CardCode
INNER JOIN CRD1 T9 ON T9.CardCode = T6.CardCode
LEFT JOIN ORIN T7 ON T7.DocEntry = T5.TrgetEntry
LEFT JOIN RIN1 T8 ON T8.DocEntry = T7.DocEntry
WHERE  T2.DocDate BETWEEN '[%0]' AND '[%1]'

[felipe_loyolarodriguez]

Buenas

Prueba de la siguiente manera:

declare @ini datetime, @fin datetime
set @ini = (/*select top 1 A.RefDate from OINV A where A.DocDate>=*/'[%0]')
set @fin = (/*select top 1 A.RefDate from OINV A where A.DocDate<=*/'[%1]')

SELECT DISTINCT T2.DocNum as '# Entrega',T2.[U_Reposicion],T9.[Block],  
            T2.[U_EntregaF] AS 'Entrega Fisica',  

                      CASE T2.[DocStatus]   
             WHEN 'O' THEN 'ABIERTO'  
             WHEN 'C' THEN 'CERRADO'  
            ELSE 'SIN INFORMACION'  
          END as 'Status' ,  

                     CASE T2.[TrnspCode]  
             WHEN '1' THEN 'ESTAFETA'  
                                   WHEN '2' THEN 'PITIC'  
                                   WHEN '3' THEN 'MENSAJERIA LOCAL NORTE'  
                                   WHEN '4' THEN 'NA'  
                                   WHEN '5' THEN 'MENSAJERIA LOCAL SUR'  
                                   WHEN '6' THEN 'ALMACEN'  
             WHEN '7' THEN 'ESTAFETA DISTRIBUIDOR'  
            ELSE 'Sin informacion'  
          END ,T4.DocNum as '# Factura',  
          T4.DocTotal - T4.VatSum AS 'Monto Factura',  
          T7.DocNum as '# NC',  
          T7.DocTotal - T7.VatSum AS 'Monto NC',  
          T4.PaidSum AS 'Monto Pagado $',   
    T4.DocTotal - T4.PaidSum AS 'Monto Pendiente de Pago $',  
    T7.PaidToDate AS 'Monto Pagado NC y $',   
    T7.DocTotal - T7.PaidToDate AS 'Monto Pendiente de Pago NC y $',  
    T4.DocDate,   
    T4.DocDueDate,   
    T4.LicTradNum,   
    T4.CardName,  
     CASE T4.[GroupNum]  
             WHEN '1' THEN '30 dias'  
                                   WHEN '2' THEN 'Pago Inmediato'  
                                   WHEN '3' THEN '15 Días'  
                                   WHEN '4' THEN '8 Días'  
                                   WHEN '5' THEN '75 Días'  
                                   WHEN '6' THEN '150 Días'  
             WHEN '7' THEN '60 DIAS'  
                                   WHEN '7' THEN 'Pago en parcialidades'  
                                   WHEN '7' THEN '21 dias'  
            ELSE 'SIN INFORMACION'  
          END ,  
          CASE T4.[DocStatus]   
             WHEN 'O' THEN 'ABIERTO'  
             WHEN 'C' THEN 'CERRADO'  
            ELSE 'SIN INFORMACION'  
          END as 'Status'  
                      ,T6.[CardCode]  

                      FROM ORDR T0   
    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry   
    INNER JOIN ODLN T2 ON T2.DocEntry = T1.TrgetEntry  
    INNER JOIN DLN1 T3 on T3.DocEntry = T2.Docentry  
    INNER JOIN OINV T4 ON T4.DocEntry = T3.TrgetEntry  
    INNER JOIN INV1 T5 ON T5.DocEntry = T4.DocEntry  
    INNER JOIN OCRD T6 ON T6.CardCode = T4.CardCode  
    INNER JOIN CRD1 T9 ON T9.CardCode = T6.CardCode  
    LEFT JOIN ORIN T7 ON T7.DocEntry = T5.TrgetEntry  
    LEFT JOIN RIN1 T8 ON T8.DocEntry = T7.DocEntry  
    WHERE  T2.DocDate BETWEEN @ini and @fin

Saludos

[Former Member]

Muchas gracias Felipe efectivamente es como indicas , una ultima pregunta intente integrar unas columnas de los artículos, realmente no se si este correcto funciona la consulta pero no me toma los datos correctos.

La consulta debe mostrar los productos en columnas ya lo hace pero no corresponde a los documentos.

declare @ini datetime, @fin datetime
set @ini = (/*select top 1 A.RefDate from OINV A where A.DocDate>=*/'[%0]')
set @fin = (/*select top 1 A.RefDate from OINV A where A.DocDate<=*/'[%1]')
SELECT DISTINCT T2.DocNum as '# Entrega',T2.[U_Reposicion],T9.[Block] as 'Colonia',  
            T2.[U_EntregaF] AS 'Entrega Fisica',        
  (SELECT ISNULL(SUM(Z.Quantity),0)
      FROM DLN1 Z INNER JOIN ODLN Y ON Y.DocEntry = Z.DocEntry
      WHERE Z.ItemCode = 'P00191'
      AND Z.DocEntry = T0.DocEntry) AS 'PRODUCTO1',
      (SELECT ISNULL(SUM(Z.Quantity),0)
      FROM DLN1 Z INNER JOIN ODLN Y ON Y.DocEntry = Z.DocEntry
      WHERE Z.ItemCode = 'P00002'
      AND Z.DocEntry = T0.DocEntry) AS 'PRODUCTO2',
      (SELECT ISNULL(SUM(Z.Quantity),0)
      FROM DLN1 Z INNER JOIN ODLN Y ON Y.DocEntry = Z.DocEntry
      WHERE Z.ItemCode = 'P00007'
      AND Z.DocEntry = T0.DocEntry) AS 'PRODUCTO3', 
      (SELECT ISNULL(SUM(Z.Quantity),0)
      FROM DLN1 Z INNER JOIN ODLN Y ON Y.DocEntry = Z.DocEntry
      WHERE Z.ItemCode = 'P00003'
      AND Z.DocEntry = T0.DocEntry) AS 'PRODUCTO4',
      (SELECT ISNULL(SUM(Z.Quantity),0)
      FROM DLN1 Z INNER JOIN ODLN Y ON Y.DocEntry = Z.DocEntry
      WHERE Z.ItemCode = 'P00004'
      AND Z.DocEntry = T0.DocEntry) AS 'PRODUCTO5',
      (SELECT ISNULL(SUM(Z.Quantity),0)
      FROM DLN1 Z INNER JOIN ODLN Y ON Y.DocEntry = Z.DocEntry
      WHERE Z.ItemCode = 'P00005'
      AND Z.DocEntry = T0.DocEntry) AS 'PRODUCTO6', 
       T2.[U_EntregaF] AS 'Entrega Fisica',



                      CASE T2.[DocStatus]   
             WHEN 'O' THEN 'ABIERTO'  
             WHEN 'C' THEN 'CERRADO'  
            ELSE 'SIN INFORMACION'  
          END as 'Status' ,  

                     CASE T2.[TrnspCode]  
             WHEN '1' THEN 'ESTAFETA'  
                                   WHEN '2' THEN 'PITIC'  
                                   WHEN '3' THEN 'MENSAJERIA LOCAL NORTE'  
                                   WHEN '4' THEN 'NA'  
                                   WHEN '5' THEN 'MENSAJERIA LOCAL SUR'  
                                   WHEN '6' THEN 'ALMACEN'  
             WHEN '7' THEN 'ESTAFETA DISTRIBUIDOR'  
            ELSE 'Sin informacion'  
          END ,T4.DocNum as '# Factura',  
          T4.DocTotal - T4.VatSum AS 'Monto Factura',  
          T7.DocNum as '# NC',  
          T7.DocTotal - T7.VatSum AS 'Monto NC',  
          T4.PaidSum AS 'Monto Pagado $',   
    T4.DocTotal - T4.PaidSum AS 'Monto Pendiente de Pago $',  
    T7.PaidToDate AS 'Monto Pagado NC y $',   
    T7.DocTotal - T7.PaidToDate AS 'Monto Pendiente de Pago NC y $',  
    T4.DocDate,   
    T4.DocDueDate,   
    T4.LicTradNum,   
    T4.CardName,  
     CASE T4.[GroupNum]  
             WHEN '1' THEN '30 dias'  
                                   WHEN '2' THEN 'Pago Inmediato'  
                                   WHEN '3' THEN '15 Días'  
                                   WHEN '4' THEN '8 Días'  
                                   WHEN '5' THEN '75 Días'  
                                   WHEN '6' THEN '150 Días'  
             WHEN '7' THEN '60 DIAS'  
                                   WHEN '7' THEN 'Pago en parcialidades'  
                                   WHEN '7' THEN '21 dias'  
            ELSE 'SIN INFORMACION'  
          END ,  
          CASE T4.[DocStatus]   
             WHEN 'O' THEN 'ABIERTO'  
             WHEN 'C' THEN 'CERRADO'  
            ELSE 'SIN INFORMACION'  
          END as 'Status'  
                      ,T6.[CardCode]  

                      FROM ORDR T0   
    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry   
    INNER JOIN ODLN T2 ON T2.DocEntry = T1.TrgetEntry  
    INNER JOIN DLN1 T3 on T3.DocEntry = T2.Docentry  
    INNER JOIN OINV T4 ON T4.DocEntry = T3.TrgetEntry  
    INNER JOIN INV1 T5 ON T5.DocEntry = T4.DocEntry  
    INNER JOIN OCRD T6 ON T6.CardCode = T4.CardCode  
    INNER JOIN CRD1 T9 ON T9.CardCode = T6.CardCode  
    LEFT JOIN ORIN T7 ON T7.DocEntry = T5.TrgetEntry  
    LEFT JOIN RIN1 T8 ON T8.DocEntry = T7.DocEntry  
    WHERE  T2.DocDate BETWEEN @ini and @fin