[Last Call] Learn how to a build a cloud-first strategyRegister Now


performing a query or check on a valuelist

Posted on 2004-11-02
Medium Priority
Last Modified: 2013-12-24
i have the following value list which gets data from a query:

<cfset userinvoices_list = ValueList(Query1.t_current_amount)>

how do i write a query or some other statement that checks if any of the values in this list
are NOT equal to '.00' AND also if the first character of any of the values are NOT equal to '-' ?

(i put quotes around each value because they are text values not numbers)  

so a query that returns zero results would be if the valuelist contained something like:

(.00, .00, .00, .00)

but would return 2 results if the valuelist contained something like:

(2.37, .00, -2.54, .00)
Question by:Judy Deo
  • 2
LVL 17

Accepted Solution

Tacobell777 earned 2000 total points
ID: 12478661
1. to put quotes around the values you use listqualify( list, "'" ) this will put single quotes around the list values
<cfset variables.userinvoices_list = listqualify( variables.userinvoices_list, "'" ) />

2. to return a result that does not contain any of the values you use
SELECT * FROM yourTable WHERE identityOrValueName NOT IN( #variables.userinvoices_list# )

3. to return a result that does contain any of the values you use
SELECT * FROM yourTable WHERE identityOrValueName IN( #variables.userinvoices_list# )

PS. if the values are integers you do not use quotes around the values, i.e. leave out step 1
LVL 17

Expert Comment

ID: 12478668
If you do not want to include any negative numbers you first need to remove these numbers from the list, i.e.
1. you loop over the list
2. check if the value is negative
3. if negative then remove the value from the list

Let me know if you need a example for this?

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month17 days, 20 hours left to enroll

829 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