CODEWITHSUNDEEP
sqlms-access
German
German

Reputation: 126

SQL Server view not supported in Access DB

This is a view in SQL Server, why is not working in Access DB?

SELECT     
    o.IdOrden, o.FechaOrden, o.NumeroDeOrden, p.RazonSocial, 
    SUM(CASE WHEN r.idtipo = 1 THEN isnull(r.importe, 0) ELSE 0 END) AS [Ch Propio], 
    SUM(CASE WHEN r.idtipo = 2 THEN isnull(r.importe, 0) ELSE 0 END) AS [Ch Tercero], 
    SUM(CASE WHEN r.idtipo = 3 THEN isnull(r.importe, 0) ELSE 0 END) AS Efectivo, 
    SUM(CASE WHEN r.idtipo = 4 THEN isnull(r.importe, 0) ELSE 0 END) AS Retenciones, 
    SUM(CASE WHEN r.idtipo = 5 THEN isnull(r.importe, 0) ELSE 0 END) AS Transferencias, 
    SUM(ISNULL(r.Importe, 0)) AS Total, 
    COUNT(r.Importe) AS [cuenta importe],  
    COUNT(o.IdOrden) AS [cuenta orden], 
    o.IdEmpresa, o.idBeneficiario, o.idUsrAlta, o.iAnulado
FROM 
    dbo.Ordenes AS o 
INNER JOIN
    dbo.Proveedores AS p ON o.idBeneficiario = p.IdProveedor 
INNER JOIN
    dbo.Remesas AS r ON o.IdOrden = r.IdOrden
GROUP BY 
    o.IdOrden, o.FechaOrden, o.NumeroDeOrden, p.RazonSocial, 
    o.IdEmpresa, o.idBeneficiario, o.idUsrAlta, o.iAnulado

EDIT:

OK, I didn't know Access has so different SQL than SQL Server.

I'm recreating a VB 6.0 windows app connected to SQL Server into Access, not linked to SQL server. Al tables imported well, form are easily develop, but there are 2 views I have to rewrite.

EDIT 2:

This still does not work, but only says Error in JOIN

select
  dbo_Ordenes.IdOrden,
  dbo_Ordenes.FechaOrden,
  dbo_Ordenes.NumeroDeOrden,
  dbo_Proveedores.RazonSocial,
  sum(iif(dbo_Remesas.idtipo = 1, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as [Ch Propio],
  sum(iif(dbo_Remesas.idtipo = 2, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as [Ch Tercero],
  sum(iif(dbo_Remesas.idtipo = 3, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Efectivo,
  sum(iif(dbo_Remesas.idtipo = 4, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Retenciones,
  sum(iif(dbo_Remesas.idtipo = 5, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Transferencias,
  sum(iif(dbo_Remesas.Importe is null, 0, dbo_Remesas.Importe)) as Total,
  count(dbo_Remesas.Importe) as [cuenta importe],
  count(dbo_Ordenes.IdOrden) as [cuenta orden],
  dbo_Ordenes.IdEmpresa,
  dbo_Ordenes.idBeneficiario,
  dbo_Ordenes.idUsrAlta,
  dbo_Ordenes.iAnulado
from (
  dbdbo_Ordenes.Ordenes
    inner join dbo_Ordenes.Proveedores
      on dbo_Ordenes.idBeneficiario = dbo_Proveedores.IdProveedor
)
  inner join dbo_Ordenes.Remesas
    on dbo_Ordenes.IdOrden = dbo_Remesas.IdOrden
group by
  dbo_Ordenes.IdOrden,
  dbo_Ordenes.FechaOrden,
  dbo_Ordenes.NumeroDeOrden,
  dbo_Proveedores.RazonSocial,
  dbo_Ordenes.IdEmpresa,
  dbo_Ordenes.idBeneficiario,
  dbo_Ordenes.idUsrAlta,
  dbo_Ordenes.iAnulado

Upvotes: 0

Views: 72

Answers (1)

German
German

Reputation: 126

It finally worked. There was a typo.

Somebody recommended this site for SQL translation: https://www.jooq.org/translate/

select
  dbo_Ordenes.IdOrden,
  dbo_Ordenes.FechaOrden,
  dbo_Ordenes.NumeroDeOrden,
  dbo_Proveedores.RazonSocial,
  sum(iif(dbo_Remesas.idtipo = 1, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as [Ch Propio],
  sum(iif(dbo_Remesas.idtipo = 2, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as [Ch Tercero],
  sum(iif(dbo_Remesas.idtipo = 3, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Efectivo,
  sum(iif(dbo_Remesas.idtipo = 4, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Retenciones,
  sum(iif(dbo_Remesas.idtipo = 5, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Transferencias,
  sum(iif(dbo_Remesas.Importe is null, 0, dbo_Remesas.Importe)) as Total,
  count(dbo_Remesas.Importe) as [cuenta importe],
  count(dbo_Ordenes.IdOrden) as [cuenta orden],
  dbo_Ordenes.IdEmpresa,
  dbo_Ordenes.idBeneficiario,
  dbo_Ordenes.idUsrAlta,
  dbo_Ordenes.iAnulado
FROM (dbo_Ordenes INNER JOIN dbo_Proveedores ON dbo_Ordenes.idBeneficiario = dbo_Proveedores.IdProveedor) INNER JOIN dbo_Remesas ON dbo_Ordenes.IdOrden = dbo_Remesas.IdOrden
group by
  dbo_Ordenes.IdOrden,
  dbo_Ordenes.FechaOrden,
  dbo_Ordenes.NumeroDeOrden,
  dbo_Proveedores.RazonSocial,
  dbo_Ordenes.IdEmpresa,
  dbo_Ordenes.idBeneficiario,
  dbo_Ordenes.idUsrAlta,
  dbo_Ordenes.iAnulado

Upvotes: 1

Related Questions