If Based SQL Statement

Posted on 2006-10-23
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
<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>
Question by:rjhunter
LVL 15

Accepted Solution

tim_cs earned 500 total points
ID: 17794157
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.

Expert Comment

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

Author Comment

ID: 17798965
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.

Featured Post

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
php.ini on ipage hosting 12 62
Alias for "domain to path" translation instead of VirtualHost on Apache 3 83
Ways to assess https/ssl websites 3 102
http to https 3 70
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question