0

How to achieve this decode statement

Hi, 

I want to achieve the below logic in Incorta. 

DECODE(CODE, NULL, NULL, DATE) 

Code - Varchar field; Date- Date field

If Code is null return null else return date.

 

Thanks,

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Decode() can be implemented as a CASE() expression but the expression must return the same data type  so I don't think we support this currently in a Incorta formula expression. If it was via a MV then we could have done a cast(null as date)  in the case expression.

    Reply Like 1
  • decode(CODE,'101','A','102','B','103','C','204','D','206','A','203','A','208','B','24','X',Code)

    How to achieve this in MV

    datatype of  Code is  -  long

    We have implemented as below but getting error . - Amit KothariEmployee

    CASE

    WHEN (

    CAST(CODE AS text) = '101'

    ) THEN 'A'

    WHEN (

    CAST(CODE AS text) = '102'

    ) THEN 'B'

    WHEN (

    CAST(CODE AS text) = '103'

    ) THEN 'C'

    WHEN (

    CAST(CODE AS text) = '204'

    ) THEN 'D'

    WHEN (

    CAST(CODE AS text) = '206'

    ) THEN 'A'

    WHEN (

    CAST(CODE AS text) = '203'

    ) THEN 'A'

    WHEN (

    CAST(CODE AS text) = '208'

    ) THEN 'B'

    WHEN (

    CAST(CODE AS text) = '24'

    ) THEN 'X'

    ELSE CAST(CODE AS text)

    END

    Reply Like
  • Hi Harsh,

    Can you try this query using incorta over incorta postgreSQL connection?

     

    This is how this connection will look like (sample)

    Reply Like
    • Anurag Malik Hi Anurag,

      Are you suggesting to create a separate schema on this Connection and to load table and try this query?

      Moreover we implemented this Decode statement in New formula column using case statement and it was working fine and was generating the appropriate sql (as above). But when we are using it in MV it is throwing error.

      Reply Like
  • Hi Harsh,

    Just asking you to use different data source for the same table.

    In place of MV use Incorta as a data source.

    Reply Like
Like Follow
  • 5 days agoLast active
  • 5Replies
  • 51Views
  • 4 Following