Okay, I still need help, but I've resolve part of my problem. I'm able now to
have the list of ID numbers in one field by themselves. So, my new field value
(MyColumn) looks like this:
'5,2,3,4,45,7'
I still need a way using Query of Queries to extract only the records that have
the 'idnumber' in that list. I've tried:
SELECT *
FROM AllResults
WHERE #idnumber# in MyColumn
I get an error:
Query Of Queries syntax error.
Encountered "3 in custom2" at line 0, column 0. Incorrect conditional
expression, Expected one of [like|null|between|in|comparison] condition
TIA,
Dave
-----Original Message-----
From: Phillips, Dave
Sent: Tuesday, August 16, 2005 2:16 PM
To: CF-Talk
Subject: SQL help
Hi,
I'm on Oracle 9i. I have a query (call it AllResults) that is returning a
column (call it MyColumn) that's value looks like this:
'identifier|5,2,3,4,45,7'
Each row may have a different 'identifier' and a different quantity of numbers.
Basically, there are two values in this column. identifier & numberlist
separated by a pipe
Now, I need to build a query of queries that queries this existing recordset
and only returns the records which has a number that I'm comparing against. I
know I can do this in CF by looping over the recordset, but I'm trying to avoid
that (for processing time sake).
Here's the pseudo code of what I'm trying to do:
(idnumber is a variable in my code and will be ONE of the numbers listed in the
numberlist)
select * from AllResults WHERE
idnumber is found in the list of numbers located in the MyColumn field
I only want the records that have 'idnumber' IN the list of numbers
I'm sure this can be done with some SQL code and since I'm not a SQL guru, let
alone Oracle, any help or suggestions would be appreciated.
Thanks!
Dave
******************************************************************************************
The information contained in this message, including attachments, may contain
privileged or confidential information that is intended to be delivered only to
the
person identified above. If you are not the intended recipient, or the person
responsible for delivering this message to the intended recipient, ALLTEL
requests
that you immediately notify the sender and asks that you do not read the
message or its
attachments, and that you delete them without copying or sending them to anyone
else.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215299
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
