Member Avatar for DGULLIVER

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
  • 238 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by ryantroop
Member Avatar for ryantroop

try

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

Member Avatar for DGULLIVER

Thanks Ryan,

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

Member Avatar for rubberman

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
Member Avatar for DGULLIVER

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

Member Avatar for ryantroop

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

Member Avatar for DGULLIVER

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 because: removed additional issue which was a red hering
Member Avatar for ryantroop

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.