Re: SOT: Oracle Temp Table
It sounds like a job for a PL/SQL stored proc. If you post the CF code I'd be happy to have a crack at the PL/SQL equivalent. On 1/17/06, Ian Skinner <[EMAIL PROTECTED]> wrote: > PS. I came up with a way to due it in CF. I loop over the list doing a > query of a query against a query of the second table. I then dynamically > build a query object combining the list and query of query results. > > It works, but I would still be interested in an Oracle "Internal" solution if > one exists. -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229743 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SOT: Oracle Temp Table
I'm not sure if this is remotely what you want, but have you checked out querysim? It just seemed vaguely like what you're looking for even though I've missed this entire conversation. http://www.cflib.org/udf.cfm?ID=255 Mike Not for this issue, because I can't outer join a CF created query with another query. Query of Query does not support outer joins! Oh I hope that comes soon. I run across the need for a Query of Query outer join three or four times a year. And it is always such a pain to work around. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229711 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
Re: SOT: Oracle Temp Table
I'm not sure if this is remotely what you want, but have you checked out querysim? It just seemed vaguely like what you're looking for even though I've missed this entire conversation. http://www.cflib.org/udf.cfm?ID=255 Mike Ian Skinner wrote: > select 'red', 'green', 'blue' from dual > > I'm wondering if that will work for you, just as a subquery for your join.. > > Won't that create three columns named "red", "green" and "blue?" What I need > is a single column "Color" with three rows each with a value of one of the > list items so that I can outer join that record set against the other record > set. > > PS. I came up with a way to due it in CF. I loop over the list doing a > query of a query against a query of the second table. I then dynamically > build a query object combining the list and query of query results. > > It works, but I would still be interested in an Oracle "Internal" solution if > one exists. > > > -- > Ian Skinner > Web Programmer > BloodSource > www.BloodSource.org > Sacramento, CA > > "C code. C code run. Run code run. Please!" > - Cynthia Dunning > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229708 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SOT: Oracle Temp Table
select 'red', 'green', 'blue' from dual I'm wondering if that will work for you, just as a subquery for your join.. Won't that create three columns named "red", "green" and "blue?" What I need is a single column "Color" with three rows each with a value of one of the list items so that I can outer join that record set against the other record set. PS. I came up with a way to due it in CF. I loop over the list doing a query of a query against a query of the second table. I then dynamically build a query object combining the list and query of query results. It works, but I would still be interested in an Oracle "Internal" solution if one exists. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229707 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOT: Oracle Temp Table
select 'red', 'green', 'blue' from dual I'm wondering if that will work for you, just as a subquery for your join.. Ian Skinner wrote: >Are there any Oracle knowledgeable people online today? > >What is the easiest way to create a temporary view or table to contain a list >of values that I currently have in a ColdFusion variable? > >In other words, how can I turn a list like this: "Red,Green,Blue"> into a temporary view or table so that I can outer join the >list to another table in the database to get a record set that contains a row >for every value in the list, whether or not there is a coresponding value in >the second table? > >Thank You > >-- >Ian Skinner >Web Programmer >BloodSource >www.BloodSource.org >Sacramento, CA > >"C code. C code run. Run code run. Please!" >- Cynthia Dunning > >Confidentiality Notice: This message including any >attachments is for the sole use of the intended >recipient(s) and may contain confidential and privileged >information. Any unauthorized review, use, disclosure or >distribution is prohibited. If you are not the >intended recipient, please contact the sender and >delete any copies of this message. > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229687 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
SOT: Oracle Temp Table
Are there any Oracle knowledgeable people online today? What is the easiest way to create a temporary view or table to contain a list of values that I currently have in a ColdFusion variable? In other words, how can I turn a list like this: into a temporary view or table so that I can outer join the list to another table in the database to get a record set that contains a row for every value in the list, whether or not there is a coresponding value in the second table? Thank You -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229686 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54