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)
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
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?