We help IT Professionals succeed at work.

If Based SQL Statement

rjhunter
rjhunter asked
on
Medium Priority
178 Views
Last Modified: 2013-12-24
Alright guys my code is below. The problem is that I would like this component to work reguardless of how many arguments are passed even if there are none.  I cannot say how many arguments are going to be passed and even if any will be. So the problem is where i would place my WHERE SQL Statement. I think you will get what i am saying if you look at my code. The problem with how it currently runs it that the code read "WHERE AND". I mean I guess i could put something in there that will be a definite for all rows in the table but I think there must be a better solution than that.

<cfquery datasource="#Request.DSN#" name="GetTickets">
SELECT #Arguments.TicketType#.*, Customers.*
FROM #Arguments.TicketType#
INNER JOIN Customers ON #Arguments.TicketType#.CustomerPK = Customers.CustomerPK
WHERE ***THE PROBLEM LIES HERE**
<cfif #Arguments.TicketStatus# neq "Undefined">AND #Arguments.TicketType#.TicketStatus = '#Arguments.TicketStatus#'</cfif>
<cfif #Arguments.ServiceStatus# neq "Undefined">AND #Arguments.TicketType#.ServiceStatus = '#Arguments.ServiceStatus#'</cfif>
<cfif #Arguments.TicketAssignedTo# neq "Undefined">AND #Arguments.TicketType#.TicketAssignedTo = '#Arguments.TicketAssignedTo#'</cfif>
<cfif #Arguments.CustomerName# neq "Undefined">AND #Arguments.TicketType#.TicketStatus = '#Arguments.CustomerName#'</cfif>
<cfif #Arguments.TicketID# neq "Undefined">AND #Arguments.TicketType#.TicketID = '#Arguments.TicketID#'</cfif>
<cfif #Arguments.PhoneNumber# neq "Undefined">AND Customers.PrimaryPhone = '#Arguments.PhoneNumber#' OR Customers.SecondaryPhone = '#Arguments.PhoneNumber#'</cfif>
</cfquery>
Comment
Watch Question

Commented:
The easiest way, and the most commonly used way, is to use "Where 1=1" for AND statements or "Where 0=1" if you had OR statements.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Agree with Tim we use the 1=1 all the time.

Author

Commented:
lol awesome. I was hoping for a way that u didnt have to compare 2 values but this works all the same and will be more effiecient then comparing a value in the table. Thanks dude.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.