Hi Diogo,

the value of the int64 field is the seconds and it is not null, that field is seconds in unix time. 

What I recommend (don't be angry with me please), is to build that statement line by line, and I think it will be simpler to you to resolve that issue. 

We are here waiting for your feedback 

Regards 

Hi,

The issue arises because OutSystems Advanced SQL has specific syntax rules and restrictions compared to SQL Sandbox or direct database query execution. 

Here’s a simplified and potentially corrected version: 

WITH RankedPedidos AS (

    SELECT DISTINCT

        {Habitacao}.[Id],

        {Morada}.[CP1] + '-' + {Morada}.[CP2] AS CodigoPostal,

        {Habitacao}.[CodigoLocalConsumo],

        {Morada}.[Rua],

        {Habitacao}.[NumPolicia],

        {Habitacao}.[NumFraccao],

        {Habitacao}.[DDLId],

        {TipoPedido}.[Designacao] AS TipoDePedido,

        {Estado}.[Data] AS DataUltimoEstado,

        CASE 

            WHEN {Ilhas}.[CodigoPostal] IS NOT NULL THEN 'Sim' 

            ELSE 'Não' 

        END AS Ilha,

        ROW_NUMBER() OVER (

            PARTITION BY {Habitacao}.[ID]

            ORDER BY 

                CASE 

                    WHEN {TipoPedido}.[ID] = 7 AND {TipoEstado2}.[ID] = 2 AND {Estado}.[Data] >= DATEADD(DAY, -60, GETDATE()) THEN 1

                    WHEN {TipoPedido}.[ID] = 8 AND {TipoEstado2}.[ID] = 2 AND {Estado}.[Data] >= DATEADD(DAY, -180, GETDATE()) THEN 2

                    ELSE 3

                END,

                {Estado}.[Data] DESC

        ) AS RowNum

    FROM 

        {Habitacao}

    LEFT JOIN {Pedido} ON {Habitacao}.[ID] = {Pedido}.[HabitacaoID]

    LEFT JOIN {TipoPedido} ON {Pedido}.[TipoPedidoID] = {TipoPedido}.[ID]

    LEFT JOIN {Estado} ON {Pedido}.[ID] = {Estado}.[PedidoID]

    LEFT JOIN {TipoEstado2} ON {Estado}.[TipoEstado2ID] = {TipoEstado2}.[ID]

    LEFT JOIN {Morada} ON {Habitacao}.[MoradaID] = {Morada}.[ID]

    LEFT JOIN {Ilhas} ON {Morada}.[CP1] + '-' + {Morada}.[CP2] = {Ilhas}.[CodigoPostal] 

                       AND {Habitacao}.[NumPolicia] = {Ilhas}.[NumPolicia]

    WHERE 

        (

            ({Pedido}.[TipoPedidoID] = 7 AND {TipoEstado2}.[ID] = 2 AND {Estado}.[Data] >= DATEADD(DAY, -60, GETDATE()) AND {Habitacao}.[DDLId] = 3)

            OR

            ({Pedido}.[TipoPedidoID] = 8 AND {TipoEstado2}.[ID] = 2 AND {Estado}.[Data] >= DATEADD(DAY, -180, GETDATE()) AND {Habitacao}.[DDLId] = 3)

            OR

            ({Ilhas}.[CodigoPostal] IS NOT NULL AND {Pedido}.[TipoPedidoID] = 7 AND {TipoEstado2}.[ID] = 2 AND {Habitacao}.[DDLId] = 3)

        )

)

SELECT 

    Id,

    CodigoPostal,

    CodigoLocalConsumo,

    Rua,

    NumPolicia,

    NumFraccao,

    DDLId,

    TipoDePedido,

    DataUltimoEstado,

    Ilha

FROM RankedPedidos

WHERE RowNum = 1

ORDER BY TipoDePedido, DataUltimoEstado ASC;

Thank,

Vipin Yadav

HI,

The error "Invalid cast from 'Int64' to 'DateTime'" in OutSystems usually arises because the Advanced SQL query is returning a value that the platform expects as a DateTime but is instead interpreted as a different type (e.g., Int64). 

Check the Data Types in the Aggregate:

• Ensure all fields you’re selecting in the SELECT clause, particularly {Estado}.[Data], are of the correct DateTime data type.
• Verify in the database schema and in OutSystems entity mappings that {Estado}.[Data] is defined as a DateTime column. If it's stored as an integer timestamp (e.g., UNIX timestamp), you'll need to convert it to a DateTime.
  
  
  Example of conversion: 
  
  
  
• SELECT DATEADD(SECOND, CAST({Estado}.[Data] AS BIGINT), '1970-01-01') AS DataUltimoEstado

  
  
  
  Thank, 
  

there are couple of possibilities

1. check your input parameters datatype - one of those assignment is creating this problem

2. check your output structure - one of those assignment is creating this problem

3. comparison between 2 data type is wrong - like  {Estado}.[Data] >= DATEADD(DAY, -60, GETDATE()) 

if {Estado}.[Data] is integer than you will get error

can you provide oml or more detail about the issue so it is easy to understand the problem.

what is {Estado}.[Data] datatype ?

Hi @Diogo Barbosa ,

the backend is the same one right? 
Both statements target the same DB?

Regards