performing a query or check on a valuelist

Posted on 2004-11-02
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:sagardeo
    LVL 17

    Accepted Solution

    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Article by: kevp75
    Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
    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 video discusses moving either the default database or any database to a new volume.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now