[cfaussie] Re: Someone help with this bit of code?

[Dale Fraser]

Dam I hate shitty queries,

Where's the inner join?

        <cfquery datasource="#DSN#" name="qMyDiscountApprovals">
                SELECT  *
                FROM            Bookings
                INNER JOIN      BookingItems ON BO_ID = BI_BookingID
                WHERE   BO_DeleteDate IS NULL
                AND             BI_ParentItem IS NULL
                <cfif Client.Authenticate eq 2>
                        AND     BO_Discount >= 0.03
                <cfelseif Client.Authenticate eq 3>
                        AND     BO_Discount >= 0.03 AND BO_Discount <= 0.07
                </cfif>
                AND             BO_DiscountApproval is NULL
                ORDER BY        BO_ID
        </cfquery>

Regards
Dale Fraser

http://dale.fraser.id.au


 

-----Original Message-----
From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf
Of Andrew Scott
Sent: Tuesday, 5 September 2006 11:47 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Someone help with this bit of code?


Ok,

Help, anyone who has better SQL than me can you see why this query is not
working?

        <cfquery datasource="#DSN#" name="qMyDiscountApprovals">
                SELECT *
                FROM Bookings, BookingItems
                WHERE BO_DeleteDate IS NULL
                AND BO_ID = BI_BookingID
                AND BI_ParentItem IS NULL
                <cfif Client.Authenticate eq 2>
                        and BO_Discount >= 0.03
                <cfelseif Client.Authenticate eq 3>
                        and BO_Discount >= 0.03 AND BO_Discount <= 0.07
                </cfif>
                and BO_DiscountApproval is NULL
        </cfquery>

And here is the code it is supposed to replace

<cfoutput query="BookingInfo" group="BO_ID">
        <!--- Search for Discounts that need approval --->
        <cfif BO_Discount GTE 0.03 AND Client.Authenticate EQ 2 AND
BO_DiscountApproval EQ "">
                <cfset myDiscountApprovals = myDiscountApprovals + 1>
        <cfelseif BO_Discount GTE 0.03 AND BO_Discount LTE 0.07 AND
Client.Authenticate EQ "3" AND BO_DiscountApproval EQ "">
                <cfset myDiscountApprovals = myDiscountApprovals + 1>
        </cfif>
</cfoutput>

Now the above code returns 23 and the actual query above returns 73, I know
it has something to do with the group="bo_id" just can't work it out?

 
Senior Coldfusion Developer
Aegeon Pty. Ltd.
www.aegeon.com.au
Phone: +613  8676 4223
Mobile: 0404 998 273
 





--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups 
"cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cfaussie
-~----------~----~----~----~------~----~------~--~---