1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Coalesec or nvl or case?

Discussion in 'SQL PL/SQL' started by Durbslaw, Jan 8, 2019.

  1. Durbslaw

    Durbslaw Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    South Africa
    Hi Group,
    I would appreciate help with the following requirement, I have three parameters and if any are not null then the parameters are parsed equivalant to AND bit I only get a result if I use OR

    WITH myCTE
    AS
    (
    SELECT
    po.PO_NO,
    di.Department_No,
    ds.currency_code,
    ds.Supplier_No,
    di.vat_rate_perc,
    te.Tax_Type,
    te.trading_entity_name,
    te.trading_registration_no,
    te.postal_address_line_1,
    te.postal_city_name,
    te.postal_postal_code

    FROM
    FND_RTL_Purchase_Order po
    INNER JOIN
    FND_RTL_SHIPMENT sp
    ON po.PO_NO = sp.PO_NO
    INNER JOIN
    DIM_ITEM di
    ON di.Item_no = po.item_no
    INNER JOIN
    DIM_SUPPLIER ds
    ON ds.supplier_no = po.supplier_no
    INNER JOIN
    FND_TRADING_ENTITY te
    ON ds.trading_entity_code = te.trading_entity_code

    )
    SELECT COUNT(PO_NO) AS PO_Count, PO_NO, Department_No, Supplier_No,currency_code, Tax_type,vat_rate_perc,trading_entity_name,
    trading_registration_no,
    postal_address_line_1,
    postal_city_name,
    postal_postal_code
    FROM myCTE
    WHERE (PO_NO = :pOrder) OR
    (department_no = :pDepartment) OR
    (Supplier_No = :pSupplier)
    GROUP BY PO_NO, Department_No, Supplier_No, Tax_Type,vat_rate_perc,currency_code, trading_entity_name,
    trading_registration_no,
    postal_address_line_1,
    postal_city_name,
    postal_postal_code
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,656
    Likes Received:
    375
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To use AND all of the values that are not null MUST be in the same record or the condition will NOT be met and no data will be returned. OR allows Oracle to find the records that meet one or more of those conditions and, thus, you get results.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    770
    Likes Received:
    148
    Trophy Points:
    830
    Location:
    Russian Federation
    The use of operators AND / or / NOT or functions depends only on your task,and the complexity of the processing logic you need to implement.

    For a start :
    1)The NVL is function ,that calculates both of the arguments before returning of result.
    2) The COALESCE is function,that evaluates arguments sequentially.
    3) The CASE-EXPRESSION