Hi,

CREATE PROCEDURE `FindDuplicateExpenses`(IN `strExpenseDate` DATE, IN `strProfile` VARCHAR(50))
    NO SQL
    SQL SECURITY INVOKER
SELECT
   PD.Code,
    PD.ExpenseAuthorizedRefAmount,
    PD.FullName,
    PD.ExpenseDate,
    PD.ExpenseNatureID,
   PD.Ignore_Expense,
   PD.Profile
FROM tblExpenses AS PD,
(SELECT
       Profile,
        ExpenseAuthorizedRefAmount,
        FullName,
        ExpenseDate,
        ExpenseNatureID
    FROM tblExpenses
    GROUP BY Profile, ExpenseAuthorizedRefAmount, FullName, ExpenseDate, ExpenseNatureID
    HAVING COUNT(*) > 1) AS SUB1
WHERE
    PD.ExpenseAuthorizedRefAmount = SUB1.ExpenseAuthorizedRefAmount
AND PD.FullName = SUB1.FullName
AND PD.ExpenseDate = SUB1.ExpenseDate
AND PD.ExpenseNatureID = SUB1.ExpenseNatureID
AND PD.ExpenseAuthorizedRefAmount >0
AND PD.Profile = strProfile
ORDER BY PD.ExpenseAuthorizedRefAmount, PD.FullName

strProfile can be blank, so if it is blank then this AND PD.Profile = strProfile should not be included in the where clause, How do I bypass this?

Cheers
Darren

Recommended Answers

All 7 Replies

try

PD.Profile = ifNull(strProfile, PD.Profile)

Thanks Ryan,

I will give it ago when I get to work in a couple of hours :)

You can also remove line 28 altogether and get rid of that qualification, which is the basic effect of what @ryantroop did is equivalent to what is there now if strProfile is NULL - and remember that on some database systems an empty string is NOT considered NULL. Just get rid of that line, and since you would not be using strProfile any longer, you can eliminate that argument in the "Create Procedure" line.

Thank rubberman.

I tried ryan's approch, it didnt work because the value was an empty string as you suggest rubberman. So I tried:
PD.Profile = if(strProfile = '', PD.Profile = SUB1.Profile, strProfile) Returned zero records when empty string
then I gave PD.Profile = if(strProfile = '', PD.Profile, strProfile) Returned zero records when empty string

I need that qualification because the VB Screen may or may not pass a value.

Cheers,
Darren

Then do an if check before the select that checks if its empty. If it is set the value to null.

Hi Again,

I'm back on this "little" project :)

So I've been mulling over this and I spotted the issue:
PD.Profile = if(strProfile = '', PD.Profile = SUB1.Profile, strProfile)
Should be
PD.Profile = if(strProfile = '', SUB1.Profile, strProfile)

If strProfile is blank it will look like "AND PD.Profile = SUB1.Profile" if not blank then "AND PD.Profile = strProfile".
So now working as it should
Thanks
Darren

No problemo! Glad you figured it out.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.