Link to home
Start Free TrialLog in
Avatar of 14_east
14_eastFlag for United States of America

asked on

Stuck on SQL case statement...

I have a query that is running perfect.  It is matching table A records to table B records on points of criteria (fields of data) and creates a custom percentage of match score.  Works great... now I have a twist on the straight forward action.  Please see snippet below:

<cfquery name="getMatch">
SELECT case when tbl_lender_states.stateprov_id = <cfqueryparam value="#getJG.a_state#" cfsqltype="cf_sql_integer"> then 18 else 0 end
   + case when tbl_lender_type1.len_repMinLoanRequest <= <cfqueryparam value="#totalLoan#" cfsqltype="cf_sql_integer"> then 18 else 0 end
as matchScore,
tbl_lender.*,tbl_lender_states.*,tbl_lender_businesstypes.*,tbl_lender_loantypes.*<cfif getJG.a_loanType EQ 1>,tbl_lender_type1.*</cfif>
FROM tbl_lender... and so on.

Open in new window


The line in question: {{+ case when tbl_lender_type1.len_repMinLoanRequest <= <cfqueryparam value="#totalLoan#" cfsqltype="cf_sql_integer"> then 18 else 0 end}}
That is checking to see if the stated loan amount from table A falls above the range of table B's minimum loan amount.  What I want to do is say "if it is between, or in the range", I know how to both seperately, but how to write it together?
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 14_east

ASKER

I thought I tried that and it didnt work.  Thanks!