0

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

Edited by DGULLIVER

3
Contributors
7
Replies
29
Views
1 Year
Discussion Span
Last Post by ryantroop
0

Thanks Ryan,

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

0

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.

Edited by rubberman

0

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

0

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

Edited by DGULLIVER: removed additional issue which was a red hering

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.