0

How to define CASE,Null,and function in a Single variable

Hi,

 

I defined below variable in MV, now I want to create same variable at report level using tables.

Please let me know how to define at report level.

(CASE
                WHEN     POLIN.MATCHING_BASIS = 'QUANTITY
                     AND POHDR.TYPE_LOOKUP_CODE = 'STANDARD''
                THEN
                   NVL (POLIN.QUANTITY, 1) * POLIN.UNIT_PRICE
                WHEN     POLIN.MATCHING_BASIS = 'AMOUNT'
                     AND POHDR.TYPE_LOOKUP_CODE = 'STANDARD'
                THEN
                   POLIN.AMOUNT
                WHEN     POLIN.MATCHING_BASIS = 'QUANTITY'
                     AND POHDR.TYPE_LOOKUP_CODE = 'BLANKET'
                THEN
                   POLIN.UNIT_PRICE * SHIPM.QUANTITY
                ELSE
                   0
             END)
          * NVL (
               PDSTR.RATE,
               CASE POHDR.CURRENCY_CODE
                  WHEN SOB.CURRENCY_CODE THEN 1
                  ELSE NULL
               END)

Regards,

KHK.

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi KHK ,

    If you've defined this field using a SQL-based MV, you should be able to use that field in your reports just like you do any other field from any other table.  Alternatively, you can create a formula column in a business view, in an insight, or on your base table using Incorta's formula functions.  It will have IF, CASE, IFNULL functions you can use to replicate what you have posted here.

    https://docs.incorta.com/4.8/r-formula-functions-all/

    I would also recommend keeping an eye out for our next developer training on learn.incorta.com. The summer public virtual schedule should be posted soon.

    Thanks,

    Dan

    Like
Like Follow
  • 5 mths agoLast active
  • 1Replies
  • 37Views
  • 2 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!