<cfif to select which query to use

[brian_williamson1]

I need some help getting syntax on an issue.  I have a listbox with 3 values, True(1), False(0) and Needs Approval(NULL) but I can't seem to get the NULL to show. The SQL column has this as a bit value so a Case adjustment does not work.

What I need this to do is if the Needs Approval value is selected in the cboApproved box then use the 'qRecordsNull" query to get the results, else use the "qRecords" query.

Below is the code that I have that works for true and false.  Any help would be greatly appreciated.

<cfset vApproved = 0>
<cfif isDefined("form.cboApproved")>
<cfif form.cboApproved eq "">
  <cfset vApproved = 0>
<cfelse>
  <cfset vApproved = #form.cboApproved#>
</cfif>
<cfelse>
<cfset vApproved = 0>
</cfif> [/code]

<td>
       <select name="cboApproved" class="smallFont">
        <!---<option value="All">All</option>--->
                 <option value="1"
         <cfif isDefined("vApproved")>
          <cfif vApproved eq 1>
            selected
          </cfif>
         </cfif>
        >True</option>
                 <option value="0"
         <cfif isDefined("vApproved")>
          <cfif vApproved eq 0>
            selected
          </cfif>
         </cfif>
        >False</option>
                 <option value= "">Need Approval</option>
       </select>
      </td>

Basically something that looks like this:

<cfif isDefined("form.cboApproved")>
<cfif #form.cboApproved# eq "Needs Approval">
  <cfquery name="qRecordsNull"
<cfelse>
  <cfquery name="qRecords"
</cfif>

[BKBK]

This sounds eerily familiar. I must have responded to it some days ago.

You could do it on 2 separate pages.

formpage.cfm

<form action="actionPage.cfm">
<select name="cboApproved">
  <option value="">Select option</option>
  <option value="1">vApproved</option>
  <option value="0">Not vApproved</option>
  <option value="-1">Needs Approval</option>
</select>
<input type="submit">
</form>

actionPage.cfm

<cfif isDefined("form.cboApproved") and form.cboApproved eq -1>
  <cfquery name="qRecordsNull">
<cfelse>
  <cfquery name="qRecords">
</cfif>

[-__cfSearching__-]

This sounds eerily familiar.

Yes, it does.

The SQL column has this as a bit value so a Case adjustment does not work.

Could you elaborate? Because it seems a bit convoluted. Just wondering if there is a simpler way to go about it.

[Owainnorth]

Deja vu?

Well seeing as the previous thread seems to have been wiped off the face of the earth and started again, I'll add a point I added last time.

If you're doing your queries in-line on the page as it would appear, why the need to name them differently? Why not have the switch/cfif *inside* the cfquery tag, so it's just the results of the query that vary? The idea of having them named differently surely just means you're going to have to duplicate the case logic at the display end? I had a need for something similar the other day, and did something like this:

<cfif client.isManager >

  <cfset qWhatever = application.Managers.get('Staff').getAll() />

<cfelse>

  <cfset qWhatever = application.Managers.get('Staff').getById(client.id) />

</cfif>

That way whatever has happened, you end up with a queryset of the same identified. Unless I missed something in the previous elusive thread of course.

[Dan_Bracuk]

Regarding,  "Well seeing as the previous thread seems to have been wiped off the face of the earth and started again,"

It's still under General Discussions and still on the first page.  It is however, below the bottom edge of your monitor.

This one did not start again.  The OP posted the same question in two forums at about the same time.

Regarding the suggestion of doing the conditional logic inside the query tag, while there is nothing wrong with that, it's not my preference.  My own style is to:

validate inputs

set variables, which includes any necessary conditional logic

run query

do what you need to do afterwards.

In this particular case, I would still have just one query as you suggested.  However, the sql would be a variable.

[BKBK]

Indeed. I did also ask myself whether it was just one query, plus, say, an adjusted where-clause, or two entirely different  queries. Which one is it, Brian?