1

I am developing a report in BI Publisher that accepts user input as search criteria and the results are rendered into a pdf template. The user input is used as parameters in the SQL query I have to do the search

I am testing with the following data

PER_FNAME   PER_MNAME   PER_LNAME
Stuart      Easterly    Adams
Stuart      Ronald      Adams
Stuart      Ronald      Adams
Stuart                  Adams
Stuart                  Adams

The results are based on user input and need to conform to the following rules/conditions:

The user will either enter a middle name or leave it blank(null)

  1. If middle name is provided (not null) return the middle name where there is a match AND where the middle name is null

    E.g. If user enters 'Ronald' results should be

    PER_FNAME   PER_MNAME   PER_LNAME
    Stuart      Ronald      Adams
    Stuart      Ronald      Adams
    Stuart                  Adams
    Stuart                  Adams
    
  2. If middle name is null return all middle names values including null

    E.g. if the user enter nothing in the middle name field the results should be

    PER_FNAME   PER_MNAME   PER_LNAME
    Stuart      Easterly    Adams
    Stuart      Ronald      Adams
    Stuart      Ronald      Adams
    Stuart                  Adams
    Stuart                  Adams
    

So far I have the following sql

    SELECT 
      PER_FNAME,
      PER_MNAME,
      PER_LNAME
      FROM
      (SELECT   
        Asset.ASSET_NUM AS BN_REN
        FROM SIEBEL.S_ASSET Asset
        WHERE
        Asset.NAME = 'Business Names'
        AND Asset.status_cd = 'Registered'
      ) Q1

      LEFT JOIN 

     (SELECT 
       Asset.ASSET_NUM AS PER_ASSET,
       Contact.FST_NAME AS PER_FNAME,
       Contact.MID_NAME AS PER_MNAME,
       Contact.LAST_NAME AS PER_LNAME
       FROM siebel.s_asset_con aCon, siebel.s_asset Asset, siebel.s_contact Contact
       WHERE Asset.row_id = aCon.asset_id
       AND aCon.CONTACT_ID = Contact.ROW_ID
     ) Q2

    ON Q1.BN_REN = Q2.PER_ASSET

    WHERE 
      PER_FNAME = NVL(:FIRST_NAME, PER_FNAME)
      AND (PER_MNAME IS NULL OR PER_MNAME = :MID_NAME)
      AND PER_LNAME = NVL(:LAST_NAME, PER_LNAME)

If the user inputs a middle name the results are as expected and I get both null and the right values. However when nothing is entered in for middle name I get only null values not all values.

Is there a way to cater to both conditions in the one sql? and if so how do I go about achieving it?

0 Answers0