5 Replies Latest reply on Oct 7, 2008 5:05 AM by fusiondan

    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.
        • 1. Re: Help with CFIF statement
          Dan Bracuk Level 5
          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

          • 2. Help with CFIF statement
            BKBK Adobe Community Professional & MVP
            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

            • 3. Re: Help with CFIF statement
              fusiondan Level 1
              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,
              • 4. Re: Help with CFIF statement
                BKBK Adobe Community Professional & MVP
                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.

                • 5. Re: Help with CFIF statement
                  fusiondan Level 1
                  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>