rjhunter
asked on
If Based SQL Statement
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#.Cus tomerPK = Customers.CustomerPK
WHERE ***THE PROBLEM LIES HERE**
<cfif #Arguments.TicketStatus# neq "Undefined">AND #Arguments.TicketType#.Tic ketStatus = '#Arguments.TicketStatus#' </cfif>
<cfif #Arguments.ServiceStatus# neq "Undefined">AND #Arguments.TicketType#.Ser viceStatus = '#Arguments.ServiceStatus# '</cfif>
<cfif #Arguments.TicketAssignedT o# neq "Undefined">AND #Arguments.TicketType#.Tic ketAssigne dTo = '#Arguments.TicketAssigned To#'</cfif >
<cfif #Arguments.CustomerName# neq "Undefined">AND #Arguments.TicketType#.Tic ketStatus = '#Arguments.CustomerName#' </cfif>
<cfif #Arguments.TicketID# neq "Undefined">AND #Arguments.TicketType#.Tic ketID = '#Arguments.TicketID#'</cf if>
<cfif #Arguments.PhoneNumber# neq "Undefined">AND Customers.PrimaryPhone = '#Arguments.PhoneNumber#' OR Customers.SecondaryPhone = '#Arguments.PhoneNumber#'< /cfif>
</cfquery>
<cfquery datasource="#Request.DSN#"
SELECT #Arguments.TicketType#.*, Customers.*
FROM #Arguments.TicketType#
INNER JOIN Customers ON #Arguments.TicketType#.Cus
WHERE ***THE PROBLEM LIES HERE**
<cfif #Arguments.TicketStatus# neq "Undefined">AND #Arguments.TicketType#.Tic
<cfif #Arguments.ServiceStatus# neq "Undefined">AND #Arguments.TicketType#.Ser
<cfif #Arguments.TicketAssignedT
<cfif #Arguments.CustomerName# neq "Undefined">AND #Arguments.TicketType#.Tic
<cfif #Arguments.TicketID# neq "Undefined">AND #Arguments.TicketType#.Tic
<cfif #Arguments.PhoneNumber# neq "Undefined">AND Customers.PrimaryPhone = '#Arguments.PhoneNumber#' OR Customers.SecondaryPhone = '#Arguments.PhoneNumber#'<
</cfquery>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Agree with Tim we use the 1=1 all the time.
ASKER
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.