Help with CFIF statement

[fusiondan]

I don't know how advanced this is but I didn't see another relevant place for this question.

I have the following price discount table in SQL Server:

Type Discount Range Expires
P 0.50 ALL 10/30/2008
P 0.10 C100 10/30/2008
P 0.15 C200 10/30/2008

When a user selects a product (i.e. product C200) I want to write code that looks in this table (called PDISCOUNT) and first checks to see if there is a Type of P that has a range of ALL. If so, it applies that discount and that's it. If not, it looks through the rest of the table to see if there is a discount that matches the product number.

The code I've got is resulting in the following scenario:

1. If there is a type of P and a range of ALL it applies the proper discount.
2. If not it will apply the discount if the range equals the product number ONLY IF it is the only product number containing a discount.

Here is my code:

<cfquery name="getALL" datasource="#DSource#" dbtype="ODBC" >
SELECT *
FROM priceDiscounts
WHERE expdate > #Now()# AND type = 'P' AND range = 'ALL'
</cfquery>

<cfquery name="getRANGE" datasource="#DSource#" dbtype="ODBC" >
SELECT *
FROM priceDiscounts
WHERE expdate > #Now()# AND type = 'P' AND range <> 'ALL'
</cfquery>

TEST:
<CFIF getALL.RecordCount neq 0>
<cfoutput>
<cfset sale = #field7# * #getALL.discount#>
<cfset weight = #field7# - #sale#>
<cfset dollars = #ListFirst(weight, ".")#>
<cfset cents = #ListLast(NumberFormat(weight,.99),".")#>
$#val(dollars)#.#cents#
</cfoutput>
<CFELSEIF getRange.RecordCount neq 0 AND getALL.RecordCount eq 0 AND getPage.Location CONTAINS getRange.range>
<cfoutput>
<cfset sale = #field7# * #getRange.discount#>
<cfset weight = #field7# - #sale#>
<cfset dollars = #ListFirst(weight, ".")#>
<cfset cents = #ListLast(NumberFormat(weight,.99),".")#>
$#val(dollars)#.#cents#
</cfoutput>
<CFELSE>
<cfoutput>
<cfset weight = #field7#>
<cfset dollars = #ListFirst(weight, ".")#>
<cfset cents = #ListLast(NumberFormat(weight,.99),".")#>
$#val(dollars)#.#cents#
</cfoutput>
</CFIF>

I am by no means an expert and there must be a more efficient way of doing this as well. Any help would be greatly appreciated.

[Dan_Bracuk]

This is oracle syntax. The general idea might work on your db.

select coalesce (discount2, discount)
from pricediscounts p1 left join (
select discount discount2, productid
from pricediscounts
where expdate > sysdate and type = 'P' and range = 'ALL'
and productid = something
) p2 on p1.productid = p2.productid
where expdate > sysdate and type = 'P' and range = 'ALL'
and productid = something

[BKBK · Accepted solution]

I would do something like the following. Some comments are in order:

1) Make just one trip to the database, and perform the rest of the filtering in memory by means of queries-of-a-query. More efficient.

2) I see no need to display the data in a mutually exclusive way. I have assumed you might wish to display getAll data as well as getRange data.

3) I have left out the pound signs (#) I found unnecessary, and have added the mask "9999.99". If your amounts are up to tens of thousands of dollars, then you will have to modify the mask to "99999.99".



edited: CHARINDEX() removed from query of query

[fusiondan]

Thanks for your reply. I tried your code and I am getting the following error:

Query Of Queries syntax error.
Encountered "CHARINDEX ( range ,. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

[BKBK]

I tried your code and I am getting the following error:

Of course, you did. My bad. I carried the SQL-Server function CHARINDEX() over to Coldfusion QoQ, where it is unknown. I have replaced it in the above code with something more in tune with QoQ.

[fusiondan]

quote:

---

Originally posted by: BKBK
I tried your code and I am getting the following error:

Of course, you did. My bad. I carried the SQL-Server function CHARINDEX() over to Coldfusion QoQ, where it is unknown. I have replaced it in the above code with something more in tune with QoQ.

---

Thanks so much. Your code did the trick. I made one minor adjustment. Here is the final code:

<cfquery name="getPriceDiscounts" datasource="#DSource#">
SELECT *
FROM priceDiscounts
WHERE expdate > #Now()# AND type = 'P'
</cfquery>

<!--- discounts for which range=ALL --->
<cfquery name="getAll" dbtype="query">
SELECT *
FROM getPriceDiscounts
WHERE range = 'ALL'
</cfquery>

<!--- discounts for which range <> ALL and getPage.Location contains range--->
<cfquery name="getRange" dbtype="query">
SELECT *
FROM getPriceDiscounts
WHERE range <> 'ALL' and '#getPage.Location#' like '%'+range+'%'
</cfquery>

<!--- discounts for which range <> ALL and getPage.Location does not contain range--->
<cfquery name="getOutsideRange" dbtype="query">
SELECT *
FROM getPriceDiscounts
WHERE range <> 'ALL' and '#getPage.Location#' not like '%'+range+'%'
</cfquery>

<CFIF getALL.RecordCount neq 0>
<div>
<p><strong>getALL discounts:</strong></p>
<!--- loop across getALL query and display the amounts --->
<cfoutput query="getALL">
<cfset sale = getPage.field7 * getALL.discount>
<cfset weight = getPage.field7 - sale>
<cfset dollars = ListFirst(weight, ".")>
<cfset cents = ListLast(NumberFormat(weight,"9999.99"),".")>
$#val(dollars)#.#cents#<br>
</cfoutput>
</div>
</CFIF>

<CFIF getRange.RecordCount neq 0 AND getALL.RecordCount eq 0>
<div>
<p><strong>getRange discounts:</strong></p>
<!--- loop across getRange query and display the amounts --->
<cfoutput query="getRange">
<cfset sale = getPage.field7 * getRange.discount>
<cfset weight = getPage.field7 - sale>
<cfset dollars = ListFirst(weight, ".")>
<cfset cents = ListLast(NumberFormat(weight,"9999.99"),".")>
$#val(dollars)#.#cents#<br>
</cfoutput>
</div>
</CFIF>

<CFIF getOutsideRange.RecordCount neq 0 AND getALL.RecordCount eq 0 AND getRange.RecordCount eq 0>
<div>
<p><strong>getOutsideRange discounts:</strong></p>
<!--- loop across getOutsideRange query and display the amounts --->
<cfoutput query="getOutsideRange">
<cfset weight = getPage.field7>
<cfset dollars = ListFirst(weight, ".")>
<cfset cents = ListLast(NumberFormat(weight,"9999.99"),".")>
$#val(dollars)#.#cents#<br>
</cfoutput>
</div>
</CFIF>