If Based SQL Statement

Posted on 2006-10-23
Medium Priority
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 1500 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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This installment of Make It Better gives Media Temple customers the latest news, plugins, and tutorials to make their Grid shared hosting experience that much smoother.
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Through the video, you can check the migration process of Outlook PST file to PDF. Kernel for Outlook to PDF tool can convert Outlook emails with all attributes like Subject, To, From, Cc, Bcc and other folders such as Inbox, Outbox, Sent Items, Jun…

619 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